Convert complex SQL to ActiveRecord Query-CodePudding

How can I convert the following query to be cleaner and utilize ActiveRecord's API more effectively? My biggest stumbling block is the aliased table -- I don't quite understand how to declare it and use it with the API.

          subq = MyTable.joins(
            'LEFT JOIN my_table AS T2 ON'\
            '('\
              'T2.other_table_id = my_table.other_table_id '\
              'AND T2.activity_date > my_table.activity_date'\
             ')'
          )
            .where('T2.other_table_id IS NULL')
            .where(my_table_type_id: ['foo', 'bar'])
            .select(:other_table_id)

          OtherTable.where(id: subq)

CodePudding user response：

You can do this using Arel (Rails underlying query assembler) like so:

my_table = MyTable.arel_table 
t2 = my_table.alias("t2")
join = Arel::Nodes::OuterJoin.new(t2,
          t2.create_on(
            t2[:other_table_id].eq(my_table[:other_table_id])
            .and(
               t2[:activity_date].gt(my_table[:activity_date])
            )
          ))
sub_q = MyTable.joins(join)
   .where(t2[:other_table_id].eq(nil))
   .where(my_table_type_id: ['foo', 'bar'])
   .select(:other_table_id)

OtherTable.where(id: sub_q)

End Result should be

SELECT 
  other_tables.* 
FROM 
  other_tables
WHERE 
  other_tables.id IN ( 
    SELECT 
      my_tables.other_table_id 
    FROM 
      my_tables 
      LEFT OUTER JOIN my_tables t2 ON t2.other_table_id = my_tables.other_table_id 
        AND t2.activity_date > my_tables.activity_date 
    WHERE 
       t2.other_table_id IS NULL AND 
       my_tables.my_table_type_id IN ('foo','bar')
   )