如何更新Rails/SQL作用域以返回确切的匹配，而不是“包含”匹配？

Question

在我的数据模型Chord has_many :notes, through: :chordnotes中

Chord.rb中的以下作用域返回将Notes作为每个this post的Note in (例如[9, 0, 5])数组传递到该作用域的任何Note

scope :with_notes, ->(notes) { joins(:chordnotes).where(chordnotes: {note_id: notes}).group("#{table_name}.#{primary_key}").having('COUNT(DISTINCT chordnotes.note_id) = ?', notes.size) }

我如何修改这个范围，只返回在notes中准确传递了音符的和弦，而不是任何有notes传入的和弦？

例如：

如果D Chord有it为9、0、5、7的音符，而音符9、0、5被传递到作用域，它将返回D Chord。

我想要一个不返回D和弦的新范围，因为传入的notes不包括7，因此与D和弦的音符不完全匹配。

谢谢!

Answer 1

在更好地理解需求之后编辑：

我认为你所拥有的范围是好的，能让你走得足够远。从这一点，你知道，你会得到的结果，所有的和弦，你需要和一些额外的。所以我要做的是，不要把太多的逻辑转移到DB上，而是从这一点开始，使用类方法在顶部添加一些rails逻辑。是的，它有点重，因为它会增加两个额外的选择调用。但是..。维护起来更容易，它使用的列应该在索引下，而且将来在这个方法的上面添加缓存也很容易，以防您开始获得真正的流量。

新方法应该如下所示：

def self.with_exact_notes(notes)
  return if notes.empty?
  potential_ids = with_notes(notes).map(&:id)
  chords = Chord.includes(:chordnotes, :notes).where(id: potential_ids)
  chords.map {|ch| ch if ch.notes.map(&:id).sort == notes.sort }.compact
end

该方法将首先使用您的作用域获取所有潜在的和弦，然后筛选出那些没有传递给该方法的确切注释ids的和弦。

那么您的查询将是：

Chord.with_exact_notes(your_notes) 

Answer 2

如果使用postgres，可以使用ARRAY_AGG函数在db中这样做：

  def self.with_notes(note_ids)
    return none if note_ids.blank?
    typecast_ids = note_ids.sort.map { |id| "#{id}::bigint" }.join(",")
    joins(:chordnotes).group(:id)
      .having("ARRAY_AGG(note_id ORDER BY note_id) = ARRAY[#{typecast_ids}]")
      .distinct
  end

令人讨厌的是，如果您的id列是bigint，则需要显式地键入您要检查的ids (map { |id| "#{id}::bigint" } )。

检查是否相等(note_ids.sort，ARRAY_AGG(note_id ORDER BY note_id))时，一定要对这两个数组进行排序。

下面是这个函数作为SQL的外观：

pry(main)> Chord.with_notes([3,4]).first
  Chord Load (0.8ms)  SELECT  DISTINCT "chords".* FROM "chords" INNER JOIN "chordnotes" ON "chordnotes"."chord_id" = "chords"."id" GROUP BY "chords"."id" HAVING (ARRAY_AGG(note_id ORDER BY note_id) = ARRAY[3::bigint,4::bigint]) ORDER BY "chords"."id" ASC LIMIT $1  [["LIMIT", 1]]

如果你在使用MySQL，你可以使用GROUP_CONCAT

  def self.with_notes(note_ids)
    joins(:chordnotes).group(:id)
      .having("GROUP_CONCAT(note_id ORDER BY note_id) = ?", note_ids.sort.join(","))
  end