1

I have the following models:

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

And I have the following query:

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

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...

23049581029
  • 355
  • 1
  • 2
  • 12

2 Answers2

2

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|

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

23049581029
  • 355
  • 1
  • 2
  • 12
  • Oh, I didn't assume that you mean the real `INTERSECT `, I've thought you just want to 'AND' the conditions. The `INTERSECT ` is definitely not supported by rails now. The code will probably look nicer if you just generate the SQL string without using ActiveRecord at all. – Dmitry Sokurenko Nov 06 '15 at 01:58
  • After 1 year from your post, I have met the same issue here, it took me 1 afternoon till I found your post. http://stackoverflow.com/questions/42689938/filter-by-many-to-many-relationships/42691195#42691195 – user3448806 Mar 09 '17 at 11:41
1

You can use where clause chaining to achieve this. Try:

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

or another version

query = Piece.joins(:instruments)   
search_params[:instruments].each do |instrument|
  query = query.where(instrument_id: instrument)
end
query.find_each(batch_size: 20) do |p|
Dmitry Sokurenko
  • 6,042
  • 4
  • 32
  • 53
  • When used with multiple values in search_params[:instruments], this query returns nothing... – 23049581029 Nov 06 '15 at 00:59
  • Do you actually have the pieces matching both instruments? You can inspect the resulting SQL code in the Rails. – Dmitry Sokurenko Nov 06 '15 at 01:02
  • Yup. I suspect the problem might be because an `instrument_pieces` object only has two fields, `:instrument_id` and `:piece_id` and this queries for an `instrument_pieces` object with an `:instrument_id` of both values. Is it possible to do an intersection of queries in this case? – 23049581029 Nov 06 '15 at 01:06
  • Can you post the SQL from log generated by my code, pls? – Dmitry Sokurenko Nov 06 '15 at 01:09
  • `SELECT "pieces".* FROM "pieces" INNER JOIN "instrument_pieces" ON "instrument_pieces"."piece_id" = "pieces"."id" WHERE "instrument_pieces"."instrument_id" = $1 AND "instrument_pieces"."instrument_id" = $2 [["instrument_id", 5], ["instrument_id", 6]] => # ` – 23049581029 Nov 06 '15 at 01:11
  • btw, try another query from the answer, while the SQL looks good for me. – Dmitry Sokurenko Nov 06 '15 at 01:14
  • Thanks for your help! – 23049581029 Nov 06 '15 at 01:52