diff options
Diffstat (limited to 'README.markdown')
-rw-r--r-- | README.markdown | 21 |
1 files changed, 11 insertions, 10 deletions
diff --git a/README.markdown b/README.markdown index 75b0b3ee0b..1776330da8 100644 --- a/README.markdown +++ b/README.markdown @@ -120,10 +120,10 @@ Aggregate functions `AVG`, `SUM`, `COUNT`, `MIN`, `MAX`, `HAVING`: ```ruby photos.group(photos[:user_id]).having(photos[:id].count.gt(5)) # => SELECT FROM photos GROUP BY photos.user_id HAVING COUNT(photos.id) > 5 -users.project(users[:age].sum) # => SELECT SUM(users.age) AS sum_id FROM users -users.project(users[:age].average) # => SELECT AVG(users.age) AS avg_id FROM users -users.project(users[:age].maximum) # => SELECT MAX(users.age) AS max_id FROM users -users.project(users[:age].minimum) # => SELECT MIN(users.age) AS min_id FROM users +users.project(users[:age].sum) # => SELECT SUM(users.age) FROM users +users.project(users[:age].average) # => SELECT AVG(users.age) FROM users +users.project(users[:age].maximum) # => SELECT MAX(users.age) FROM users +users.project(users[:age].minimum) # => SELECT MIN(users.age) FROM users users.project(users[:age].count) # => SELECT COUNT(users.age) FROM users ``` @@ -160,7 +160,7 @@ products. #### Complex Joins -Where Arel really shines in its ability to handle complex joins and aggregations. As a first example, let's consider an "adjacency list", a tree represented in a table. Suppose we have a table `comments`, representing a threaded discussion: +Where Arel really shines is in its ability to handle complex joins and aggregations. As a first example, let's consider an "adjacency list", a tree represented in a table. Suppose we have a table `comments`, representing a threaded discussion: ```ruby comments = Arel::Table.new(:comments) @@ -172,16 +172,17 @@ And this table has the following attributes: # [:id, :body, :parent_id] ``` -The `parent_id` column is a foreign key from the `comments` table to itself. Now, joining a table to itself requires aliasing in SQL. In fact, you may alias in Arel as well: +The `parent_id` column is a foreign key from the `comments` table to itself. +Joining a table to itself requires aliasing in SQL. This aliasing can be handled from Arel as below: ```ruby replies = comments.alias comments_with_replies = \ - comments.join(replies).on(replies[:parent_id].eq(comments[:id])) -# => SELECT * FROM comments INNER JOIN comments AS comments_2 WHERE comments_2.parent_id = comments.id + comments.join(replies).on(replies[:parent_id].eq(comments[:id])).where(comments[:id].eq(1)) +# => SELECT * FROM comments INNER JOIN comments AS comments_2 WHERE comments_2.parent_id = comments.id AND comments.id = 1 ``` -This will return the first comment's reply's body. +This will return the reply for the first comment. [Common Table Expresssions(CTE)](https://en.wikipedia.org/wiki/Common_table_expressions#Common_table_expression) support via: @@ -200,7 +201,7 @@ users. project(users[:id], cte_table[:click].sum). with(composed_cte) -# => WITH cte_table AS (SELECT FROM photos WHERE photos.created_at > '2014-05-02') SELECT users.id, SUM(cte_table.click) AS sum_id FROM users INNER JOIN cte_table ON users.id = cte_table.user_id +# => WITH cte_table AS (SELECT FROM photos WHERE photos.created_at > '2014-05-02') SELECT users.id, SUM(cte_table.click) FROM users INNER JOIN cte_table ON users.id = cte_table.user_id ``` When your query is too complex for `Arel`, you can use `Arel::SqlLiteral`: |