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.
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).
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With