arel, how to join

In ARel (NOT ActiveRecord) we will do the following:

p = Arel::Table.new :products    # Base Rel-varc = Arel::Table.new :categories  # Base Rel-varpredicate = p[:category_id].eq( c[:id] ) # for equality predicatep.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.

Category.joins(:products).select("distinct categories.*").all

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.