Rails ActiveRecord与has_many关联相交查询 [英] Rails ActiveRecord intersect query with has_many association

问题描述

我有以下模型：

class Piece < ActiveRecord::Base
     has_many :instrument_pieces
     has_many :instruments, through: :instrument_pieces
end

class Instrument < ActiveRecord::Base
    has_many :pieces, through: :instrument_pieces
    has_many :instrument_pieces
end

class InstrumentPiece < ActiveRecord::Base
    belongs_to :instrument 
    belongs_to :piece
end

我有以下查询：

Piece
.joins(:instrument_pieces)   
.where(instrument_pieces: { instrument_id: search_params[:instruments] } )
.find_each(batch_size: 20) do |p|

其中 search_params [：instruments] 是数组。该查询的问题在于它将检索具有任何工具的所有片段，因此如果 search_params [：instruments] = [ 1， 3] ，查询将返回具有1或3或两者的仪器关联的片段。我希望查询仅返回其乐器关联同时包括乐器1和3的片段。我已经阅读了文档，但是我仍然不确定如何做到这一点...

Where search_params[:instruments] is an array. The problem with this query is that it will retrieve all pieces that have any of the instruments, so if search_params[:instruments] = ["1","3"], the query will return pieces with an instrument association of either 1 or 3 or of both. I'd like the query to only return pieces whose instrument associations include both instruments 1 and 3. I've read through the docs, but I'm still not sure how this can be done...

推荐答案

似乎我想要的是两个查询之间的交集，所以我最终要做的是：

It seems like what I wanted was an intersection between the two queries, so what i ended up doing was:

queries = []
query = Piece.joins(:instruments)   
search_params[:instruments].each do |instrument|
    queries << query.where(instruments: {id: instrument})
end
sql_str = ""
queries.each_with_index do |query, i|
    sql_str += "#{query.to_sql}"
    sql_str += " INTERSECT " if i != queries.length - 1
end

Piece.find_by_sql(sql_str).each do |p|

非常丑陋，但ActiveRecord还不支持INTERSECT。我想应该等一下ActiveRecord 5。

Very ugly, but ActiveRecord doesn't support INTERSECT yet. Time to wait for ActiveRecord 5, I suppose.

这篇关于Rails ActiveRecord与has_many关联相交查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！