如何找到id与任何数组不匹配的位置？

I have a Workout model that has and belongs to many Equipment models. I have an array of some Equipment IDs. I want to find all Workouts that don't have any Equipment assigned that matches any of the array of Equipment IDs.

我有一个锻炼模型,它拥有并属于许多设备模型。我有一些设备ID的数组。我想找到所有没有分配任何设备ID的设备的锻炼。

So, if my array = [2,3,5] I want to find all workouts where the assigned equipment ids does not include 2, 3 or 5.

所以,如果我的数组= [2,3,5],我想找到所分配的设备ID不包括2,3或5的所有训练。

EDIT:

Workout.joins(:equipment).where("equipment.id not in(?)",[2,3,5]).uniq

Assuming five instances of Equipment, the code above returns workouts with equipment.ids 1 and 4 (good), but also returns partial matches for example Workouts with equipment.id = [1,2], [1,2,3].

假设有五个装备实例,上面的代码返回带有equipment.ids 1和4(好)的训练,但也返回部分匹配,例如Workouts with equipment.id = [1,2],[1,2,3]。

3 个解决方案

#1

It helps to think of what result set your query returns.

它有助于考虑查询返回的结果集。

Workout.joins(:equipment).where("equipment.id not in(?)",[2,3,5]).uniq

Joins all the related equipments to their workouts. If a workout was linked to 4 equipments then you'd get 4 rows for that workout. The where clause just filters that 4 down to a smaller number - it can't wipe them all out just because one matches.

加入所有相关设备进行锻炼。如果锻炼与4个设备相关联,那么你将获得4排锻炼。 where子句只是将第4个过滤到较小的数字 - 它不能仅仅因为一个匹配而将它们全部擦掉。

What you need to do instead is add conditions to the join itself. Something like

您需要做的是为连接本身添加条件。就像是

select workouts.*
left join equipments_workouts on workout_id = workouts.id and equipment_id in (2,3,5)
where equipment_id is null

Should return the correct workouts (it should also return a workout with 0 equipments but I don't know if that's a consideration.)

应该返回正确的锻炼(它还应该返回0设备的锻炼,但我不知道这是否是一个考虑因素。)

This works by trying to join 'bad' equipments. Because it's a left join, if no such row can be found then the result set will still include a row for that workout but with the columns for equipmnts_workouts all set to null. As a bonus you no longer have to eliminate duplicates.

这是通过尝试加入'坏'设备来实现的。因为它是一个左连接,如果没有找到这样的行,那么结果集仍将包含该训练的行,但equipmnts_workouts的列都设置为null。作为奖励,你不再需要消除重复。

Activerecord doesn't have a very nice way of writing queries like this. The joins method will accept an arbitrary SQL fragment though:

Activerecord没有非常好的方式来编写这样的查询。连接方法将接受任意SQL片段:

Workout.joins("left join equipment_workouts on workout_id = workouts.id and equipment_id in (2,3,5)").
        where("equipment_id is null")

You might find the sanitize_sql method useful for generating that sql fragment

您可能会发现sanitize_sql方法对于生成该sql片段很有用

#2

Workout.joins(:equipment).merge(Equipment.where("id not in(?)",[2,3,5])).uniq

Workout.joins(:equipment).where("equipments.id not in(?)",[2,3,5]).uniq

also u can try this, it should find all Workouts that don't have any Equipment

你也可以尝试这个,它应该找到所有没有任何设备的锻炼

Workout.includes(:equipment).where("equipments.id not in(?)",[2,3,5])

#3

This can be improved, but should work:

这可以改进,但应该工作:

class Workout < ActiveRecord::Base
  scope :without_equipments, lambda{|ids| joins(:equipment).where("equipments.id not in (?)", ids.repeated_permutation(ids.size).map(&:uniq).uniq)}
end
Workout.without_equipments 2,3,5

#1