diff options
author | bjhaid <abejideayodele@gmail.com> | 2014-05-02 13:56:24 -0500 |
---|---|---|
committer | bjhaid <abejideayodele@gmail.com> | 2014-05-02 14:59:06 -0500 |
commit | b5b13f254bb2a395bcde6b16e7d811339d634c01 (patch) | |
tree | 6e958480b2b04f9d20b497f14f47e034c8418326 | |
parent | bb05bf01ce7243aba06ba0fd6e7a3da65aa77fbb (diff) | |
download | rails-b5b13f254bb2a395bcde6b16e7d811339d634c01.tar.gz rails-b5b13f254bb2a395bcde6b16e7d811339d634c01.tar.bz2 rails-b5b13f254bb2a395bcde6b16e7d811339d634c01.zip |
Updated README
-rw-r--r-- | README.markdown | 74 |
1 files changed, 74 insertions, 0 deletions
diff --git a/README.markdown b/README.markdown index 99263dc2d2..8285bbfd12 100644 --- a/README.markdown +++ b/README.markdown @@ -53,6 +53,18 @@ users.project(users[:id]) # => SELECT users.id FROM users ``` +Comparison operators `=`, `!=`, `<`, `>`, `<=`, `>=`, `IN`: + +```ruby +users.where(users[:age].eq(10)).project(Arel.sql('*')) # => SELECT * FROM "users" WHERE "users"."age" = 10 +users.where(users[:age].not_eq(10)).project(Arel.sql('*')) # => SELECT * FROM "users" WHERE "users"."age" != 10 +users.where(users[:age].lt(10)).project(Arel.sql('*')) # => SELECT * FROM "users" WHERE "users"."age" < 10 +users.where(users[:age].gt(10)).project(Arel.sql('*')) # => SELECT * FROM "users" WHERE "users"."age" > 10 +users.where(users[:age].lteq(10)).project(Arel.sql('*')) # => SELECT * FROM "users" WHERE "users"."age" <= 10 +users.where(users[:age].gteq(10)).project(Arel.sql('*')) # => SELECT * FROM "users" WHERE "users"."age" >= 10 +users.where(users[:age].in([20, 16, 17])).project(Arel.sql('*')) # => SELECT * FROM "users" WHERE "users"."age" IN (20, 16, 17) +``` + Joins resemble SQL strongly: ```ruby @@ -60,6 +72,13 @@ users.join(photos).on(users[:id].eq(photos[:user_id])) # => SELECT * FROM users INNER JOIN photos ON users.id = photos.user_id ``` +Left Joins + +```ruby +users.join(photos, Arel::Nodes::OuterJoin).on(users[:id].eq(photos[:user_id])) +# => SELECT FROM users LEFT OUTER JOIN photos ON users.id = photos.user_id +``` + What are called `LIMIT` and `OFFSET` in SQL are called `take` and `skip` in Arel: ```ruby @@ -97,6 +116,23 @@ users.where(users[:name].eq('bob').or(users[:age].lt(25))) The `AND` operator behaves similarly. +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].count) # => SELECT COUNT(users.age) FROM users +``` + +Aliasing Aggregate Functions: + +```ruby +users.project(users[:age].average.as("mean_age")) # => SELECT AVG(users.age) AS mean_age FROM users +``` + ### The Crazy Features The examples above are fairly simple and other libraries match or come close to matching the expressiveness of Arel (e.g., `Sequel` in Ruby). @@ -147,6 +183,44 @@ comments_with_replies = \ This will return the first comment's reply's body. +[Common Table Expresssions(CTE)](https://en.wikipedia.org/wiki/Common_table_expressions#Common_table_expression) support via: + +Create a `CTE` + +```ruby +cte_table = Arel::Table.new(:cte_table) +composed_cte = Arel::Nodes::As.new(cte_table, photos.where(photos[:created_at].gt(Date.current))) +``` + +Use the created `CTE`: + +```ruby +users. + join(cte_table).on(users[:id].eq(cte_table[:user_id])). + 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 +``` + +When your query is too complex for `Arel`, you can use `Arel::SqlLiteral`: + +```ruby +photo_clicks =Arel::Nodes::SqlLiteral.new(<<-SQL + CASE WHEN condition1 THEN calculation1 + WHEN condition2 THEN calculation2 + WHEN condition3 THEN calculation3 + ELSE default_calculation END +SQL +) +photos.project(photo_clicks.as("photo_clicks")) +# => SELECT CASE WHEN condition1 THEN calculation1 + WHEN condition2 THEN calculation2 + WHEN condition3 THEN calculation3 + ELSE default_calculation END + FROM "photos" +``` + ### License Arel is released under the [MIT License](http://opensource.org/licenses/MIT). |