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).t开发者_如何学Pythono_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.

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)

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.

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

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

The most flexible solution I have found so far is something like the following:

class Request
  class << self
    def your_join_scope
      joins('INNER JOIN "ads" ON "ads"."id" = "requests"."ad_id"')
    end
  end
end

The power of it is that you can mix and match it with your other scopes