我有两个表我试图做一个内部连接用。
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查询
发布评论