Stickies

Playing with arel

users table stuff

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

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

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

... with conditions

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

... with a join, can get tricky

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

Returns ...

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

using additional Arel tables seems to clear this up

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

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

get a relation's columns

users.columns.map(&:name)

Updating tables

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...

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... ?

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 ...

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)

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

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

The top level Arel classes

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:

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

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

... or day of the year

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

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 ...

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 ...

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