From 6d39b29361522abeef8347033df9da0622f91e9c Mon Sep 17 00:00:00 2001 From: Philip Arndt Date: Fri, 28 Jun 2013 04:11:25 +1200 Subject: Prettied up the README with syntax highlighting. [ci skip] --- README.markdown | 105 ++++++++++++++++++++++++++++++++++++++------------------ 1 file changed, 71 insertions(+), 34 deletions(-) diff --git a/README.markdown b/README.markdown index 76fe730294..7f6bac1c75 100644 --- a/README.markdown +++ b/README.markdown @@ -21,13 +21,17 @@ For the moment, Arel uses Active Record's connection adapters to connect to the Generating a query with Arel is simple. For example, in order to produce - SELECT * FROM users +```sql +SELECT * FROM users +``` you construct a table relation and convert it to sql: - users = Arel::Table.new(:users) - query = users.project(Arel.sql('*')) - query.to_sql +```ruby +users = Arel::Table.new(:users) +query = users.project(Arel.sql('*')) +query.to_sql +``` ### More Sophisticated Queries @@ -35,45 +39,65 @@ Here is a whirlwind tour through the most common relational operators. These wil First is the 'restriction' operator, `where`: - users.where(users[:name].eq('amy')) - # => SELECT * FROM users WHERE users.name = 'amy' +```ruby +users.where(users[:name].eq('amy')) +# => SELECT * FROM users WHERE users.name = 'amy' +``` What would, in SQL, be part of the `SELECT` clause is called in Arel a `projection`: - users.project(users[:id]) # => SELECT users.id FROM users +```ruby +users.project(users[:id]) +# => SELECT users.id FROM users +``` Joins resemble SQL strongly: - users.join(photos).on(users[:id].eq(photos[:user_id])) - # => SELECT * FROM users INNER JOIN photos ON users.id = photos.user_id +```ruby +users.join(photos).on(users[:id].eq(photos[:user_id])) +# => SELECT * FROM users INNER JOIN photos ON users.id = photos.user_id +``` What are called `LIMIT` and `OFFSET` in SQL are called `take` and `skip` in Arel: - users.take(5) # => SELECT * FROM users LIMIT 5 - users.skip(4) # => SELECT * FROM users OFFSET 4 +```ruby +users.take(5) # => SELECT * FROM users LIMIT 5 +users.skip(4) # => SELECT * FROM users OFFSET 4 +``` `GROUP BY` is called `group`: - users.project(users[:name]).group(users[:name]) # => SELECT users.name FROM users GROUP BY users.name +```ruby +users.project(users[:name]).group(users[:name]) +# => SELECT users.name FROM users GROUP BY users.name +``` The best property of the Relational Algebra is its "composability", or closure under all operations. For example, to restrict AND project, just "chain" the method invocations: - users \ - .where(users[:name].eq('amy')) \ - .project(users[:id]) \ - # => SELECT users.id FROM users WHERE users.name = 'amy' +```ruby +users \ + .where(users[:name].eq('amy')) \ + .project(users[:id]) \ +# => SELECT users.id FROM users WHERE users.name = 'amy' +``` All operators are chainable in this way, and they are chainable any number of times, in any order. - users.where(users[:name].eq('bob')).where(users[:age].lt(25)) +```ruby +users.where(users[:name].eq('bob')).where(users[:age].lt(25)) +``` Of course, many of the operators take multiple arguments, so the last example can be written more tersely: - users.where(users[:name].eq('bob'), users[:age].lt(25)) +```ruby +users.where(users[:name].eq('bob'), users[:age].lt(25)) +``` The `OR` operator works like this: - users.where(users[:name].eq('bob').or(users[:age].lt(25))) +```ruby +users.where(users[:name].eq('bob').or(users[:age].lt(25))) +``` The `AND` operator behaves similarly. @@ -85,38 +109,51 @@ The examples above are fairly simple and other libraries match or come close to Suppose we have a table `products` with prices in different currencies. And we have a table `currency_rates`, of constantly changing currency rates. In Arel: - products = Arel::Table.new(:products) - products.columns # => [products[:id], products[:name], products[:price], products[:currency_id]] +```ruby +products = Arel::Table.new(:products) +products.columns +# => [products[:id], products[:name], products[:price], products[:currency_id]] - currency_rates = Arel::Table.new(:currency_rates) - currency_rates.columns # => [currency_rates[:from_id], currency_rates[:to_id], currency_rates[:date], currency_rates[:rate]] +currency_rates = Arel::Table.new(:currency_rates) +currency_rates.columns +# => [currency_rates[:from_id], currency_rates[:to_id], currency_rates[:date], currency_rates[:rate]] +``` Now, to order products by price in user preferred currency simply call: - products. - join(:currency_rates).on(products[:currency_id].eq(currency_rates[:from_id])). - where(currency_rates[:to_id].eq(user_preferred_currency), currency_rates[:date].eq(Date.today)). - order(products[:price] * currency_rates[:rate]) +```ruby +products. + join(:currency_rates).on(products[:currency_id].eq(currency_rates[:from_id])). + where(currency_rates[:to_id].eq(user_preferred_currency), currency_rates[:date].eq(Date.today)). + order(products[:price] * currency_rates[:rate]) +``` #### 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: - comments = Arel::Table.new(:comments) +```ruby +comments = Arel::Table.new(:comments) +``` And this table has the following attributes: - comments.columns # => [comments[:id], comments[:body], comments[:parent_id]] +```ruby +comments.columns +# => [comments[:id], comments[:body], comments[: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: - 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 +```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 +``` This will return the first comment's reply's body. ### License - + Arel is released under the [MIT License](http://opensource.org/licenses/MIT). -- cgit v1.2.3