找到所有记录与其他两个条件,一个条件是真,假的 - Find all records with two conditions where one condition are true and other false

- 此内容更新于:2016-02-02
主题:

我试图找到所有相关的记录,两个条件得到满足。我在这,但它不工作,例子:我想让所有的学生记录,存在于集团和没有出勤有或没有。

原文:

I'm trying to find all associated records where two conditions are met. I'm trying this, but it doesn't work, examples:

  @students = @group.students
    .includes(:attendances)
    .where.not(attendances: {student_id: @ids, event_time_id: @event_id})

  @students = @group.students
    .includes(:attendances)
    .where.not(attendances: {event_time_id: @event_id})
    .where.not(attendances: {student_id: @ids})

  @students = @group.students
    .includes(:attendances)
    .where("attendances.student_id IN (?) AND NOT attendances.event_time_id = ?", @ids, @event_id)

I want to get all the student records that exist in group and not have attendance with @event_id or have not attendance at all.

解决方案:
我认为这可能会做你想做的事情(未测试),不是最优雅的看代码…
原文:

I think this might do what you want (untested), not the most elegant looking code...

exists_fragment = "SELECT 1 FROM attendances WHERE student_id = students.id"

@group.students
  .joins(:attendance)
  .where("attendances.event_id <> ? OR NOT EXISTS(?)", @event_id, exists_fragment)
楼主:我收到一个mysql错误。

(原文:I have received a mysql error.)

解决方案:
工作的解决方案:不知道如果这是最好的解决方案,但解决问题提到更少的代码版本更新:
原文:

Working solution:

  @students = @group.students.includes(:attendances)
    .where("attendances.event_time_id != ?", @event_id)
    .references(:attendances)

  @students_on = @group.students.includes(:attendances)
    .where(attendances: { event_time_id: @event_id })

  @students = @students - @students_on

Not sure if this is the best solution but it solves the mentioned problems

UPDATE Less code version:

  @students = @group.students
  @students_exist =  @group.students.includes(:attendances)
    .where(attendances: { event_time_id: @event_id })
  @students = @students - @students_exist