Analysis of Common rails database query operations and Methods

1. Get Data

Obtain the first and last records
Copy codeThe Code is as follows:
Model. first
Model. first (options)
Model. find (: first, options)
Model. last
Model. last (options)
Model. find (: last, options)

Retrieve records by id

Copy codeThe Code is as follows:
Model. find (1, 10, options)
Model. find ([1, 10], options)
. Find all

Copy codeThe Code is as follows:
Model. all (options)

Perform the same operation on a group of data

Copy codeThe Code is as follows:
User. all. each do | user |
NewsLetter. weekly_deliver (user)
End
If the number of table records is large, this method consumes resources because it loads the data of the entire table at a time. Use the following method to load only 1000 rows at a time, and then gradually complete the yield table

Copy codeThe Code is as follows:
User. find_each do | user |
NewsLetter. weekly_deliver (user)
End
Custom method. find_each accepts the same options as find.

Copy codeThe Code is as follows:
User. find_each (: batch_size => 5000,: start => 2000) do | user |
NewsLetter. weekly_deliver (user)
End
Find_in_batches, similar to find_each, But it passes a model object array instead of a single model object.

Copy codeThe Code is as follows:
Invoice. find_in_batches (: include =>: invoice_lines) do | invoices |
Export. add_invoices (invoices)
End

2. query Conditions

By replacing? To pass the condition ValueTo avoid SQL injection.

