ActiveRecord的where.not不工作/怪异的行为 [英] ActiveRecord where.not is not working/ weird behavior
本文介绍了ActiveRecord的where.not不工作/怪异的行为的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
用户的has_many
计划。我试图找到那些没有与取消状态的一个计划的所有用户的ID。很想知道发生了什么解释下面的行为。
有关背景下,应该返回是这样的:
User.select {| U | u.plans.select {| P | p.status =取消} .Count之间的&GT!; 0} .MAP(安培;:ID)
#=> [27,28,29,30,31,32,33,34,35,36,37,41,42,44,45,46,47,48,49,50,51,52,53,54,55 ,56,57,58,60,61,62,63]
下面就是我得到:
#语句1
User.joins(:规划).where.not(plans.status=>中取消)地图(安培;:ID)。
#用户负载(0.3ms)选择用户。*从用户INNER JOIN计划ON计划,user_ID的=用户,IDWHERE(计划,状态!='取消')
#=> [44]
#语句2
User.joins(:规划)。凡(plans.status =!?,取消),地图(安培;:ID)。
#用户负载(0.3ms)选择用户。*从用户INNER JOIN计划ON计划,user_ID的=用户,IDWHERE(plans.status!='取消')
#=> [44]
#语句3
User.joins(:规划)。凡(plans.status ==?,无).MAP(安培;:ID)
#用户负载(0.3ms)选择用户。*从用户INNER JOIN计划ON计划,user_ID的=用户,IDWHERE(plans.status == NULL)
#=> []
#语句4
User.joins(:规划)。凡(plans.status=>无).MAP(安培;:ID)
#用户负载(为0.7ms),选择用户。*从用户INNER JOIN计划ON计划,user_ID的=用户,IDWHERE计划,状态IS NULL
#=> [27,28,29,30,31,32,33,34,35,36,37,41,44,42,45,46,47,48,49,50,51,52,53,54,55 ,56,57,58,61,60,62,63]
问题:
- 为什么语句3和4没有返回相同的结果?
- 为什么语句1和2(幸运的是,这些都是一样的,都返回相同的结果)没有返回相同的结果,说明4?对于情况下,我宁愿不上
搜索无
,但注销
。我可以证实,所有计划有两种状态零
或注销
更新每个请求
与零状态#计划
Plan.where(状态:无)。首先
#=> <计划ID:1,拉链code:94282,selected_plan:1,meal_type:厨师的选择(混合),most_favorite:,least_favorite:过敏,START_DATE:2015年-05-27 00:00:00,delivery_address:D,delivery_instructions:,PHONE1:10,PHONE2:222,电话3:2222,agree_tos:真,USER_ID:20,created_at: 2015年5月24日5时十八分40秒,的updated_at:2015年6月21日4时54分31秒,stripe_subscription_id:无,stripe_invoice_number:无,cancel_reason:无,cancel_reason_other:无,NPS:无,nps_open:零,cancel_open:无,状态:无,referred_by_ code:零>
以取消状态#计划
Plan.where(状态:取消)第一。
#=> <计划编号:20,邮编code:12345,selected_plan:5,meal_type:肉类(含蔬菜),most_favorite:,least_favorite:过敏,START_DATE:2015年-06-08 00:00:00,delivery_address:ASDF,delivery_instructions:,PHONE1:333,PHONE2:333,电话3:3333,agree_tos:真,USER_ID:38,created_at: 2015年6月1日21点39分54秒,的updated_at:2015年6月23日六时23分10秒,stripe_subscription_id:sub_6OKkJoNx2u8ZXZ,stripe_invoice_number:0,cancel_reason:无,cancel_reason_other:,NPS:6 ,nps_open:,cancel_open:状态:取消,referred_by_ code:零>
解决方案
答问题1:
您错过了,在有条件的SQL,按照条件的参数被替换到位?
不能相比的概念。因此,更换双 ==
与 =
。
User.joins(:规划)。凡(plans.status =?,无).MAP(安培;:ID)
User has_many
Plans. I'm trying to find the IDs of all Users that do NOT have a Plan with status of "canceled". Would love to know what's explaining the behavior below.
For context, what should be returned is this:
User.select { |u| u.plans.select { |p| p.status != "canceled" }.count > 0 }.map(&:id)
# => [27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 41, 42, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 60, 61, 62, 63]
Here's what I'm getting:
# statement 1
User.joins(:plans).where.not("plans.status" => "canceled").map(&:id)
# User Load (0.3ms) SELECT "users".* FROM "users" INNER JOIN "plans" ON "plans"."user_id" = "users"."id" WHERE ("plans"."status" != 'canceled')
# => [44]
# statement 2
User.joins(:plans).where("plans.status != ?", "canceled").map(&:id)
# User Load (0.3ms) SELECT "users".* FROM "users" INNER JOIN "plans" ON "plans"."user_id" = "users"."id" WHERE (plans.status != 'canceled')
# => [44]
# statement 3
User.joins(:plans).where("plans.status == ?", nil).map(&:id)
# User Load (0.3ms) SELECT "users".* FROM "users" INNER JOIN "plans" ON "plans"."user_id" = "users"."id" WHERE (plans.status == NULL)
# => []
# statement 4
User.joins(:plans).where("plans.status" => nil).map(&:id)
# User Load (0.7ms) SELECT "users".* FROM "users" INNER JOIN "plans" ON "plans"."user_id" = "users"."id" WHERE "plans"."status" IS NULL
# => [27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 41, 44, 42, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 61, 60, 62, 63]
Questions:
- Why are statement 3 and 4 not returning the same result?
- Why are statement 1 and 2 (fortunately these are the same and are returning the same result) not returning the same result as statement 4? For context, I'd rather not search on
nil
, but on"canceled"
. And I can confirm that all plans have either a status ofnil
or"canceled"
UPDATE PER REQUEST
# plan with nil status
Plan.where(status: nil).first
# => <Plan id: 1, zipcode: "94282", selected_plan: 1, meal_type: "Chef's choice (mixed)", most_favorite: "", least_favorite: "", allergies: "", start_date: "2015-05-27 00:00:00", delivery_address: "d", delivery_instructions: "", phone1: "10", phone2: "222", phone3: "2222", agree_tos: true, user_id: 20, created_at: "2015-05-24 05:18:40", updated_at: "2015-06-21 04:54:31", stripe_subscription_id: nil, stripe_invoice_number: nil, cancel_reason: nil, cancel_reason_other: nil, nps: nil, nps_open: nil, cancel_open: nil, status: nil, referred_by_code: nil>
# plan with canceled status
Plan.where(status: "canceled").first
# => <Plan id: 20, zipcode: "12345", selected_plan: 5, meal_type: "Meat (with veggies)", most_favorite: "", least_favorite: "", allergies: "", start_date: "2015-06-08 00:00:00", delivery_address: "asdf", delivery_instructions: "", phone1: "333", phone2: "333", phone3: "3333", agree_tos: true, user_id: 38, created_at: "2015-06-01 21:39:54", updated_at: "2015-06-23 06:23:10", stripe_subscription_id: "sub_6OKkJoNx2u8ZXZ", stripe_invoice_number: 0, cancel_reason: nil, cancel_reason_other: "", nps: 6, nps_open: "", cancel_open: "", status: "canceled", referred_by_code: nil>
解决方案
Answer to Question 1:
You missed the concept that in conditional sql, the arguments that follow the condition are replaced in place of ?
not compared to. So, replace the double ==
with =
.
User.joins(:plans).where("plans.status = ?", nil).map(&:id)
这篇关于ActiveRecord的where.not不工作/怪异的行为的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文