如何在Active Record中编写子查询？实现关联表单查询获取数据

Hey there! Let's figure out how to replicate your raw SQL query using Active Record—no execute method required.

First, let's recap your setup: you have a User model with a has_many :posts association, and a Post model with belongs_to :user. Your goal is a single, efficient query to pull exactly the fields you need, which makes total sense for large datasets where includes (which typically runs two separate queries) might not be optimal.

Here's how to build the equivalent Active Record query: #

Your raw SQL uses an inner join with a subquery on posts, but we can simplify that to a direct join since the subquery is only selecting specific fields. We'll also handle the reserved SQL keyword from properly to avoid syntax errors.

Option 1: Using string selects (simple, with reserved word handling)

User.joins(:posts)
    .select(
      'users.id AS user_id',
      'users.name',
      'users.timestamp',
      'posts.id AS post_id',
      'posts.user_id',
      'posts.title',
      'posts.`from`', # Backticks escape the reserved SQL keyword
      'posts.`to`'
    )
    .where(users: { id: [1, 2, 3] })

Option 2: Using Arel (safer, database-agnostic)

If you prefer a more maintainable approach that avoids string interpolation risks, use Arel tables to construct the query:

user_table = User.arel_table
post_table = Post.arel_table

User.joins(:posts)
    .select(
      user_table[:id].as('user_id'),
      user_table[:name],
      user_table[:timestamp],
      post_table[:id].as('post_id'),
      post_table[:user_id],
      post_table[:title],
      post_table[:from],
      post_table[:to]
    )
    .where(user_table[:id].in([1, 2, 3]))

What this does: #

• joins(:posts) generates an inner join between users and posts, matching the logic of your raw SQL.
• select specifies exactly the fields you need, avoiding the overhead of pulling unnecessary data (critical for large datasets).
• The where clause filters users to the IDs you specified, just like your original query.

Handling the results: #

This query returns an ActiveRecord::Relation where each row combines a user and one of their posts (inner joins produce a row per user-post pair). If you want to group posts under their respective users, you can process the results in memory:

query_results = User.joins(:posts)... # The query above

# Group records by user ID
user_post_groups = query_results.group_by { |record| record.user_id }

# Transform into a hash of users mapped to their posts
user_with_posts = user_post_groups.transform_values do |records|
  records.map do |rec|
    Post.new(
      id: rec.post_id,
      user_id: rec.user_id,
      title: rec.title,
      from: rec.from,
      to: rec.to
    )
  end
end

Why this beats includes for large datasets: #

includes works by first fetching all matching users, then fetching all their posts in a second query, then associating them in memory. For large datasets, this can lead to:

1. Two large result sets being transferred from the database.
2. Extra memory usage to handle the in-memory association.

Your single join query avoids both issues by fetching only the data you need in one round trip to the database.

内容的提问来源于stack exchange，提问作者Ahmad hamza