Full to Queen Hook on Converter Rails Fashion Bed Group
Active Record Query Interface
This guide covers dissimilar ways to retrieve data from the database using Active Record.
Subsequently reading this guide, you will know:
- How to find records using a variety of methods and conditions.
- How to specify the society, retrieved attributes, grouping, and other properties of the found records.
- How to employ eager loading to reduce the number of database queries needed for data retrieval.
- How to utilize dynamic finder methods.
- How to use method chaining to employ multiple Active Record methods together.
- How to cheque for the existence of particular records.
- How to perform various calculations on Agile Record models.
- How to run EXPLAIN on relations.
Capacity
- What is the Active Record Query Interface?
- Retrieving Objects from the Database
- Retrieving a Unmarried Object
- Retrieving Multiple Objects in Batches
- Conditions
- Pure String Conditions
- Array Conditions
- Hash Conditions
- NOT Conditions
- OR Weather condition
- AND Conditions
- Ordering
- Selecting Specific Fields
- Limit and Offset
- Group
- Total of grouped items
- Having
- Overriding Conditions
-
unscope -
only -
reselect -
reorder -
reverse_order -
rewhere
-
- Null Relation
- Readonly Objects
- Locking Records for Update
- Optimistic Locking
- Pessimistic Locking
- Joining Tables
-
joins -
left_outer_joins
-
- Eager Loading Associations
- includes
- preload
- eager_load
- Scopes
- Passing in arguments
- Using conditionals
- Applying a default telescopic
- Merging of scopes
- Removing All Scoping
- Dynamic Finders
- Enums
- Agreement Method Chaining
- Retrieving filtered information from multiple tables
- Retrieving specific data from multiple tables
- Detect or Build a New Object
-
find_or_create_by -
find_or_create_by! -
find_or_initialize_by
-
- Finding by SQL
-
select_all -
pluck -
ids
-
- Existence of Objects
- Calculations
- Count
- Boilerplate
- Minimum
- Maximum
- Sum
- Running EXPLAIN
- Interpreting Explain
1 What is the Active Record Query Interface?
If you lot're used to using raw SQL to find database records, so you will mostly find that there are better ways to carry out the same operations in Rails. Agile Record insulates you lot from the demand to use SQL in near cases.
Active Tape will perform queries on the database for you lot and is compatible with most database systems, including MySQL, MariaDB, PostgreSQL, and SQLite. Regardless of which database system you're using, the Active Record method format will always be the aforementioned.
Lawmaking examples throughout this guide will refer to one or more of the following models:
All of the following models use id as the primary primal, unless specified otherwise.
class Author < ApplicationRecord has_many :books , -> { guild ( year_published: :desc ) } end class Volume < ApplicationRecord belongs_to :supplier belongs_to :author has_many :reviews has_and_belongs_to_many :orders , join_table: 'books_orders' scope :in_print , -> { where ( out_of_print: false ) } telescopic :out_of_print , -> { where ( out_of_print: true ) } scope :one-time , -> { where ( 'year_published < ?' , 50 . years . ago )} telescopic :out_of_print_and_expensive , -> { out_of_print . where ( 'price > 500' ) } scope :costs_more_than , -> ( corporeality ) { where ( 'price > ?' , amount ) } end class Customer < ApplicationRecord has_many :orders has_many :reviews finish class Order < ApplicationRecord belongs_to :customer has_and_belongs_to_many :books , join_table: 'books_orders' enum :condition , [ :shipped , :being_packed , :complete , :cancelled ] scope :created_before , -> ( fourth dimension ) { where ( 'created_at < ?' , time ) } end class Review < ApplicationRecord belongs_to :customer belongs_to :volume enum :state , [ :not_reviewed , :published , :hidden ] end class Supplier < ApplicationRecord has_many :books has_many :authors , through: :books stop 
2 Retrieving Objects from the Database
To call back objects from the database, Active Record provides several finder methods. Each finder method allows you to pass arguments into it to perform certain queries on your database without writing raw SQL.
The methods are:
-
annotate -
find -
create_with -
distinct -
eager_load -
extending -
extract_associated -
from -
group -
having -
includes -
joins -
left_outer_joins -
limit -
lock -
none -
offset -
optimizer_hints -
order -
preload -
readonly -
references -
reorder -
reselect -
reverse_order -
select -
where
Finder methods that render a collection, such every bit where and grouping, return an case of ActiveRecord::Relation. Methods that detect a unmarried entity, such as find and get-go, return a single instance of the model.
The main operation of Model.notice(options) can be summarized equally:
- Convert the supplied options to an equivalent SQL query.
- Fire the SQL query and call up the corresponding results from the database.
- Instantiate the equivalent Ruby object of the appropriate model for every resulting row.
- Run
after_findand and thenafter_initializecallbacks, if whatsoever.
2.1 Retrieving a Single Object
Active Record provides several different ways of retrieving a unmarried object.
ii.ane.1 detect
Using the detect method, you can retrieve the object respective to the specified master key that matches any supplied options. For example:
# Observe the customer with primary central (id) x. irb> customer = Customer . observe ( 10 ) => #< Customer id: 10 , first_name: "Ryan" > The SQL equivalent of the to a higher place is:
SELECT * FROM customers WHERE ( customers . id = 10 ) LIMIT 1 The find method will raise an ActiveRecord::RecordNotFound exception if no matching record is found.
Y'all tin also employ this method to query for multiple objects. Call the discover method and pass in an array of primary keys. The return will be an assortment containing all of the matching records for the supplied primary keys. For example:
# Find the customers with main keys 1 and 10. irb> customers = Client . find ([ ane , 10 ]) # OR Client.find(1, 10) => [ #< Customer id: one , first_name: "Lifo" > , #< Customer id: 10 , first_name: "Ryan" > ] The SQL equivalent of the above is:
SELECT * FROM customers WHERE ( customers . id IN ( 1 , 10 )) The find method will raise an ActiveRecord::RecordNotFound exception unless a matching tape is found for all of the supplied primary keys.
2.1.2 take
The take method retrieves a record without any implicit ordering. For case:
irb> customer = Customer . take => #< Customer id: 1 , first_name: "Lifo" > The SQL equivalent of the in a higher place is:
SELECT * FROM customers LIMIT 1 The take method returns zilch if no record is plant and no exception will be raised.
Yous can pass in a numerical argument to the accept method to render upwards to that number of results. For instance
irb> customers = Customer . take ( 2 ) => [ #< Customer id: 1 , first_name: "Lifo" > , #< Customer id: 220 , first_name: "Sara" > ] The SQL equivalent of the in a higher place is:
SELECT * FROM customers LIMIT ii The take! method behaves exactly like have, except that it will raise ActiveRecord::RecordNotFound if no matching record is found.
The retrieved record may vary depending on the database engine.
ii.1.3 first
The first method finds the get-go record ordered by master central (default). For example:
irb> customer = Client . first => #< Client id: 1 , first_name: "Lifo" > The SQL equivalent of the in a higher place is:
SELECT * FROM customers ORDER Past customers . id ASC LIMIT 1 The first method returns nil if no matching tape is found and no exception will exist raised.
If your default scope contains an social club method, outset will render the first record according to this ordering.
Y'all can pass in a numerical statement to the offset method to return up to that number of results. For example
irb> customers = Customer . offset ( three ) => [ #< Customer id: 1 , first_name: "Lifo" > , #< Customer id: 2 , first_name: "Fifo" > , #< Client id: 3 , first_name: "Filo" > ] The SQL equivalent of the higher up is:
SELECT * FROM customers Order Past customers . id ASC LIMIT 3 On a collection that is ordered using order, first will return the first record ordered by the specified attribute for order.
irb> customer = Customer . gild ( :first_name ). first => #< Customer id: two , first_name: "Fifo" > The SQL equivalent of the above is:
SELECT * FROM customers Guild BY customers . first_name ASC LIMIT i The get-go! method behaves exactly like first, except that information technology will enhance ActiveRecord::RecordNotFound if no matching tape is found.
ii.i.four last
The concluding method finds the last tape ordered by principal key (default). For instance:
irb> customer = Customer . last => #< Customer id: 221 , first_name: "Russel" > The SQL equivalent of the above is:
SELECT * FROM customers Gild By customers . id DESC LIMIT 1 The last method returns nil if no matching record is found and no exception will exist raised.
If your default scope contains an social club method, terminal volition return the final tape according to this ordering.
You can pass in a numerical statement to the last method to return up to that number of results. For example
irb> customers = Customer . last ( 3 ) => [ #< Customer id: 219 , first_name: "James" > , #< Customer id: 220 , first_name: "Sara" > , #< Customer id: 221 , first_name: "Russel" > ] The SQL equivalent of the to a higher place is:
SELECT * FROM customers Society By customers . id DESC LIMIT 3 On a collection that is ordered using order, final will return the last record ordered by the specified attribute for order.
irb> client = Customer . lodge ( :first_name ). last => #< Customer id: 220 , first_name: "Sara" > The SQL equivalent of the above is:
SELECT * FROM customers ORDER BY customers . first_name DESC LIMIT 1 The terminal! method behaves exactly like last, except that it volition enhance ActiveRecord::RecordNotFound if no matching record is plant.
2.1.5 find_by
The find_by method finds the get-go record matching some conditions. For instance:
irb> Client . find_by first_name: 'Lifo' => #< Customer id: 1 , first_name: "Lifo" > irb> Customer . find_by first_name: 'Jon' => nil Information technology is equivalent to writing:
Client . where ( first_name: 'Lifo' ). take The SQL equivalent of the above is:
SELECT * FROM customers WHERE ( customers . first_name = 'Lifo' ) LIMIT 1 Note that at that place is no ORDER By in the above SQL. If your find_by conditions tin lucifer multiple records, you should use an order to guarantee a deterministic result.
The find_by! method behaves exactly similar find_by, except that information technology will raise ActiveRecord::RecordNotFound if no matching record is establish. For example:
irb> Customer . find_by! first_name: 'does not exist' ActiveRecord::RecordNotFound This is equivalent to writing:
Customer . where ( first_name: 'does non exist' ). have! 2.2 Retrieving Multiple Objects in Batches
Nosotros often need to iterate over a large set of records, as when we send a newsletter to a large prepare of customers, or when we export data.
This may announced straightforward:
# This may consume too much retentivity if the tabular array is big. Customer . all . each practice | customer | NewsMailer . weekly ( customer ). deliver_now finish But this arroyo becomes increasingly impractical as the table size increases, since Customer.all.each instructs Active Record to fetch the unabridged tabular array in a unmarried laissez passer, build a model object per row, and then keep the unabridged assortment of model objects in memory. Indeed, if nosotros take a big number of records, the entire collection may exceed the corporeality of retentivity available.
Rail provides two methods that accost this problem past dividing records into retention-friendly batches for processing. The first method, find_each, retrieves a batch of records and then yields each tape to the block individually every bit a model. The 2d method, find_in_batches, retrieves a batch of records and then yields the unabridged batch to the cake as an array of models.
The find_each and find_in_batches methods are intended for use in the batch processing of a big number of records that wouldn't fit in memory all at in one case. If you but demand to loop over a thousand records the regular find methods are the preferred option.
2.2.one find_each
The find_each method retrieves records in batches then yields each one to the block. In the post-obit case, find_each retrieves customers in batches of yard and yields them to the cake one by one:
Customer . find_each do | customer | NewsMailer . weekly ( customer ). deliver_now finish This process is repeated, fetching more than batches as needed, until all of the records have been processed.
find_each works on model classes, every bit seen above, and also on relations:
Customer . where ( weekly_subscriber: true ). find_each do | customer | NewsMailer . weekly ( client ). deliver_now end every bit long as they have no ordering, since the method needs to forcefulness an order internally to iterate.
If an social club is present in the receiver the behaviour depends on the flag config.active_record.error_on_ignored_order. If true, ArgumentError is raised, otherwise the order is ignored and a warning issued, which is the default. This can be overridden with the option :error_on_ignore, explained beneath.
2.2.1.1 Options for find_each
:batch_size
The :batch_size choice allows you to specify the number of records to be retrieved in each batch, before being passed individually to the block. For example, to retrieve records in batches of 5000:
Client . find_each ( batch_size: 5000 ) do | customer | NewsMailer . weekly ( customer ). deliver_now end :beginning
By default, records are fetched in ascending gild of the primary fundamental. The :beginning option allows y'all to configure the commencement ID of the sequence whenever the lowest ID is not the one y'all need. This would be useful, for instance, if you wanted to resume an interrupted batch procedure, provided you lot saved the last processed ID as a checkpoint.
For case, to send newsletters only to customers with the primary central starting from 2000:
Customer . find_each ( start: 2000 ) practise | customer | NewsMailer . weekly ( client ). deliver_now end :finish
Like to the :start option, :finish allows you to configure the last ID of the sequence whenever the highest ID is not the one you need. This would exist useful, for example, if y'all wanted to run a batch process using a subset of records based on :start and :finish.
For example, to send newsletters only to customers with the chief key starting from 2000 upwards to 10000:
Customer . find_each ( kickoff: 2000 , finish: 10000 ) do | customer | NewsMailer . weekly ( customer ). deliver_now end Some other case would be if you wanted multiple workers treatment the same processing queue. Y'all could accept each worker handle 10000 records by setting the appropriate :starting time and :end options on each worker.
:error_on_ignore
Overrides the awarding config to specify if an fault should exist raised when an order is present in the relation.
ii.2.2 find_in_batches
The find_in_batches method is similar to find_each, since both retrieve batches of records. The difference is that find_in_batches yields batches to the block every bit an array of models, instead of individually. The following example volition yield to the supplied cake an array of up to yard customers at a time, with the final block containing any remaining customers:
# Give add_customers an array of 1000 customers at a time. Customer . find_in_batches practice | customers | export . add_customers ( customers ) finish find_in_batches works on model classes, as seen in a higher place, and also on relations:
# Give add_customers an array of m recently agile customers at a fourth dimension. Customer . recently_active . find_in_batches do | customers | export . add_customers ( customers ) finish as long equally they have no ordering, since the method needs to forcefulness an social club internally to iterate.
2.ii.2.1 Options for find_in_batches
The find_in_batches method accepts the same options as find_each:
:batch_size
Just like for find_each, batch_size establishes how many records will exist retrieved in each group. For case, retrieving batches of 2500 records can be specified as:
Customer . find_in_batches ( batch_size: 2500 ) do | customers | export . add_customers ( customers ) end :start
The start option allows specifying the outset ID from where records will be selected. As mentioned before, by default records are fetched in ascending guild of the principal key. For example, to retrieve customers starting on ID: 5000 in batches of 2500 records, the following lawmaking tin be used:
Customer . find_in_batches ( batch_size: 2500 , commencement: 5000 ) do | customers | consign . add_customers ( customers ) end :finish
The finish selection allows specifying the ending ID of the records to be retrieved. The lawmaking below shows the case of retrieving customers in batches, up to the customer with ID: 7000:
Customer . find_in_batches ( terminate: 7000 ) do | customers | export . add_customers ( customers ) end :error_on_ignore
The error_on_ignore selection overrides the application config to specify if an error should exist raised when a specific society is present in the relation.
3 Conditions
The where method allows you to specify conditions to limit the records returned, representing the WHERE-part of the SQL statement. Weather condition can either be specified as a string, array, or hash.
3.1 Pure String Conditions
If you'd like to add conditions to your find, you could just specify them in there, just like Book.where("title = 'Introduction to Algorithms'"). This will observe all books where the title field value is 'Introduction to Algorithms'.
Edifice your own weather condition every bit pure strings tin exit you vulnerable to SQL injection exploits. For instance, Book.where("championship LIKE '%#{params[:title]}%'") is not safe. See the adjacent section for the preferred mode to handle weather using an array.
3.2 Array Weather
Now what if that title could vary, say as an argument from somewhere? The observe would and then have the form:
Volume . where ( "title = ?" , params [ :title ]) Agile Record will take the showtime statement as the conditions string and whatever additional arguments will replace the question marks (?) in information technology.
If you lot want to specify multiple atmospheric condition:
Book . where ( "title = ? AND out_of_print = ?" , params [ :title ], fake ) In this example, the starting time question marker will be replaced with the value in params[:title] and the second volition be replaced with the SQL representation of false, which depends on the adapter.
This code is highly preferable:
Volume . where ( "title = ?" , params [ :title ]) to this code:
Book . where ( "title = #{ params [ :title ] } " ) because of argument safety. Putting the variable straight into the conditions string will pass the variable to the database as-is. This means that it will exist an unescaped variable directly from a user who may have malicious intent. If you do this, you put your entire database at risk because once a user finds out they can exploit your database they tin can practice just virtually annihilation to it. Never e'er put your arguments directly within the conditions string.
3.two.1 Placeholder Conditions
Similar to the (?) replacement style of params, you tin too specify keys in your conditions string along with a corresponding keys/values hash:
Book . where ( "created_at >= :start_date AND created_at <= :end_date" , { start_date: params [ :start_date ], end_date: params [ :end_date ]}) This makes for clearer readability if you lot have a big number of variable weather condition.
3.3 Hash Atmospheric condition
Active Record besides allows you lot to pass in hash conditions which can increase the readability of your atmospheric condition syntax. With hash conditions, you pass in a hash with keys of the fields you want qualified and the values of how you lot want to authorize them:
Only equality, range, and subset checking are possible with Hash weather condition.
iii.3.1 Equality Conditions
Volume . where ( out_of_print: true ) This volition generate SQL like this:
SELECT * FROM books WHERE ( books . out_of_print = 1 ) The field name can also be a string:
Book . where ( 'out_of_print' => true ) In the case of a belongs_to human relationship, an association key can be used to specify the model if an Agile Record object is used equally the value. This method works with polymorphic relationships as well.
author = Author . start Volume . where ( author: author ) Author . joins ( :books ). where ( books: { author: author }) 3.3.two Range Conditions
Book . where ( created_at: ( Time . now . midnight - 1 . day ) .. Fourth dimension . at present . midnight ) This will find all books created yesterday by using a BETWEEN SQL argument:
SELECT * FROM books WHERE ( books . created_at Between '2008-12-21 00:00:00' AND '2008-12-22 00:00:00' ) This demonstrates a shorter syntax for the examples in Array Conditions
iii.iii.3 Subset Weather
If you want to find records using the IN expression you tin laissez passer an assortment to the conditions hash:
Customer . where ( orders_count: [ 1 , 3 , v ]) This lawmaking will generate SQL similar this:
SELECT * FROM customers WHERE ( customers . orders_count IN ( 1 , iii , 5 )) 3.four NOT Weather
Non SQL queries can be built by where.not:
Customer . where . not ( orders_count: [ 1 , 3 , 5 ]) In other words, this query can be generated by calling where with no statement, then immediately concatenation with non passing where atmospheric condition. This volition generate SQL like this:
SELECT * FROM customers WHERE ( customers . orders_count NOT IN ( 1 , 3 , v )) iii.5 OR Conditions
OR weather betwixt two relations tin can be built by calling or on the showtime relation, and passing the second one equally an argument.
Client . where ( last_name: 'Smith' ). or ( Customer . where ( orders_count: [ 1 , 3 , 5 ])) SELECT * FROM customers WHERE ( customers . last_name = 'Smith' OR customers . orders_count IN ( 1 , three , 5 )) 3.6 AND Conditions
AND conditions tin can exist built by chaining where conditions.
Customer . where ( last_name: 'Smith' ). where ( orders_count: [ 1 , iii , 5 ])) SELECT * FROM customers WHERE customers . last_name = 'Smith' AND customers . orders_count IN ( 1 , 3 , five ) AND conditions for the logical intersection between relations tin be congenital by calling and on the first relation, and passing the 2nd ane as an argument.
Customer . where ( id: [ i , 2 ]). and ( Customer . where ( id: [ 2 , iii ])) SELECT * FROM customers WHERE ( customers . id IN ( 1 , two ) AND customers . id IN ( 2 , 3 )) 4 Ordering
To call back records from the database in a specific order, you can apply the guild method.
For example, if you're getting a ready of records and want to order them in ascending order by the created_at field in your table:
Volume . guild ( :created_at ) # OR Volume . order ( "created_at" ) You could specify ASC or DESC as well:
Book . society ( created_at: :desc ) # OR Volume . social club ( created_at: :asc ) # OR Book . gild ( "created_at DESC" ) # OR Book . lodge ( "created_at ASC" ) Or ordering by multiple fields:
Volume . order ( title: :asc , created_at: :desc ) # OR Book . order ( :championship , created_at: :desc ) # OR Book . order ( "title ASC, created_at DESC" ) # OR Volume . lodge ( "title ASC" , "created_at DESC" ) If you want to call order multiple times, subsequent orders volition be appended to the beginning:
irb> Book . order ( "title ASC" ). club ( "created_at DESC" ) SELECT * FROM books ORDER BY title ASC, created_at DESC In most database systems, on selecting fields with singled-out from a result set using methods similar select, pluck and ids; the order method will raise an ActiveRecord::StatementInvalid exception unless the field(southward) used in lodge clause are included in the select list. See the next section for selecting fields from the result set.
5 Selecting Specific Fields
By default, Model.find selects all the fields from the effect prepare using select *.
To select only a subset of fields from the result set, you tin can specify the subset via the select method.
For example, to select only isbn and out_of_print columns:
Book . select ( :isbn , :out_of_print ) # OR Volume . select ( "isbn, out_of_print" ) The SQL query used by this find call volition exist somewhat like:
SELECT isbn , out_of_print FROM books Be careful because this also ways you're initializing a model object with only the fields that you've selected. If you attempt to access a field that is not in the initialized record yous'll receive:
ActiveModel::MissingAttributeError: missing aspect: <attribute> Where <attribute> is the attribute you asked for. The id method will not raise the ActiveRecord::MissingAttributeError, so but be conscientious when working with associations because they need the id method to office properly.
If yous would similar to but grab a single tape per unique value in a certain field, you can use distinct:
Client . select ( :last_name ). distinct This would generate SQL like:
SELECT Distinct last_name FROM customers You can also remove the uniqueness constraint:
# Returns unique last_names query = Customer . select ( :last_name ). distinct # Returns all last_names, even if at that place are duplicates query . singled-out ( false ) 6 Limit and Offset
To use LIMIT to the SQL fired by the Model.detect, you tin can specify the LIMIT using limit and beginning methods on the relation.
You can use limit to specify the number of records to be retrieved, and utilize offset to specify the number of records to skip earlier starting to render the records. For example
will return a maximum of 5 customers and because information technology specifies no offset it will return the first 5 in the table. The SQL information technology executes looks like this:
SELECT * FROM customers LIMIT 5 Calculation offset to that
Client . limit ( 5 ). offset ( 30 ) volition return instead a maximum of 5 customers beginning with the 31st. The SQL looks like:
SELECT * FROM customers LIMIT 5 Beginning 30 7 Grouping
To employ a Group Past clause to the SQL fired past the finder, you tin can use the group method.
For example, if yous want to find a collection of the dates on which orders were created:
Society . select ( "created_at" ). group ( "created_at" ) And this will requite you a unmarried Club object for each engagement where there are orders in the database.
The SQL that would exist executed would be something like this:
SELECT created_at FROM orders Grouping BY created_at 7.i Total of grouped items
To get the total of grouped items on a unmarried query, call count after the grouping.
irb> Order . group ( :status ). count => { "being_packed" => 7 , "shipped" => 12 } The SQL that would be executed would be something like this:
SELECT COUNT ( * ) AS count_all , status Equally status FROM orders Grouping By status 8 Having
SQL uses the HAVING clause to specify conditions on the Grouping BY fields. You can add the HAVING clause to the SQL fired past the Model.observe by calculation the having method to the discover.
For example:
Society . select ( "created_at, sum(full) as total_price" ). group ( "created_at" ). having ( "sum(total) > ?" , 200 ) The SQL that would exist executed would be something like this:
SELECT created_at as ordered_date , sum ( full ) as total_price FROM orders GROUP BY created_at HAVING sum ( total ) > 200 This returns the date and total toll for each order object, grouped by the day they were ordered and where the total is more than than $200.
You would access the total_price for each order object returned like this:
big_orders = Order . select ( "created_at, sum(full) equally total_price" ) . group ( "created_at" ) . having ( "sum(full) > ?" , 200 ) big_orders [ 0 ]. total_price # Returns the total price for the get-go Lodge object ix Overriding Conditions
9.1 unscope
You tin can specify certain conditions to exist removed using the unscope method. For example:
Book . where ( 'id > 100' ). limit ( twenty ). order ( 'id desc' ). unscope ( :club ) The SQL that would be executed:
SELECT * FROM books WHERE id > 100 LIMIT 20 -- Original query without `unscope` SELECT * FROM books WHERE id > 100 Gild BY id desc LIMIT xx You can also unscope specific where clauses. For case, this will remove id status from the where clause:
Book . where ( id: x , out_of_print: fake ). unscope ( where: :id ) # SELECT books.* FROM books WHERE out_of_print = 0 A relation which has used unscope volition affect any relation into which it is merged:
Book . society ( 'id desc' ). merge ( Volume . unscope ( :gild )) # SELECT books.* FROM books 9.2 but
You can likewise override conditions using the only method. For example:
Volume . where ( 'id > x' ). limit ( twenty ). club ( 'id desc' ). simply ( :club , :where ) The SQL that would be executed:
SELECT * FROM books WHERE id > ten ORDER BY id DESC -- Original query without `only` SELECT * FROM books WHERE id > 10 Social club BY id DESC LIMIT xx ix.iii reselect
The reselect method overrides an existing select statement. For example:
Volume . select ( :title , :isbn ). reselect ( :created_at ) The SQL that would exist executed:
SELECT `books` . `created_at` FROM `books` Compare this to the example where the reselect clause is not used:
Volume . select ( :title , :isbn ). select ( :created_at ) the SQL executed would be:
SELECT `books` . `title` , `books` . `isbn` , `books` . `created_at` FROM `books` 9.four reorder
The reorder method overrides the default telescopic guild. For case if the class definition includes this:
class Author < ApplicationRecord has_many :books , -> { order ( year_published: :desc ) } end And you execute this:
The SQL that would be executed:
SELECT * FROM authors WHERE id = ten LIMIT 1 SELECT * FROM books WHERE author_id = ten Social club Past year_published DESC You can using the reorder clause to specify a different way to order the books:
Writer . find ( 10 ). books . reorder ( 'year_published ASC' ) The SQL that would be executed:
SELECT * FROM authors WHERE id = 10 LIMIT one SELECT * FROM books WHERE author_id = x ORDER BY year_published ASC 9.five reverse_order
The reverse_order method reverses the ordering clause if specified.
Volume . where ( "author_id > 10" ). order ( :year_published ). reverse_order The SQL that would exist executed:
SELECT * FROM books WHERE author_id > 10 ORDER BY year_published DESC If no ordering clause is specified in the query, the reverse_order orders by the main key in reverse club.
Book . where ( "author_id > 10" ). reverse_order The SQL that would be executed:
SELECT * FROM books WHERE author_id > 10 Social club BY books . id DESC The reverse_order method accepts no arguments.
9.6 rewhere
The rewhere method overrides an existing, named where condition. For case:
Book . where ( out_of_print: true ). rewhere ( out_of_print: simulated ) The SQL that would exist executed:
SELECT * FROM books WHERE `out_of_print` = 0 If the rewhere clause is non used, the where clauses are ANDed together:
Book . where ( out_of_print: true ). where ( out_of_print: false ) the SQL executed would be:
SELECT * FROM books WHERE `out_of_print` = i AND `out_of_print` = 0 x Null Relation
The none method returns a chainable relation with no records. Whatever subsequent conditions chained to the returned relation volition continue generating empty relations. This is useful in scenarios where you demand a chainable response to a method or a scope that could render nix results.
Book . none # returns an empty Relation and fires no queries. # The highlighted_reviews method below is expected to always return a Relation. Book . first . highlighted_reviews . boilerplate ( :rating ) # => Returns boilerplate rating of a book class Book # Returns reviews if at that place are at least v, # else consider this as not-reviewed book def highlighted_reviews if reviews . count > five reviews else Review . none # Does not encounter minimum threshold yet end stop finish xi Readonly Objects
Active Record provides the readonly method on a relation to explicitly disallow modification of any of the returned objects. Any effort to modify a readonly record will not succeed, raising an ActiveRecord::ReadOnlyRecord exception.
client = Client . readonly . kickoff client . visits += 1 customer . save As customer is explicitly set up to exist a readonly object, the above code will raise an ActiveRecord::ReadOnlyRecord exception when calling customer.relieve with an updated value of visits.
12 Locking Records for Update
Locking is helpful for preventing race weather condition when updating records in the database and ensuring atomic updates.
Active Record provides ii locking mechanisms:
- Optimistic Locking
- Pessimistic Locking
12.1 Optimistic Locking
Optimistic locking allows multiple users to access the same tape for edits, and assumes a minimum of conflicts with the data. It does this by checking whether another procedure has made changes to a tape since information technology was opened. An ActiveRecord::StaleObjectError exception is thrown if that has occurred and the update is ignored.
Optimistic locking column
In order to employ optimistic locking, the tabular array needs to accept a column called lock_version of type integer. Each time the record is updated, Active Record increments the lock_version column. If an update request is made with a lower value in the lock_version field than is currently in the lock_version column in the database, the update asking will fail with an ActiveRecord::StaleObjectError.
For case:
c1 = Customer . find ( 1 ) c2 = Customer . observe ( 1 ) c1 . first_name = "Sandra" c1 . save c2 . first_name = "Michael" c2 . save # Raises an ActiveRecord::StaleObjectError You're and then responsible for dealing with the conflict past rescuing the exception and either rolling back, merging, or otherwise apply the business logic needed to resolve the conflict.
This beliefs can be turned off by setting ActiveRecord::Base of operations.lock_optimistically = false.
To override the proper name of the lock_version column, ActiveRecord::Base provides a course aspect called locking_column:
course Customer < ApplicationRecord self . locking_column = :lock_customer_column stop 12.2 Pessimistic Locking
Pessimistic locking uses a locking mechanism provided by the underlying database. Using lock when edifice a relation obtains an exclusive lock on the selected rows. Relations using lock are ordinarily wrapped within a transaction for preventing deadlock conditions.
For example:
Book . transaction exercise book = Book . lock . first book . title = 'Algorithms, second edition' book . salvage! cease The above session produces the following SQL for a MySQL backend:
SQL ( 0 . ii ms ) BEGIN Book Load ( 0 . 3 ms ) SELECT * FROM `books` LIMIT 1 FOR UPDATE Book Update ( 0 . 4 ms ) UPDATE `books` Gear up `updated_at` = '2009-02-07 18:05:56' , `title` = 'Algorithms, 2nd edition' WHERE `id` = ane SQL ( 0 . 8 ms ) COMMIT You can also pass raw SQL to the lock method for assuasive unlike types of locks. For example, MySQL has an expression called LOCK IN SHARE Mode where yous can lock a record but nonetheless let other queries to read it. To specify this expression just pass it in as the lock choice:
Book . transaction do book = Book . lock ( "LOCK IN SHARE MODE" ). find ( one ) book . increment! ( :views ) end Notation that your database must support the raw SQL, that you laissez passer in to the lock method.
If yous already have an instance of your model, you can start a transaction and learn the lock in one go using the following code:
book = Volume . first book . with_lock do # This cake is called within a transaction, # book is already locked. book . increment! ( :views ) end xiii Joining Tables
Active Record provides two finder methods for specifying JOIN clauses on the resulting SQL: joins and left_outer_joins. While joins should be used for INNER Join or custom queries, left_outer_joins is used for queries using LEFT OUTER Bring together.
thirteen.1 joins
At that place are multiple ways to apply the joins method.
thirteen.1.1 Using a String SQL Fragment
You can just supply the raw SQL specifying the JOIN clause to joins:
Writer . joins ( "INNER Bring together books ON books.author_id = authors.id AND books.out_of_print = FALSE" ) This volition upshot in the following SQL:
SELECT authors . * FROM authors INNER JOIN books ON books . author_id = authors . id AND books . out_of_print = False 13.1.2 Using Assortment/Hash of Named Associations
Active Record lets yous utilize the names of the associations defined on the model equally a shortcut for specifying Bring together clauses for those associations when using the joins method.
All of the following will produce the expected bring together queries using INNER Bring together:
13.1.2.1 Joining a Unmarried Association
This produces:
SELECT books . * FROM books INNER Join reviews ON reviews . book_id = books . id Or, in English: "return a Book object for all books with reviews". Annotation that you will come across duplicate books if a book has more ane review. If you want unique books, you tin use Volume.joins(:reviews).distinct.
13.1.3 Joining Multiple Associations
Book . joins ( :author , :reviews ) This produces:
SELECT books . * FROM books INNER JOIN authors ON authors . id = books . author_id INNER Join reviews ON reviews . book_id = books . id Or, in English: "return all books with their author that have at to the lowest degree one review". Note again that books with multiple reviews will show up multiple times.
thirteen.1.3.1 Joining Nested Associations (Unmarried Level)
Book . joins ( reviews: :customer ) This produces:
SELECT books . * FROM books INNER Bring together reviews ON reviews . book_id = books . id INNER Join customers ON customers . id = reviews . customer_id Or, in English: "return all books that have a review by a customer."
xiii.1.3.2 Joining Nested Associations (Multiple Level)
Writer . joins ( books: [{ reviews: { client: :orders } }, :supplier ] ) This produces:
SELECT * FROM authors INNER Bring together books ON books . author_id = authors . id INNER Bring together reviews ON reviews . book_id = books . id INNER JOIN customers ON customers . id = reviews . customer_id INNER Join orders ON orders . customer_id = customers . id INNER JOIN suppliers ON suppliers . id = books . supplier_id Or, in English: "return all authors that have books with reviews and have been ordered by a customer, and the suppliers for those books."
13.1.4 Specifying Weather on the Joined Tables
Yous tin can specify conditions on the joined tables using the regular Array and String conditions. Hash weather condition provide a special syntax for specifying conditions for the joined tables:
time_range = ( Time . now . midnight - one . day ) .. Time . now . midnight Customer . joins ( :orders ). where ( 'orders.created_at' => time_range ). singled-out This will notice all customers who have orders that were created yesterday, using a Between SQL expression to compare created_at.
An alternative and cleaner syntax is to nest the hash weather:
time_range = ( Time . at present . midnight - 1 . twenty-four hours ) .. Time . now . midnight Customer . joins ( :orders ). where ( orders: { created_at: time_range }). distinct For more avant-garde atmospheric condition or to reuse an existing named scope, Relation#merge may be used. First, let'due south add a new named scope to the Order model:
grade Lodge < ApplicationRecord belongs_to :customer scope :created_in_time_range , -> ( time_range ) { where ( created_at: time_range ) } terminate Now we can use Relation#merge to merge in the created_in_time_range scope:
time_range = ( Time . at present . midnight - i . twenty-four hours ) .. Time . now . midnight Client . joins ( :orders ). merge ( Order . created_in_time_range ( time_range )). distinct This will observe all customers who have orders that were created yesterday, again using a BETWEEN SQL expression.
13.2 left_outer_joins
If yous desire to select a set of records whether or not they accept associated records y'all can use the left_outer_joins method.
Customer . left_outer_joins ( :reviews ). singled-out . select ( 'customers.*, COUNT(reviews.*) AS reviews_count' ). group ( 'customers.id' ) Which produces:
SELECT Distinct customers . * , COUNT ( reviews . * ) AS reviews_count FROM customers LEFT OUTER JOIN reviews ON reviews . customer_id = customers . id GROUP By customers . id Which means: "return all customers with their count of reviews, whether or not they take any reviews at all"
xiv Eager Loading Associations
Eager loading is the mechanism for loading the associated records of the objects returned past Model.find using as few queries as possible.
North + 1 queries problem
Consider the following code, which finds 10 books and prints their authors' last_name:
books = Book . limit ( ten ) books . each do | book | puts book . author . last_name stop This code looks fine at the first sight. Only the trouble lies inside the full number of queries executed. The above code executes one (to find 10 books) + 10 (one per each volume to load the author) = 11 queries in total.
Solution to N + i queries trouble
Active Record lets you lot specify in advance all the associations that are going to be loaded.
The methods are:
-
includes -
preload -
eager_load
14.1 includes
With includes, Active Record ensures that all of the specified associations are loaded using the minimum possible number of queries.
Revisiting the above example using the includes method, nosotros could rewrite Book.limit(x) to eager load authors:
books = Book . includes ( :author ). limit ( 10 ) books . each practice | book | puts book . author . last_name end The above code will execute but 2 queries, equally opposed to 11 queries in the previous case:
SELECT `books` * FROM `books` LIMIT ten SELECT `authors` . * FROM `authors` WHERE `authors` . `book_id` IN ( 1 , ii , iii , 4 , 5 , 6 , vii , viii , ix , 10 ) 14.ane.ane Eager Loading Multiple Associations
Agile Record lets you lot eager load whatever number of associations with a single Model.find call past using an array, hash, or a nested hash of array/hash with the includes method.
14.1.2 Array of Multiple Associations
Customer . includes ( :orders , :reviews ) This loads all the customers and the associated orders and reviews for each.
14.1.two.1 Nested Associations Hash
Customer . includes ( orders: { books: [ :supplier , :writer ]}). discover ( 1 ) This will detect the client with id ane and eager load all of the associated orders for it, the books for all of the orders, and the author and supplier for each of the books.
xiv.1.3 Specifying Weather on Eager Loaded Associations
Even though Active Record lets y'all specify conditions on the eager loaded associations just like joins, the recommended fashion is to use joins instead.
Notwithstanding if yous must do this, yous may utilize where as you would normally.
Writer . includes ( :books ). where ( books: { out_of_print: truthful }) This would generate a query which contains a LEFT OUTER Join whereas the joins method would generate one using the INNER JOIN function instead.
SELECT authors . id AS t0_r0 , ... books . updated_at AS t1_r5 FROM authors LEFT OUTER JOIN "books" ON "books" . "author_id" = "authors" . "id" WHERE ( books . out_of_print = i ) If there was no where condition, this would generate the normal set of two queries.
Using where like this will only work when you pass it a Hash. For SQL-fragments you need to use references to force joined tables:
Author . includes ( :books ). where ( "books.out_of_print = true" ). references ( :books ) If, in the instance of this includes query, there were no books for any authors, all the authors would notwithstanding be loaded. By using joins (an INNER JOIN), the join conditions must match, otherwise no records will be returned.
If an association is eager loaded as role of a join, whatever fields from a custom select clause will not be present on the loaded models. This is considering it is ambiguous whether they should appear on the parent record, or the child.
14.2 preload
With preload, Active record ensures that loaded using a query for every specified association.
Revisiting the example where N + 1 was occurred using the preload method, we could rewrite Volume.limit(10) to authors:
books = Book . preload ( :writer ). limit ( x ) books . each practise | book | puts book . author . last_name end The above code will execute just two queries, as opposed to xi queries in the previous case:
SELECT `books` * FROM `books` LIMIT 10 SELECT `authors` . * FROM `authors` WHERE `authors` . `book_id` IN ( i , 2 , three , 4 , 5 , vi , 7 , eight , 9 , ten ) The preload method using an assortment, hash, or a nested hash of array/hash in the same way as the includes method to load whatever number of associations with a single Model.find call. However, unlike the includes method, it is non possible to specify atmospheric condition for eager loaded associations.
xiv.three eager_load
With eager_load, Active record ensures that strength eager loading past usingLEFT OUTER JOIN for all specified associations.
Revisiting the example where Northward + 1 was occurred using the eager_load method, we could rewrite Volume.limit(ten) to authors:
books = Book . eager_load ( :author ). limit ( 10 ) books . each practice | volume | puts book . author . last_name end The in a higher place lawmaking will execute only 2 queries, as opposed to 11 queries in the previous case:
SELECT DISTINCT `books` . `id` FROM `books` LEFT OUTER JOIN `authors` ON `authors` . `book_id` = `books` . `id` LIMIT ten SELECT `books` . `id` As t0_r0 , `books` . `last_name` AS t0_r1 , ... FROM `books` LEFT OUTER Bring together `authors` ON `authors` . `book_id` = `books` . `id` WHERE `books` . `id` IN ( 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 ) The eager_load method using an array, hash, or a nested hash of array/hash in the aforementioned way as the includes method to load any number of associations with a single Model.find call. Also, like the includes method, you can specify the conditions of the eager loaded association.
15 Scopes
Scoping allows yous to specify usually-used queries which can be referenced as method calls on the association objects or models. With these scopes, yous can apply every method previously covered such equally where, joins and includes. All scope bodies should return an ActiveRecord::Relation or nix to allow for further methods (such as other scopes) to be called on it.
To define a simple scope, we use the telescopic method within the class, passing the query that we'd like to run when this telescopic is called:
class Book < ApplicationRecord scope :out_of_print , -> { where ( out_of_print: true ) } end To call this out_of_print scope we can call information technology on either the class:
irb> Book . out_of_print => #< ActiveRecord :: Relation > # all out of print books Or on an association consisting of Volume objects:
irb> author = Author . first irb> author . books . out_of_print => #< ActiveRecord :: Relation > # all out of print books past `author` Scopes are too chainable within scopes:
class Book < ApplicationRecord scope :out_of_print , -> { where ( out_of_print: truthful ) } scope :out_of_print_and_expensive , -> { out_of_print . where ( "price > 500" ) } stop 15.1 Passing in arguments
Your scope can have arguments:
class Book < ApplicationRecord scope :costs_more_than , -> ( amount ) { where ( "price > ?" , amount ) } terminate Call the scope as if it were a form method:
irb> Book . costs_more_than ( 100.ten ) Even so, this is just duplicating the functionality that would be provided to you by a grade method.
form Volume < ApplicationRecord def self . costs_more_than ( corporeality ) where ( "price > ?" , amount ) cease end These methods will still be accessible on the clan objects:
irb> author . books . costs_more_than ( 100.x ) 15.2 Using conditionals
Your scope can employ conditionals:
class Order < ApplicationRecord scope :created_before , -> ( fourth dimension ) { where ( "created_at < ?" , time ) if time . nowadays? } finish Similar the other examples, this volition behave similarly to a form method.
course Social club < ApplicationRecord def self . created_before ( time ) where ( "created_at < ?" , fourth dimension ) if fourth dimension . nowadays? terminate terminate Nonetheless, there is ane important caveat: A telescopic will ever render an ActiveRecord::Relation object, even if the conditional evaluates to false, whereas a form method, will return zero. This tin can cause NoMethodError when chaining class methods with conditionals, if any of the conditionals render false.
15.iii Applying a default scope
If we wish for a telescopic to exist applied across all queries to the model we can use the default_scope method inside the model itself.
class Book < ApplicationRecord default_scope { where ( out_of_print: false ) } finish When queries are executed on this model, the SQL query will now wait something like this:
SELECT * FROM books WHERE ( out_of_print = false ) If you demand to do more complex things with a default scope, you tin alternatively ascertain it as a grade method:
class Book < ApplicationRecord def self . default_scope # Should render an ActiveRecord::Relation. finish end The default_scope is also applied while creating/building a tape when the scope arguments are given as a Hash. Information technology is non applied while updating a record. E.g.:
class Book < ApplicationRecord default_scope { where ( out_of_print: false ) } end irb> Book . new => #< Book id: zero , out_of_print: fake > irb> Book . unscoped . new => #< Book id: naught , out_of_print: zero > Be enlightened that, when given in the Array format, default_scope query arguments cannot be converted to a Hash for default attribute consignment. East.g.:
form Volume < ApplicationRecord default_scope { where ( "out_of_print = ?" , faux ) } end irb> Book . new => #< Book id: nil , out_of_print: nix > 15.4 Merging of scopes
Just similar where clauses, scopes are merged using AND conditions.
class Book < ApplicationRecord telescopic :in_print , -> { where ( out_of_print: imitation ) } scope :out_of_print , -> { where ( out_of_print: truthful ) } scope :recent , -> { where ( 'year_published >= ?' , Date . current . year - l )} scope :old , -> { where ( 'year_published < ?' , Date . current . year - 50 )} finish irb> Book . out_of_print . one-time SELECT books.* FROM books WHERE books.out_of_print = 'true' AND books.year_published < 1969 We can mix and lucifer scope and where conditions and the final SQL will accept all atmospheric condition joined with AND.
irb> Book . in_print . where ( 'toll < 100' ) SELECT books.* FROM books WHERE books.out_of_print = 'false' AND books.price < 100 If we do desire the last where clause to win then merge can be used.
irb> Book . in_print . merge ( Book . out_of_print ) SELECT books.* FROM books WHERE books.out_of_print = truthful One of import caveat is that default_scope will be prepended in scope and where conditions.
class Book < ApplicationRecord default_scope { where ( 'year_published >= ?' , Date . electric current . year - fifty )} scope :in_print , -> { where ( out_of_print: false ) } scope :out_of_print , -> { where ( out_of_print: true ) } stop irb> Book . all SELECT books.* FROM books WHERE (year_published >= 1969) irb> Book . in_print SELECT books.* FROM books WHERE (year_published >= 1969) AND books.out_of_print = false irb> Book . where ( 'toll > 50' ) SELECT books.* FROM books WHERE (year_published >= 1969) AND (price > fifty) As you can see above the default_scope is existence merged in both scope and where conditions.
15.five Removing All Scoping
If we wish to remove scoping for whatever reason we can use the unscoped method. This is especially useful if a default_scope is specified in the model and should non exist applied for this particular query.
This method removes all scoping and will exercise a normal query on the table.
irb> Volume . unscoped . all SELECT books.* FROM books irb> Volume . where ( out_of_print: truthful ). unscoped . all SELECT books.* FROM books unscoped tin likewise have a block:
irb> Volume . unscoped { Volume . out_of_print } SELECT books.* FROM books WHERE books.out_of_print 16 Dynamic Finders
For every field (also known every bit an attribute) y'all ascertain in your table, Active Record provides a finder method. If you lot have a field called first_name on your Client model for example, y'all get the instance method find_by_first_name for gratuitous from Active Record. If you likewise have a locked field on the Customer model, y'all also get find_by_locked method.
You tin can specify an exclamation point (!) on the terminate of the dynamic finders to get them to raise an ActiveRecord::RecordNotFound error if they practise not render any records, like Customer.find_by_name!("Ryan")
If yous want to discover both by name and orders_count, you can chain these finders together by simply typing "and" between the fields. For instance, Customer.find_by_first_name_and_orders_count("Ryan", 5).
17 Enums
An enum lets you define an Array of values for an attribute and refer to them by name. The bodily value stored in the database is an integer that has been mapped to 1 of the values.
Declaring an enum will:
- Create scopes that tin be used to find all objects that have or exercise not have one of the enum values
- Create an instance method that can exist used to determine if an object has a item value for the enum
- Create an instance method that can be used to change the enum value of an object
for all possible values of an enum.
For instance, given this enum annunciation:
class Order < ApplicationRecord enum :status , [ :shipped , :being_packaged , :complete , :cancelled ] stop These scopes are created automatically and can be used to find all objects with or without a particular value for status:
irb> Order . shipped => #< ActiveRecord :: Relation > # all orders with status == :shipped irb> Order . not_shipped => #< ActiveRecord :: Relation > # all orders with status != :shipped These instance methods are created automatically and query whether the model has that value for the status enum:
irb> order = Guild . shipped . first irb> social club . shipped? => truthful irb> club . complete? => false These case methods are created automatically and volition get-go update the value of status to the named value and then query whether or not the condition has been successfully set to the value:
irb> order = Guild . first irb> guild . shipped! UPDATE "orders" SET "status" = ?, "updated_at" = ? WHERE "orders"."id" = ? [["status", 0], ["updated_at", "2019-01-24 07:13:08.524320"], ["id", 1]] => true Full documentation most enums tin can exist found here.
18 Agreement Method Chaining
The Active Record pattern implements Method Chaining, which allow us to use multiple Active Record methods together in a simple and straightforward manner.
You can chain methods in a statement when the previous method called returns an ActiveRecord::Relation, similar all, where, and joins. Methods that return a single object (see Retrieving a Single Object Section) take to exist at the end of the statement.
There are some examples below. This guide won't cover all the possibilities, just a few as examples. When an Active Record method is called, the query is not immediately generated and sent to the database. The query is sent but when the data is really needed. So each instance beneath generates a single query.
18.1 Retrieving filtered data from multiple tables
Customer . select ( 'customers.id, customers.last_name, reviews.torso' ) . joins ( :reviews ) . where ( 'reviews.created_at > ?' , 1 . calendar week . ago ) The issue should be something similar this:
SELECT customers . id , customers . last_name , reviews . body FROM customers INNER JOIN reviews ON reviews . customer_id = customers . id WHERE ( reviews . created_at > '2019-01-08' ) 18.2 Retrieving specific data from multiple tables
Book . select ( 'books.id, books.championship, authors.first_name' ) . joins ( :author ) . find_by ( title: 'Abstraction and Specification in Program Development' ) The above should generate:
SELECT books . id , books . title , authors . first_name FROM books INNER JOIN authors ON authors . id = books . author_id WHERE books . title = $ ane [[ "title" , "Abstraction and Specification in Programme Development" ]] LIMIT 1 Note that if a query matches multiple records, find_by will fetch only the kickoff one and ignore the others (see the LIMIT 1 statement to a higher place).
nineteen Find or Build a New Object
It's common that you need to find a tape or create it if information technology doesn't exist. You can do that with the find_or_create_by and find_or_create_by! methods.
19.1 find_or_create_by
The find_or_create_by method checks whether a record with the specified attributes exists. If it doesn't, and so create is chosen. Let's see an case.
Suppose you want to find a client named "Andy", and if there'due south none, create one. Y'all can do so by running:
irb> Client . find_or_create_by ( first_name: 'Andy' ) => #< Customer id: five , first_name: "Andy" , last_name: zippo , title: nothing , visits: 0 , orders_count: zip , lock_version: 0 , created_at: "2019-01-17 07:06:45" , updated_at: "2019-01-17 07:06:45" > The SQL generated past this method looks like this:
SELECT * FROM customers WHERE ( customers . first_name = 'Andy' ) LIMIT ane BEGIN INSERT INTO customers ( created_at , first_name , locked , orders_count , updated_at ) VALUES ( '2011-08-thirty 05:22:57' , 'Andy' , 1 , Nada , '2011-08-30 05:22:57' ) COMMIT find_or_create_by returns either the record that already exists or the new tape. In our case, we didn't already have a customer named Andy so the record is created and returned.
The new record might not be saved to the database; that depends on whether validations passed or not (but like create).
Suppose we want to set the 'locked' aspect to false if we're creating a new record, only we don't want to include it in the query. So we want to find the customer named "Andy", or if that customer doesn't exist, create a client named "Andy" which is non locked.
We tin achieve this in two ways. The first is to use create_with:
Customer . create_with ( locked: false ). find_or_create_by ( first_name: 'Andy' ) The second manner is using a block:
Customer . find_or_create_by ( first_name: 'Andy' ) exercise | c | c . locked = false terminate The block will but be executed if the client is being created. The second fourth dimension nosotros run this lawmaking, the block will be ignored.
xix.2 find_or_create_by!
Yous tin also use find_or_create_by! to raise an exception if the new record is invalid. Validations are not covered on this guide, but allow'south assume for a moment that you lot temporarily add
validates :orders_count , presence: truthful to your Customer model. If you try to create a new Client without passing an orders_count, the record will be invalid and an exception volition be raised:
irb> Customer . find_or_create_by! ( first_name: 'Andy' ) ActiveRecord::RecordInvalid: Validation failed: Orders count can't be bare 19.3 find_or_initialize_by
The find_or_initialize_by method will work just similar find_or_create_by simply it will telephone call new instead of create. This ways that a new model instance will be created in memory merely won't exist saved to the database. Continuing with the find_or_create_by case, we at present desire the customer named 'Nina':
irb> nina = Client . find_or_initialize_by ( first_name: 'Nina' ) => #< Customer id: cypher , first_name: "Nina" , orders_count: 0 , locked: true , created_at: "2011-08-30 06:09:27" , updated_at: "2011-08-thirty 06:09:27" > irb> nina . persisted? => false irb> nina . new_record? => true Because the object is non yet stored in the database, the SQL generated looks like this:
SELECT * FROM customers WHERE ( customers . first_name = 'Nina' ) LIMIT one When you desire to salve it to the database, but call save:
twenty Finding past SQL
If y'all'd like to use your ain SQL to find records in a table y'all can use find_by_sql. The find_by_sql method will return an assortment of objects fifty-fifty if the underlying query returns simply a unmarried record. For instance you could run this query:
irb> Client . find_by_sql ( "SELECT * FROM customers INNER Bring together orders ON customers.id = orders.customer_id Club By customers.created_at desc" ) => [ #< Customer id: i , first_name: "Lucas" ... > , #< Customer id: 2 , first_name: "Jan" ... > , ... ] find_by_sql provides you with a simple way of making custom calls to the database and retrieving instantiated objects.
20.1 select_all
find_by_sql has a close relative chosen connection.select_all. select_all will call up objects from the database using custom SQL just like find_by_sql but will non instantiate them. This method will render an case of ActiveRecord::Outcome class and calling to_a on this object would return you lot an array of hashes where each hash indicates a record.
irb> Client . connectedness . select_all ( "SELECT first_name, created_at FROM customers WHERE id = '1'" ). to_a => [{ "first_name" => "Rafael" , "created_at" => "2012-11-10 23:23:45.281189" }, { "first_name" => "Eileen" , "created_at" => "2013-12-09 xi:22:35.221282" }] 20.2 pluck
pluck can exist used to query unmarried or multiple columns from the underlying table of a model. It accepts a list of column names as an statement and returns an array of values of the specified columns with the corresponding data type.
irb> Book . where ( out_of_print: true ). pluck ( :id ) SELECT id FROM books WHERE out_of_print = simulated => [ i , ii , 3 ] irb> Order . distinct . pluck ( :condition ) SELECT Distinct status FROM orders => [ "shipped" , "being_packed" , "cancelled" ] irb> Customer . pluck ( :id , :first_name ) SELECT customers.id, customers.first_name FROM customers => [[ 1 , "David" ], [ 2 , "Fran" ], [ 3 , "Jose" ]] pluck makes it possible to replace code similar:
Customer . select ( :id ). map { | c | c . id } # or Customer . select ( :id ). map ( & :id ) # or Customer . select ( :id , :first_name ). map { | c | [ c . id , c . first_name ] } with:
Customer . pluck ( :id ) # or Customer . pluck ( :id , :first_name ) Unlike select, pluck direct converts a database result into a Ruby Array, without constructing ActiveRecord objects. This tin mean better performance for a big or frequently-run query. However, any model method overrides volition not be available. For case:
class Client < ApplicationRecord def proper noun "I am #{ first_name } " finish end irb> Customer . select ( :first_name ). map & :name => [ "I am David" , "I am Jeremy" , "I am Jose" ] irb> Customer . pluck ( :first_name ) => [ "David" , "Jeremy" , "Jose" ] Y'all are not limited to querying fields from a single table, you lot tin query multiple tables as well.
irb> Guild . joins ( :customer , :books ). pluck ( "orders.created_at, customers.email, books.title" ) Furthermore, unlike select and other Relation scopes, pluck triggers an immediate query, and thus cannot exist chained with whatever further scopes, although it tin can work with scopes already constructed earlier:
irb> Client . pluck ( :first_name ). limit ( 1 ) NoMethodError: undefined method `limit' for #<Array:0x007ff34d3ad6d8> irb> Customer . limit ( i ). pluck ( :first_name ) => [ "David" ] You should also know that using pluck will trigger eager loading if the relation object contains include values, even if the eager loading is not necessary for the query. For example:
irb> assoc = Customer . includes ( :reviews ) irb> assoc . pluck ( :id ) SELECT "customers"."id" FROM "customers" LEFT OUTER Bring together "reviews" ON "reviews"."id" = "customers"."review_id" One way to avoid this is to unscope the includes:
irb> assoc . unscope ( :includes ). pluck ( :id ) 20.3 ids
ids can be used to pluck all the IDs for the relation using the table's primary central.
irb> Customer . ids SELECT id FROM customers class Customer < ApplicationRecord cocky . primary_key = "customer_id" cease irb> Customer . ids SELECT customer_id FROM customers 21 Existence of Objects
If you but desire to check for the existence of the object there's a method called exists?. This method volition query the database using the aforementioned query as discover, only instead of returning an object or drove of objects it will render either truthful or false.
The exists? method also takes multiple values, just the catch is that information technology volition return true if whatsoever one of those records exists.
Client . exists? ( id: [ ane , two , iii ]) # or Customer . exists? ( first_name: [ 'Jane' , 'Sergei' ]) Information technology'due south even possible to use exists? without any arguments on a model or a relation.
Customer . where ( first_name: 'Ryan' ). exists? The above returns truthful if there is at to the lowest degree one customer with the first_name 'Ryan' and false otherwise.
The in a higher place returns false if the customers table is empty and true otherwise.
Yous tin as well use whatever? and many? to bank check for existence on a model or relation. many? will use SQL count to decide if the item exists.
# via a model Club . any? # => SELECT i FROM orders LIMIT 1 Society . many? # => SELECT COUNT(*) FROM (SELECT ane FROM orders LIMIT 2) # via a named scope Gild . shipped . any? # => SELECT i FROM orders WHERE orders.status = 0 LIMIT 1 Order . shipped . many? # => SELECT COUNT(*) FROM (SELECT 1 FROM orders WHERE orders.status = 0 LIMIT 2) # via a relation Book . where ( out_of_print: true ). any? Book . where ( out_of_print: true ). many? # via an association Customer . showtime . orders . any? Customer . first . orders . many? 22 Calculations
This section uses count every bit an example method in this preamble, but the options described employ to all sub-sections.
All calculation methods piece of work directly on a model:
irb> Customer . count SELECT COUNT(*) FROM customers Or on a relation:
irb> Client . where ( first_name: 'Ryan' ). count SELECT COUNT(*) FROM customers WHERE (first_name = 'Ryan') Yous tin can besides employ various finder methods on a relation for performing complex calculations:
irb> Customer . includes ( "orders" ). where ( first_name: 'Ryan' , orders: { status: 'shipped' }). count Which volition execute:
SELECT COUNT ( Singled-out customers . id ) FROM customers LEFT OUTER JOIN orders ON orders . customer_id = customers . id WHERE ( customers . first_name = 'Ryan' AND orders . status = 0 ) assuming that Club has enum status: [ :shipped, :being_packed, :cancelled ].
22.i Count
If yous want to meet how many records are in your model'southward table you could call Client.count and that will return the number. If you want to be more specific and find all the customers with a championship nowadays in the database you can apply Client.count(:title).
For options, delight run into the parent section, Calculations.
22.2 Boilerplate
If you want to see the average of a certain number in ane of your tables you can call the boilerplate method on the class that relates to the table. This method phone call will await something like this:
Order . average ( "subtotal" ) This will return a number (maybe a floating-point number such as 3.14159265) representing the average value in the field.
For options, delight see the parent section, Calculations.
22.3 Minimum
If you lot want to notice the minimum value of a field in your table you lot tin call the minimum method on the class that relates to the table. This method call volition await something like this:
Gild . minimum ( "subtotal" ) For options, please see the parent section, Calculations.
22.4 Maximum
If you want to find the maximum value of a field in your table you can call the maximum method on the class that relates to the tabular array. This method call will look something like this:
Lodge . maximum ( "subtotal" ) For options, please see the parent department, Calculations.
22.5 Sum
If you want to observe the sum of a field for all records in your table you lot can phone call the sum method on the class that relates to the table. This method call will look something similar this:
For options, delight see the parent section, Calculations.
23 Running Explain
Y'all can run explain on a relation. Explain output varies for each database.
For instance, running
Customer . where ( id: 1 ). joins ( :orders ). explain may yield
EXPLAIN for: SELECT `customers`.* FROM `customers` INNER Bring together `orders` ON `orders`.`customer_id` = `customers`.`id` WHERE `customers`.`id` = one +----+-------------+------------+-------+---------------+ | id | select_type | table | type | possible_keys | +----+-------------+------------+-------+---------------+ | i | SIMPLE | customers | const | PRIMARY | | 1 | SIMPLE | orders | ALL | NULL | +----+-------------+------------+-------+---------------+ +---------+---------+-------+------+-------------+ | key | key_len | ref | rows | Extra | +---------+---------+-------+------+-------------+ | Master | 4 | const | ane | | | Nothing | NULL | NULL | 1 | Using where | +---------+---------+-------+------+-------------+ ii rows in prepare (0.00 sec) under MySQL and MariaDB.
Active Record performs a pretty printing that emulates that of the corresponding database beat. So, the aforementioned query running with the PostgreSQL adapter would yield instead
EXPLAIN for: SELECT "customers".* FROM "customers" INNER Join "orders" ON "orders"."customer_id" = "customers"."id" WHERE "customers"."id" = $one [["id", 1]] QUERY PLAN ------------------------------------------------------------------------------ Nested Loop (cost=four.33..xx.85 rows=4 width=164) -> Index Scan using customers_pkey on customers (cost=0.15..8.17 rows=i width=164) Index Cond: (id = 'one'::bigint) -> Bitmap Heap Scan on orders (toll=4.18..12.64 rows=4 width=eight) Recheck Cond: (customer_id = 'i'::bigint) -> Bitmap Index Browse on index_orders_on_customer_id (cost=0.00..4.eighteen rows=4 width=0) Index Cond: (customer_id = 'i'::bigint) (7 rows) Eager loading may trigger more than than one query under the hood, and some queries may need the results of previous ones. Considering of that, explain actually executes the query, and then asks for the query plans. For example,
Customer . where ( id: 1 ). includes ( :orders ). explain may yield this for MySQL and MariaDB:
Explain for: SELECT `customers`.* FROM `customers` WHERE `customers`.`id` = 1 +----+-------------+-----------+-------+---------------+ | id | select_type | table | type | possible_keys | +----+-------------+-----------+-------+---------------+ | 1 | SIMPLE | customers | const | PRIMARY | +----+-------------+-----------+-------+---------------+ +---------+---------+-------+------+-------+ | key | key_len | ref | rows | Actress | +---------+---------+-------+------+-------+ | PRIMARY | 4 | const | 1 | | +---------+---------+-------+------+-------+ 1 row in set (0.00 sec) EXPLAIN for: SELECT `orders`.* FROM `orders` WHERE `orders`.`customer_id` IN (1) +----+-------------+--------+------+---------------+ | id | select_type | tabular array | type | possible_keys | +----+-------------+--------+------+---------------+ | 1 | SIMPLE | orders | ALL | NULL | +----+-------------+--------+------+---------------+ +------+---------+------+------+-------------+ | key | key_len | ref | rows | Extra | +------+---------+------+------+-------------+ | Nil | Aught | Nil | ane | Using where | +------+---------+------+------+-------------+ 1 row in set (0.00 sec) and may yield this for PostgreSQL:
Customer Load (0.3ms) SELECT "customers".* FROM "customers" WHERE "customers"."id" = $1 [["id", 1]] Order Load (0.3ms) SELECT "orders".* FROM "orders" WHERE "orders"."customer_id" = $1 [["customer_id", 1]] => Explicate for: SELECT "customers".* FROM "customers" WHERE "customers"."id" = $one [["id", 1]] QUERY PLAN ---------------------------------------------------------------------------------- Index Scan using customers_pkey on customers (cost=0.15..8.17 rows=one width=164) Index Cond: (id = 'one'::bigint) (2 rows) 23.ane Interpreting EXPLAIN
Interpretation of the output of EXPLAIN is beyond the scope of this guide. The post-obit pointers may be helpful:
-
SQLite3: Explicate QUERY Programme
-
MySQL: Explain Output Format
-
MariaDB: EXPLAIN
-
PostgreSQL: Using Explicate
Feedback
You're encouraged to help improve the quality of this guide.
Please contribute if you lot encounter any typos or factual errors. To get started, y'all tin can read our documentation contributions section.
You may also find incomplete content or stuff that is not up to engagement. Please do add any missing documentation for main. Brand sure to check Edge Guides kickoff to verify if the issues are already stock-still or not on the main branch. Check the Ruddy on Rails Guides Guidelines for mode and conventions.
If for whatever reason you spot something to fix but cannot patch it yourself, please open an issue.
And last just non to the lowest degree, any kind of give-and-take regarding Ruby on Rails documentation is very welcome on the rubyonrails-docs mailing list.
0 Response to "Full to Queen Hook on Converter Rails Fashion Bed Group"
Post a Comment