Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

arel, how to join

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?

like image 260
Jan Avatar asked Sep 17 '10 00:09

Jan


People also ask

What is Arel SQL rails?

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.

What is Arel 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).


3 Answers

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.

like image 53
eprothro Avatar answered Oct 02 '22 20:10

eprothro


Category.joins(:products).select("distinct categories.*").all
like image 45
gertas Avatar answered Oct 02 '22 19:10

gertas


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.
like image 24
Snuggs Avatar answered Oct 02 '22 19:10

Snuggs