arel, how to join
18,581
Solution 1
Category.joins(:products).select("distinct categories.*").all
Solution 2
In ARel (NOT ActiveRecord) we will do the following:
p = Arel::Table.new :products # Base Rel-var
c = Arel::Table.new :categories # Base Rel-var
predicate = p[:category_id].eq( c[:id] ) # for equality predicate
p.join(c) # Natural join
.on( predicate ) # Equi-Join
.group( p[:category_id] ) # Grouping expression to get distinct categories
.project( c[:id] ) # Project the distinct category IDs of the derived set.
Solution 3
Another, simpler, approach is to use the ActiveRecord query interface's join
in conjunction with ARel for the conditional statement:
joins(:user)
.where(User.arel_table[:name].matches("%#{query}%"))
Generates the following sql in sqlite3:
"SELECT \"patients\".* FROM \"patients\" INNER JOIN \"users\" ON \"users\".\"id\" = \"patients\".\"user_id\" WHERE (\"users\".\"name\" LIKE '%query%')"
And the following sql in postgres (notice the ILIKE):
"SELECT \"patients\".* FROM \"patients\" INNER JOIN \"users\" ON \"users\".\"id\" = \"patients\".\"user_id\" WHERE (\"users\".\"name\" ILIKE '%query%')"
This allows you to join with simplicity, but still get the abstraction of the ARel matcher to your RDBMS.
Author by
Jan
Updated on July 30, 2022Comments
-
Jan over 1 year
Given
class Category < ActiveRecord::Base has_many :products, :order => 'name ASC' end
Using the Rails 3 stack, how can I query for all categories that 'have' products?
-
Jan over 13 yearsDo you happen to know a good reference on what is possible with arel queries?
-
gertas over 13 yearsI take my knowledge from googling, reading Rails-elite blogs. Important thing: ActiveRecord differs much from pure Arel.
-
Translunar over 13 yearsWhen you say "In ARel (NOT ActiveRecord)," what precisely do you mean? That we can't do this in a class derived from
ActiveRecord::Base
? This is confusing. -
bradgonesurfing over 12 yearsIn all these examples I never see the final step folding the AREL query back into Rails to get real records. What to do with p now? You can call to_sql on it for sure but how to turn it into an ActiveRecord::Relation that will load records?
-
Tim Fletcher over 11 years@bradgonesurfing Just stick it in an AR where method. ARel just generates SQL.
-
bradgonesurfing over 11 yearsThe above is a join and can't be stuck in a where method.
-
sockmonk over 8 yearsFor an arel that generates a SELECT (not just a where clause), you use
find_by_sql(arel_expression.to_sql)
. This will return an array of records, not a chainable ActiveRecord::Relation, but at least it gives you the results. -
Harry Wood over 8 years@bradgonesurfing is asking a different question here really. Here is that question, and its answer: stackoverflow.com/questions/13442073/… Briefly, a good answer is to use
.all.each
, and loop (Usingfind_by_sql
works, but is not a good answer) That's a basic ActiveRecord thing, which is why people tend to leave out these next steps, when answering questions like this about ARel querying.