How can I generate a select from an aliased table in Arel?

Question

I need to generate SQL using Arel with the form

SELECT c2.user_id, MAX(c2.created_at) as max_created_at
FROM comments AS c2
GROUP BY c2.user_id

to be used as a subquery in a larger query

SELECT *
FROM comments
INNER JOIN (...subquery...) s1
ON comments.user_id = s1.user_id
AND comments.created_at = s1.max_created_at

and I can't figure out how to alias the comments table in the subquery.

The closest I can get is

c2 = Comment.arel_table.alias
s1 = Comment.arel_table.project(
    c2[:user_id], c2[:created_at].maximum.as('max_created_at')
    ).group('user_id').as('s1')

but this generates the incorrect SQL

SELECT c2.user_id, MAX(c2.created_at) as max_created_at
FROM comments
GROUP BY c2.user_id

(Errors because c2 isn't defined)

Generating the query without aliasing leads to incorrect results as the table names inside and out the subquery collide.

This gives the error that Arel::TableAlias has to project method.

s1 = c2.project(...

How can I query an aliased table using Arel?

Plastered

Answer 1

You can use from to tell it which table (or in this case, table alias) to project from:

c2 = Comment.arel_table.alias
s1 = Comment.arel_table.
  project(c2[:user_id], c2[:created_at].maximum.as('max_created_at')).
  from(c2).group('user_id').as('s1')
puts s1.to_sql
# (SELECT "comments_2"."user_id", MAX("comments_2"."created_at") AS max_created_at
#  FROM "comments" "comments_2" GROUP BY user_id) s1

Honorine