使用内部连接的Rails的ActiveRecord查询 [英] Rails ActiveRecord query using inner join

问题描述

我有两个表我试图做一个内部连接用。

I have two tables that I'm attempting to do an inner join with.

一个是用户表，其中主键是 ID 。

One is a users table where the primary key is id.

另一个表条，其中 USER_ID 是一个外键。 条也有一个名为列 foo_id ，其中 food_id 是一个外键FOOS 表中的。

Another table is bars where user_id is a foreign key. bars also has a column called foo_id where food_id is a foreign key to the foos table.

我试图把一个ActiveRecord的查询，在那里我可以选择开启或之前创建N天前，所有的用户和没有任何 FOOS ，其中 bars.foo_id 等于一个特定的ID。我试图做这样的事情：

I am trying to put together an ActiveRecord query where I can select all users that were created on or before N days ago and do not have any foos where bars.foo_id equal to a particular id. I tried doing something like this:

users = User.where("users.created_at <= ?", 50.days.ago).joins(:bars).where("bars.foo_id != 5")

此查询字段超过30,000的结果，这是不正确，导致用户表只拥有12000行。

This query fields over 30,000 results, which is incorrect, cause the Users table only has 12,000 rows.

我究竟做错了什么？

推荐答案

你得到你的加入数学错误的，它具有创建为每个用户+ foo的组合行的效果。这就是一个完整的连接工程。这样做的原因滑是因为你还没有真正加入吧表用户表。通常情况下，你必须在你的情况下加入了条件，如 bars.user_id = users.id 将是一个不错的主意。

You're getting your join math wrong and it's having the effect of creating rows for each user + foo combination. This is how a full join works. The reason for this slip is because you haven't actually joined the bars table to the users table. Normally you have to join with a condition, like in your case bars.user_id=users.id would be a good idea.

话虽这么说，你想要做的，而不是什么是确定哪些用户有资格，然后加载这些：

That being said, what you want to do instead is determine which users qualify, then load those:

users = User.where('id IN (SELECT DISTINCT user_id FROM bars WHERE bars.foo_id!=?)', 5)

本次选择，如果运行在它自己的，应该简单地返回用户的列表没有特定FOO。以此为，其中的条件应该只加载那些用户。

This sub-select, if run on its own, should return simply a list of users without that particular foo. Using this as a WHERE condition should load only those users.

这篇关于使用内部连接的Rails的ActiveRecord查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！