Activerrecord 中的子查询

使用 SQL，我可以很容易地进行这样的子查询

User.where(:id => Account.where(..).select(:user_id))

这就产生了:

SELECT * FROM users WHERE id IN (SELECT user_id FROM accounts WHERE ..)

如何使用 ails 的3 activerrecord/arel/meta _ where 来完成这项工作？

我确实需要/想要真正的子查询，没有 Ruby 变通方法(使用多个查询)。

62104 次浏览
小开

In ARel, the where() methods can take arrays as arguments that will generate a "WHERE id IN..." query. So what you have written is along the right lines.

For example, the following ARel code:

User.where(:id => Order.where(:user_id => 5)).to_sql

... which is equivalent to:

User.where(:id => [5, 1, 2, 3]).to_sql

... would output the following SQL on a PostgreSQL database:

SELECT "users".* FROM "users" WHERE "users"."id" IN (5, 1, 2, 3)"

Update: in response to comments

Okay, so I misunderstood the question. I believe that you want the sub-query to explicitly list the column names that are to be selected in order to not hit the database with two queries (which is what ActiveRecord does in the simplest case).

You can use project for the select in your sub-select:

accounts = Account.arel_table
User.where(:id => accounts.project(:user_id).where(accounts[:user_id].not_eq(6)))

... which would produce the following SQL:

SELECT "users".* FROM "users" WHERE "users"."id" IN (SELECT user_id FROM "accounts" WHERE "accounts"."user_id" != 6)

I sincerely hope that I have given you what you wanted this time!

小开

I was looking for the answer to this question myself, and I came up with an alternative approach. I just thought I'd share it - hope it helps someone! :)

# 1. Build you subquery with AREL.
subquery = Account.where(...).select(:id)
# 2. Use the AREL object in your query by converting it into a SQL string
query = User.where("users.account_id IN (#{subquery.to_sql})")

Bingo! Bango!

Works with Rails 3.1

小开

Rails now does this by default :)

Message.where(user_id: Profile.select("user_id").where(gender: 'm'))

will produce the following SQL

SELECT "messages".* FROM "messages" WHERE "messages"."user_id" IN (SELECT user_id FROM "profiles" WHERE "profiles"."gender" = 'm')

(the version number that "now" refers to is most likely 3.2)

小开

Another alternative:

Message.where(user: User.joins(:profile).where(profile: { gender: 'm' })
小开

This is an example of a nested subquery using rails ActiveRecord and using JOINs, where you can add clauses on each query as well as the result :

You can add the nested inner_query and an outer_query scopes in your Model file and use ...

  inner_query = Account.inner_query(params)
result = User.outer_query(params).joins("(#{inner_query.to_sql}) alias ON users.id=accounts.id")
.group("alias.grouping_var, alias.grouping_var2 ...")
.order("...")

An example of the scope:

   scope :inner_query , -> (ids) {
select("...")
.joins("left join users on users.id = accounts.id")
.where("users.account_id IN (?)", ids)
.group("...")
}

提交答案