如何从Arel中的别名表中生成选择？

I need to generate SQL using Arel with the form

我需要使用带有表单的Arel生成SQL

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

但是这会生成不正确的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)

（错误，因为没有定义c2）

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.

这给出了Arel :: TableAlias必须使用项目方法的错误。

s1 = c2.project(...

How can I query an aliased table using Arel?

如何使用Arel查询别名表？

1 个解决方案

#1

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

您可以使用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