Copy codeThe Code is as follows:
Client. first (: conditions => ["orders_count =? ", Params [: orders])
Symbol placeholder Condition

Copy codeThe Code is as follows:
Client. all (: conditions => ["created_at >=: start_date AND created_at <=: end_date", {: start_date => params [: start_date],: end_date => params [: end_date]}])
Range condition in (SET)

Copy codeThe Code is as follows:
Client. all (: conditions => ["created_at IN (?) ", (Params [: start_date]. to_date)... (params [: end_date]. to_date])
Generate SQL

Copy codeThe Code is as follows:
SELECT * FROM users WHERE (created_at IN ('2017-12-31 ', '2017-01-01', '2017-01-02 ', '2017-01-03', '2017-01-04 ', '2017-01-05 ', '2017-01-06', '2017-01-07 ', '2017-01-08 '))
If you want to generate a date and time, Add. to_time
Copy codeThe Code is as follows: params [: start_date]. to_date.to_time, generate the format 00:00:00

An error is reported in the preceding conditions when the database is in the upper database. For example, if Mysql reports an error that the query statement is too long, you can change it to created_at>? AND created_at <? Format

Hash Condition

Copy codeThe Code is as follows:
Client. all (: conditions =>{: locked => true })
With range Condition

Copy codeThe Code is as follows:
Client. all (: conditons =>{: created => (Time. now. midnight-1.day)... Time. now. midnight })
Generate SQL

Copy codeThe Code is as follows:
SELECT * FROM clients WHERE (clients. created_at BETWEEN '2017-12-21 00:00:00 'AND '2017-12-22 00:00:00 ')
Set conditions

Copy codeThe Code is as follows:
Client. all (: conditons =>{: orders_count => [1, 3, 5])
Generate SQL

Copy codeThe Code is as follows:
SELECT * FROM clients WHERE (clients. orders_count IN (1, 3, 5 ))

3. query options

Sort

Copy codeThe Code is as follows:
# Single sorting
Client. all (: order => "created_at ASC ")
# Multiple sorting
Client. all (: order => "orders_count ASC, created_at DESC ")
Returns the specified field.

Copy codeThe Code is as follows:
Client. all (: select => "viewable_by, locked ")
# Use Functions
Client. all (: select => "DISTINCT (name )")
Limit and Offset

Copy codeThe Code is as follows:
Client. all (: limit => 5)
# Generate
SELECT * FROM clients LIMIT 5
Client. all (: limit => 5,: offset => 5)
# Generate
SELECT * FROM clients LIMIT 5, 5
Group

Copy codeThe Code is as follows:
Order. all (: group => "date (created_at)",: order => "created_at ")
Generate SQL
Copy codeThe Code is as follows: SELECT * FROM orders group by date (created_at)
Having
Copy codeThe Code is as follows:
Order. all (: group => "date (created_at)",: having => ["created_at>? ", 1. month. ago)
Generate SQL
Copy codeThe Code is as follows: SELECT * FROM orders group by date (created_at) HAVING created_at> '2017-01-15'
Read-Only

Copy codeThe Code is as follows:
Client = Client. first (: readonly => true)
Client. locked = false
Client. save
# Saving read-only objects triggers the ActiveRecord: ReadOnlyRecord exception.
Lock record during update

Optimistic lock Optimistic Locking

To use optimistic locks, you must create a lock_version field in the table. Each time a record is updated, ActiveRecord automatically increments the value of lock_version,

Copy codeThe Code is as follows:
C1 = Client. find (1) c2 = Client. find (1) c1.name = "Michael" c1.save c2.name = "shocould fail" c2.save # Raises a ActiveRecord: StaleObjectError
Note: You must ensure that your database schema defaults the lock_version column to 0.

This behavior can be turned off by setting ActiveRecord: Base. lock_optimistically = false.

Specifies the optimistic lock field name

Copy codeThe Code is as follows:
Class Client <ActiveRecord: Base set_locking_column: lock_client_column end
Pessimistic lock Pessimistic Locking
Pessimistic locking is directly provided by the database

Copy codeThe Code is as follows:
Item. transaction do
I = Item. first (: lock => true)
I. name = 'Jones'
I. save
End
Mysql execution return
Copy codeThe Code is as follows: SQL (0.2 ms) BEGIN Item Load (0.3 ms) SELECT * FROM 'items 'LIMIT 1 FOR UPDATE Item Update (0.4 ms) UPDATE 'items 'set' updated _ at' = '2017-02-07 18:05:56 ', 'name' = 'Jones' WHERE 'id' = 1 SQL (2009 ms) COMMIT

Add the original lock statement to a specific database
The lock Declaration for Mysql is shared mode, that is, the lock is still readable.
Copy codeThe Code is as follows: Item. transaction do I = Item. find (1,: lock => "lock in share mode") I. increment! (: Views) end

4. Join table

Copy codeThe Code is as follows:
Client. all (: joins => "left outer join address ON addresses. client_id = clients. id ')
Generate SQL
Copy codeThe Code is as follows: SELECT clients. * FROM clients left outer join addresses ON addresses. client_id = clients. id
Associate tables with Array, Hash, and Named Associations
The following models are available:

Copy codeThe Code is as follows:
Class Category <ActiveRecord: Base
Has_timeout: posts
End
Class Post <ActiveRecord: Base
Belongs_to: category
Has_sources: comments
Has_many: tags
End
Class Comments <ActiveRecord: Base
Belongs_to: post
Has_one: guest
End
Class Guest <ActiveRecord: Base
Belongs_to: comment
End
Copy codeThe Code is as follows:
# Associate a link
Category. all: joins =>: posts
# Associate multiple links
Post. all: joins => [: category,: comments]
# Nested Association
Category. all: joins = >{: posts => [{: comments = >:guest},: tags]}
Set conditions for associated query results

Copy codeThe Code is as follows:
Time_range = (Time. now. midnight-1.day)... Time. now. midnight Client. all: joins =>: orders,: conditions => {'Orders. created_at '=> time_ran
# Or
Time_range = (Time. now. midnight-1.day ).. time. now. midnight Client. all: joins =>: orders,: conditions => {: orders = >{: created_at => time_range }}
5. Optimize Loading
Run the following code: 1 + 10 SQL statements

Copy codeThe Code is as follows:
Clients = Client. all (: limit => 10) clients. each do | client |
Puts client. address. postcode
End
Optimization:

Copy codeThe Code is as follows:
Clients = Client. all (: include =>: address,: limit => 10)
Clients. each do | client |
Puts client. address. postcode
End
Load all types and comments of post at one time

Copy codeThe Code is as follows:
Post. all: include => [: category,: comments]
Load all post, cooment, and tags whose category is 1

Copy codeThe Code is as follows:
Category. find 1,: include =>{: posts =>[ {: comments =>: guest},: tags]}
6. dynamic query

Copy codeThe Code is as follows:
Client. find_by_name ("Ryan ")
Client. find_all_by_name ("Ryan ")
#! Method. If no record exists, the ActiveRecord: RecordNotFound exception is thrown.
Client. find_by_name! ("Ryan ")
# Querying Multiple Fields
Client. find_by_name_and_locked ("Ryan", true)
# Create and save when the query is not available
Client. find_or_create_by_name (params [: name])
# An instance is created but not saved when the query fails.
Client. find_or_initialize_by_name ('ry ')
7. find_by_ SQL

Copy codeThe Code is as follows:
Client. find_by_ SQL ("SELECT * FROM clients INNER JOIN orders ON clients. id = orders. client_id ORDER clients. created_at desc ")
8. select_all
Similar to find_by_ SQL, but does not use model instantiation to return records. You will get a hash Array

Copy codeThe Code is as follows:
Client. connection. select_all ("SELECT * FROM clients WHERE id = '1 '")
9. Determine whether the record exists

Copy codeThe Code is as follows:
# Query by id
Client. exists? (1)
Client. exists? (1, 2, 3)
# Or
Client. exists? ([1, 2, 3])
# Query by other conditions
Client. exists? (: Conditions => "first_name = 'ry '")
# If no parameter exists, the table is empty? False: true
Client. exists?
10. Computing

Copy codeThe Code is as follows:
# Calculate the number of result sets
Client. count (: conditons => "first_name = 'ry '")
# Evaluate the number of non-blank fields
Client. count (: age)
# Average Value
Client. average ("orders_count ")
# Minimum value
Client. minimum ("age ")
# Maximum value
Client. maximum ("age ")
# Sum
Client. sum ("orders_count ")