Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Arel, Joins and Rails Queries

I've been stuck on a problem recently for a little while and found my way to Arel which looks like it should allow me to do OR's in my queries.

As a starting point I needed to convert an existing Rails 3 query to Arel and that's where I've run into problems.

The following scope and query works as I would expect it to. It gives me the requests associated with a particular user's ads.

#in the Request class
scope :responder, lambda { |user| joins(:ad).where(:ads => { :user_id => user }) }

Request.responder(303).to_sql

=> "SELECT \"requests\".* FROM \"requests\" INNER JOIN \"ads\" ON \"ads\".\"id\" = \"requests\".\"ad_id\" WHERE (\"ads\".\"user_id\" = 303)"

According to doco on the Arel github page and Railscast 215 I should be able to do something like the following to replicate the query with Arel

  requests = Request.arel_table
  ads = Ad.arel_table
  where(requests.join(ads).on(ads[:id].eq(requests[:ad_id])))

This results in an error

TypeError: Cannot visit Arel::SelectManager

I can do the following in the console though

r = Request.arel_table
a = Ad.arel_table

r.join(a).to_sql
 => "SELECT  FROM \"requests\" INNER JOIN \"ads\" "

So it looks like it's forming the SQL request, however when you put that in a where

Request.where(r.join(a)).to_sql

I get the following

TypeError: Cannot visit Arel::SelectManager....

I've tried doing other Arel actions within the where and it works (e.g.)

Request.where(r[:status].eq(nil)).to_sql
 => "SELECT \"requests\".* FROM \"requests\" WHERE (\"requests\".\"status\" IS NULL)"

This is a little beyond my growing rails/ruby knowledge. Any ideas?

Thanks in advance.

like image 868
Jason Avatar asked Feb 22 '11 00:02

Jason


People also ask

What is Arel query?

Arel is a SQL abstraction that ActiveRecord uses to build SQL queries. Arel wraps each component of the SQL query language with Ruby objects and provides an expressive DSL for composing SQL queries. When using Arel, you're mainly interacting with tables ( Arel::Table ) and nodes ( Arel::Nodes::Node subclasses).

What is Arel SQL rails?

Arel is a library that was introduced in Rails 3 for use in constructing SQL queries. Every time you pass a hash to where, it goes through Arel eventually. Rails exposes this with a public API that we can hook into when we need to build a more complex query.


4 Answers

You can use join_sources.first on Arel::SelectManager and the pass this to joins

requests = Request.arel_table
ads = Ad.arel_table
Request.joins(requests.join(ads).on(ads[:id].eq(requests[:ad_id])).join_sources.first)
like image 137
user1430522 Avatar answered Nov 12 '22 18:11

user1430522


The larger answer here, is that despite the fancy, inspiring example here: http://magicscalingsprinkles.wordpress.com/2010/01/28/why-i-wrote-arel/

...Active Record doesn't actually support full Arel functionality. At least, not through relations or scopes. You can create any (most) queries in Arel, but in the end you're going to be using:

sql = User.arel_table.(something awesome in Arel).to_sql
users = User.find_by_sql(sql)

Technically, there's still a ".to_a" method, but it doesn't return ActiveRecord model instances, and it's deprecated.

{UPDATE}

http://erniemiller.org/projects/squeel/ is the best thing to happen to ActiveRecord, ever. All that Arel stuff, realized.

like image 23
nessur Avatar answered Nov 12 '22 17:11

nessur


I'm no Arel expert, but I think you want to combine the join and where scopes instead of including one within the other. So instead of

where(requests.join(ads).on(ads[:id].eq(requests[:ad_id])))

try (for instance):

requests.join(ads).on(ads[:id].eq(requests[:ad_id])).where(requests[:status].eq(nil))
like image 33
zetetic Avatar answered Nov 12 '22 16:11

zetetic


To do OR's in rails 3, check out MetaWhere. There's a nice railscast on it: http://railscasts.com/episodes/251-metawhere-metasearch

like image 34
DGM Avatar answered Nov 12 '22 18:11

DGM