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?
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.
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).
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.
Category.joins(:products).select("distinct categories.*").all
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.
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