users table stuff

I like to use an 'arex' helper method to wrap arbitrary active record queries...

``` ruby def arex query ActiveRecord::Base.connection.execute query end users = Arel::Table.new(:user) basic_query = users.project(Arel.sql(sql('*'))) # select all fields basic_query = users.project(users[:id]) # select one field ```

specify fields

``` ruby specific_query = users.project([users[:id], users[:username]]) ```

... with conditions

``` ruby specific_query = users.project([users[:id], users[:username]]).where(users[:id].eq(1)) ```

... with a join, can get tricky

``` ruby join_query = users.project([users[:id], users[:username]]).join(:user_profile).on(users[:id].eq('user_profile.user_id')) ```

Returns ...

``` ruby "SELECT \"user\".\"id\", \"user\".\"username\" FROM \"user\" INNER JOIN 'user_profile' ON \"user\".\"id\" = 0" ```

using additional Arel tables seems to clear this up

``` ruby profiles = Arel::Table.new(:user_profile) join_query = users.project([users[:id], users[:username]]).join(profiles).on(users[:id].eq(profiles['user_id'])) ```

and you can relax it a bit too

``` ruby join_query = users.project('*').join(profiles).on(users[:id].eq(profiles['user_id'])) ```

get a relation's columns

``` ruby users.columns.map(&:name) ```

Updating tables

``` ruby crudder = Arel::SelectManager.new users.engine crudder.compile_update([[users[:username], "steveo@lyti.cs"]]).where(users[:id].eq(1)).to_sql #=> "UPDATE \"user\" SET \"username\" = 'steveo@lyti.cs' WHERE \"user\".\"id\" = 1" ```

Get join table relations...

``` ruby profiles.project(Arel.star).join(user_groups).on(user_groups[:user_id].eq(profiles[:user_id])).where(user_groups[:group_id].eq(3)) ```

So ... stored procedures :D

Could you express the following as Arel... ?

``` ruby ActiveRecord::Base.connection.execute "select id, (select * from my_schema.account_ref(u.id)) as bal from user as u where u.id = 109" ```

And the answer, as it turns out, is yes, somewhat ...

``` ruby users = Arel::Table.new(:user) users.table_alias = 'u' manager = Arel::SelectManager.new users.engine manager.project Arel.star manager.from Arel.sql("my_schema.account_ref(#{users.table_alias}.id)") ```

In sql you have column aliases (AS) and table (or *relation*) aliases (table_alias)

``` ruby users.project([users[:id], manager.as('bal')]) arex users.project([users[:id], manager.as('bal')]).where(users[:id].eq(109)).to_sql ```

sadly this is considerably more verbose than just using connection.execute with the sql

``` ruby arex "select * from my_schema.account_ref(42)" ```

The top level Arel classes

``` ruby Arel::AliasPredication Arel::Attribute Arel::Attributes Arel::Compatibility Arel::Crud Arel::DeleteManager Arel::Expression Arel::Expressions Arel::InnerJoin Arel::InsertManager Arel::Math Arel::Node Arel::Nodes Arel::OrderPredications Arel::OuterJoin Arel::Predications Arel::Relation Arel::SelectManager Arel::Sql Arel::SqlLiteral Arel::Table Arel::TreeManager Arel::UpdateManager Arel::VERSION Arel::Visitors ```

Using arel for data analysis

The nice thing about active record is that it works for mvc really well But if you want to do data analysis, such as working out the function of some data, it's not so good

So if we want to analyse bidding data:

``` ruby bids = Arel::Table.new(Bid.table_name) res = arex bids.project(bids[:id]).where(bids[:id].eq(1)).to_sql ```

How many bids placed by day of the week

``` ruby res = arex bids.project(bids[:id].count).group("extract(dow from #{bids.name}.created_at)").to_sql ```

... or day of the year

``` ruby res = arex bids.project(bids[:id].count).group("extract(doy from #{bids.name}.created_at)").to_sql ```

add in the date as a field, and get the number per month since launch

``` ruby res = arex bids.project(bids[:id].count, "date_trunc('month', #{bids.name}.created_at) as gdate").group("gdate").to_sql ```

get the number per day ...

``` ruby res = arex bids.project(bids[:id].count, "date_trunc('day', #{bids.name}.created_at) as group_date").group("group_date").to_sql ```

add in the status of the bid ...

``` ruby res = arex bids.project(bids[:id].count, "date_trunc('day', #{bids.name}.created_at) as group_date", bids[:status]).group("group_date", bids[:status]).to_sql ```