How to write this query using Arel

Question

I have just started using Arel (with MySQL) and I am comfortable with basic queries. However I am stuck in multi-joins. I have the below query for which I would like use Arel? Could do with some help.

 SELECT count(*)
  FROM    table_1 p
       LEFT JOIN
          (SELECT pid
             FROM table_2 s LEFT JOIN table_3 i ON s.key = i.key
            WHERE i.default = 'y') AS table_4
       ON p.pid = table_4.pid AND isnull(table_4.pid) AND p.show = 'y'

This is what I have managed so far (obviously the Final query is not working)

=> Sub-query

table_2.select(:pid).joins(table_3).
   where(:table_3 => {:default => 'y'}).as('table_4')

=> Final

table_1.joins(:table_1 => :table_4).
   where (:table_4 => {ISNULL(:pid)}, :table_1 => {:show = 'y'})

Answer 1

You can do it like this. I removed aliasing where it wasn't necessary, but you can add it back as desired:

table_1 = Arel::Table.new(:table_1)
table_2 = Arel::Table.new(:table_2)
table_3 = Arel::Table.new(:table_3)

table_4 = table_2
  .join(table_3, Arel::Nodes::OuterJoin) # specifies join using LEFT OUTER
  .on(table_2[:key].eq(table_3[:key])) # defines join keys
  .where(table_3[:default].eq('y')) # defines your equals condition
  .project(table_2[:pid]).as('table_4') # AREL uses project not select

query = table_1
  .join(table_4, Arel::Nodes::OuterJoin)
  .on(table_1[:pid].eq(table_4[:pid]))
  .where(table_4[:pid].eq(nil).and(table_1[:show].eq('y'))) # multiple conditions
  .project("count(*)")

# The AREL documentation is pretty good: 
#   https://github.com/rails/arel/blob/master/README.markdown

# If you are using ActiveRecord you can do:
ActiveRecord::Base.connection.execute(query.to_sql)