diff options
Diffstat (limited to 'guides/source/active_record_querying.md')
| -rw-r--r-- | guides/source/active_record_querying.md | 90 |
1 files changed, 63 insertions, 27 deletions
diff --git a/guides/source/active_record_querying.md b/guides/source/active_record_querying.md index 784be91845..928ab43b3b 100644 --- a/guides/source/active_record_querying.md +++ b/guides/source/active_record_querying.md @@ -11,7 +11,7 @@ After reading this guide, you will know: * How to specify the order, retrieved attributes, grouping, and other properties of the found records. * How to use eager loading to reduce the number of database queries needed for data retrieval. * How to use dynamic finder methods. -* How to use method chaining to use multiple ActiveRecord methods together. +* How to use method chaining to use multiple Active Record methods together. * How to check for the existence of particular records. * How to perform various calculations on Active Record models. * How to run EXPLAIN on relations. @@ -50,7 +50,7 @@ class Role < ApplicationRecord end ``` -Active Record will perform queries on the database for you and is compatible with most database systems (MySQL, PostgreSQL and SQLite to name a few). Regardless of which database system you're using, the Active Record method format will always be the same. +Active Record will perform queries on the database for you 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 same. Retrieving Objects from the Database ------------------------------------ @@ -153,9 +153,9 @@ You can pass in a numerical argument to the `take` method to return up to that n ```ruby client = Client.take(2) # => [ - #<Client id: 1, first_name: "Lifo">, - #<Client id: 220, first_name: "Sara"> -] +# #<Client id: 1, first_name: "Lifo">, +# #<Client id: 220, first_name: "Sara"> +# ] ``` The SQL equivalent of the above is: @@ -192,10 +192,10 @@ You can pass in a numerical argument to the `first` method to return up to that ```ruby client = Client.first(3) # => [ - #<Client id: 1, first_name: "Lifo">, - #<Client id: 2, first_name: "Fifo">, - #<Client id: 3, first_name: "Filo"> -] +# #<Client id: 1, first_name: "Lifo">, +# #<Client id: 2, first_name: "Fifo">, +# #<Client id: 3, first_name: "Filo"> +# ] ``` The SQL equivalent of the above is: @@ -243,10 +243,10 @@ You can pass in a numerical argument to the `last` method to return up to that n ```ruby client = Client.last(3) # => [ - #<Client id: 219, first_name: "James">, - #<Client id: 220, first_name: "Sara">, - #<Client id: 221, first_name: "Russel"> -] +# #<Client id: 219, first_name: "James">, +# #<Client id: 220, first_name: "Sara">, +# #<Client id: 221, first_name: "Russel"> +# ] ``` The SQL equivalent of the above is: @@ -472,6 +472,12 @@ NOTE: Only equality, range and subset checking are possible with Hash conditions Client.where(locked: true) ``` +This will generate SQL like this: + +```sql +SELECT * FROM clients WHERE (clients.locked = 1) +``` + The field name can also be a string: ```ruby @@ -517,13 +523,17 @@ SELECT * FROM clients WHERE (clients.orders_count IN (1,3,5)) ### NOT Conditions -`NOT` SQL queries can be built by `where.not`. +`NOT` SQL queries can be built by `where.not`: ```ruby -Article.where.not(author: author) +Client.where.not(locked: true) ``` -In other words, this query can be generated by calling `where` with no argument, then immediately chain with `not` passing `where` conditions. +In other words, this query can be generated by calling `where` with no argument, then immediately chain with `not` passing `where` conditions. This will generate SQL like this: + +```sql +SELECT * FROM clients WHERE (clients.locked != 1) +``` Ordering -------- @@ -1085,6 +1095,8 @@ SELECT categories.* FROM categories INNER JOIN tags ON tags.article_id = articles.id ``` +Or, in English: "return all categories that have articles, where those articles have a comment made by a guest, and where those articles also have a tag." + #### Specifying Conditions on the Joined Tables You can specify conditions on the joined tables using the regular [Array](#array-conditions) and [String](#pure-string-conditions) conditions. [Hash conditions](#hash-conditions) provide a special syntax for specifying conditions for the joined tables: @@ -1109,7 +1121,7 @@ If you want to select a set of records whether or not they have associated records you can use the `left_outer_joins` method. ```ruby -Author.left_outer_joins(:posts).uniq.select('authors.*, COUNT(posts.*) AS posts_count').group('authors.id') +Author.left_outer_joins(:posts).distinct.select('authors.*, COUNT(posts.*) AS posts_count').group('authors.id') ``` Which produces: @@ -1294,6 +1306,28 @@ Using a class method is the preferred way to accept arguments for scopes. These category.articles.created_before(time) ``` +### Using conditionals + +Your scope can utilize conditionals: + +```ruby +class Article < ApplicationRecord + scope :created_before, ->(time) { where("created_at < ?", time) if time.present? } +end +``` + +Like the other examples, this will behave similarly to a class method. + +```ruby +class Article < ApplicationRecord + def self.created_before(time) + where("created_at < ?", time) if time.present? + end +end +``` + +However, there is one important caveat: A scope will always return an `ActiveRecord::Relation` object, even if the conditional evaluates to `false`, whereas a class method, will return `nil`. This can cause `NoMethodError` when chaining class methods with conditionals, if any of the conditionals return `false`. + ### Applying a default scope If we wish for a scope to be applied across all queries to the model we can use the @@ -1589,7 +1623,7 @@ now want the client named 'Nick': ```ruby nick = Client.find_or_initialize_by(first_name: 'Nick') -# => <Client id: nil, first_name: "Nick", orders_count: 0, locked: true, created_at: "2011-08-30 06:09:27", updated_at: "2011-08-30 06:09:27"> +# => #<Client id: nil, first_name: "Nick", orders_count: 0, locked: true, created_at: "2011-08-30 06:09:27", updated_at: "2011-08-30 06:09:27"> nick.persisted? # => false @@ -1621,10 +1655,10 @@ Client.find_by_sql("SELECT * FROM clients INNER JOIN orders ON clients.id = orders.client_id ORDER BY clients.created_at desc") # => [ - #<Client id: 1, first_name: "Lucas" >, - #<Client id: 2, first_name: "Jan" >, - # ... -] +# #<Client id: 1, first_name: "Lucas" >, +# #<Client 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. @@ -1636,9 +1670,9 @@ Client.find_by_sql("SELECT * FROM clients ```ruby Client.connection.select_all("SELECT first_name, created_at FROM clients WHERE id = '1'") # => [ - {"first_name"=>"Rafael", "created_at"=>"2012-11-10 23:23:45.281189"}, - {"first_name"=>"Eileen", "created_at"=>"2013-12-09 11:22:35.221282"} -] +# {"first_name"=>"Rafael", "created_at"=>"2012-11-10 23:23:45.281189"}, +# {"first_name"=>"Eileen", "created_at"=>"2013-12-09 11:22:35.221282"} +# ] ``` ### `pluck` @@ -1891,7 +1925,7 @@ EXPLAIN for: SELECT `users`.* FROM `users` INNER JOIN `articles` ON `articles`.` 2 rows in set (0.00 sec) ``` -under MySQL. +under MySQL and MariaDB. Active Record performs a pretty printing that emulates that of the corresponding database shell. So, the same query running with the @@ -1951,7 +1985,7 @@ EXPLAIN for: SELECT `articles`.* FROM `articles` WHERE `articles`.`user_id` IN 1 row in set (0.00 sec) ``` -under MySQL. +under MySQL and MariaDB. ### Interpreting EXPLAIN @@ -1962,4 +1996,6 @@ following pointers may be helpful: * MySQL: [EXPLAIN Output Format](http://dev.mysql.com/doc/refman/5.7/en/explain-output.html) +* MariaDB: [EXPLAIN](https://mariadb.com/kb/en/mariadb/explain/) + * PostgreSQL: [Using EXPLAIN](http://www.postgresql.org/docs/current/static/using-explain.html) |