たとえば
「ユーザー1がコメントした記事の中で、コメントのlike数が1以上の記事を検索する」
SELECT `posts`.*
FROM `posts`
INNER JOIN (
SELECT `comments`.*
FROM `comments`
WHERE
`comments`.`user_id` = 1 AND
`comments`.`likes_count` >= 1
) liked_user_comments ON liked_user_comments.`post_id` = `posts`.`id`
こんなのをRailsで書くには？
コード
Arel::Table オブジェクトの取得
user = User.find(1)
posts = Post.arel_table
comments = Comment.arel_table
サブクエリの組み立て
.project などによってできる Arel::SelectManagerオブジェクト の .as を呼ぶと Arel::Nodes::TableAliasオブジェクト を得ることができ、テーブルと同様に扱うことができるようになります。
liked_user_comments = comments
.project(Arel.sql('*'))
.where(comments[:user_id]
.eq(user.id)
.and(comments[:likes_count].gtdq(1)))
.as('liked_user_comments') # これ
JOIN句の組み立て
先ほど得た Arel::Nodes::TableAlias を .join に渡すとサブクエリになります。
最後に .join_sources を呼ぶことでJOINの右辺を取り出すことができます。（Arel::Nodes::InnerJoinオブジェクト）
join_conds = posts
.join(liked_user_comments, Arel::Nodes::InnerJoin)
.on(liked_user_comments[:post_id].eq(posts[:id]))
.join_sources
ActiveRecord の joins に渡す
Post.joins(join_conds)
参考