I'm having difficulty writing a query, whether in SQL or not, but I'd like to be able to write it in ruby using the gem Squeel. Basically, I have a class:
class Article < ActiveRecord::Base
belongs_to :genre
belongs_to :publisher
...
end
I want a scope that takes in an array of ordered pairs of the format (genre_id, publisher_id)
and outputs an ActiveRecord::Relation
that contains all of the records with genre, publisher pairs equal to the pairs passed in.
Basically, I want Article.where{ genre_id.eq_any [1, 5, 76] }
, except instead of a single attribute, I want it on a pair of attributes:
Article.where{ genre_id_publisher_id.eq_any [(1,2), (1,4), (2, 4)] }
The only way I can think of doing it is making a scope with a select call that adds a column which would be the concatenation of the two ids with some delimiter, then searching based on that:
Article.select(*,
"#{genre_id}-#{publisher_id}" as genre_publisher_pair).where(
genre_publisher_pair.eq_any ["1-2", "1-4", "2-4"])
However, that seems like too much overhead. I also already have a compound index on [genre_id, publisher_id] and I'm afraid this won't use that index, which is going to be a requirement for me.
Is there an easier way to write these compound scopes? Thanks!