Arel, Joins and Rails Queries

Question

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.

score 10 · Answer 1 · answered Jun 01 '12 at 11:16

10

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)

answered Jun 01 '12 at 11:16

user1430522

101
1
3

You can also just do `join_sources` joins will figure it out. I believe that would also pass all of them if your had multiple but don't quote me on the second part – Will Dec 19 '12 at 20:49

nessur · Answer 2 · 2011-09-30T17:43:26.257

8

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.

edited Sep 30 '11 at 17:43

answered Mar 17 '11 at 21:06

nessur

1,143
1
11
18

2

Your wordpress link is dead – Factor Mystic Dec 06 '12 at 21:52
I know! But it was a really good link back in early 2011. I'm sad that he's not showing off that project anymore. – nessur May 29 '13 at 21:52
1

archive.org link to zombie blog: http://web.archive.org/web/20110202025045/http://magicscalingsprinkles.wordpress.com/2010/01/28/why-i-wrote-arel/ – mrm Jun 09 '13 at 15:29

score 2 · Answer 3 · answered Feb 22 '11 at 04:00

2

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

answered Feb 22 '11 at 04:00

zetetic

47,184
10
111
119

Thanks zetetic. I'll give that a go instead. – Jason Feb 28 '11 at 21:37
1

That works, if you add a ".to_sql" to the end and drop it inside of `Request.find_by_sql(...)` – nessur Mar 31 '11 at 18:00

score 1 · Accepted Answer · answered Feb 22 '11 at 04:45

1

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

answered Feb 22 '11 at 04:45

DGM

26,629
7
58
79

Thanks DGM. I have seen MetaWhere but I was trying to avoid adding another gem when it seems there's facilities already in rails. I should take another look though. – Jason Feb 28 '11 at 21:36

Jeroen van Dijk · Answer 5 · 2011-09-14T14:11:12.023

0

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

edited Sep 14 '11 at 14:11

answered Jul 18 '11 at 14:01

Jeroen van Dijk

1,029
10
16

No need to use Arel::SqlLiteral Why not just joins('INNER JOIN "ads" ON "ads"."id" = "requests"."ad_id"') – Snuggs Sep 09 '11 at 23:56

Arel, Joins and Rails Queries

5 Answers5

Linked