Rails Joins with Exclude certain IDs

Question

我有以下查询，它根据IDs

数组取出一组结果

User.joins(:memberships).
     where(plan_memberships: { user_id: attendee_ids, plan_id: plan.id })

现在我想做另一个不包括attendee_ids的调用，然后与上面的结果连接，以保留attendee_ids的顺序，然后是其余的。

是否有一种方法可以首先根据某些id保留结果的顺序，然后显示剩余的结果?

Answer 1

我认为在您的情况下，最好的方法是通过两个数据库查询分别检索结果。

common_scope = User.joins(:memberships).where(plan_memberships: { plan_id: plan.id })
excluded = common_scope.where.not(plan_memberships: { user_id: attendee_ids })
encluded = common_scope.where(plan_memberships: { user_id: attendee_ids })
all_sorted = excluded + encluded

Answer to additional question:

如果你想检索一定数量的记录，你可以这样做:

common_scope = User.joins(:memberships).where(plan_memberships: { plan_id: plan.id })
excluded_scope = common_scope.where.not(plan_memberships: { user_id: attendee_ids })
encluded_scope = common_scope.where(plan_memberships: { user_id: attendee_ids })
number = 3
excluded = excluded_scope.limit(number)
size = excluded.size
if size < number
  encluded = encluded_scope.limit(number - size)
  all_sorted = excluded + encluded
else
  all_sorted = excluded
end

Answer 2

您可以尝试注入一些SQL来覆盖排除情况。

User.joins(:memberships).
     where(plan_memberships: { user_id: attendee_ids, plan_id: plan.id }).
     where('plan_memberships.user_id not in (?)', banned_user_ids)

注意:根据您选择的数据库

Answer 3

如果你正在使用MySql，有一个选项来保留基于id的顺序，你已经得到了一个数组

field(user_id, [array of ids])

更多信息，请访问http://www.electrictoolbox.com/mysql-order-specific-field-values/