We might find ourselves in a situation where we have a many-to-many relationship between two models and we want to filter the records of these models.
Let's say we have Product
and Tag
models, with a join model called ProductsTag
, the names of the tables being products
, tags
and products_tags
respectively.
Table |-products-| |----products_tags----| |-tags-|
columns | id | | product_id , tag_id | | id |
If we want to filter out products having some specific tags, then we will be executing a query like this
SELECT *
FROM products
INNER JOIN products_tags
ON products.id = products_tags.product_id
INNER JOIN tags
ON tags.id = products_tags.tag_id
WHERE tags.id IN (comma separated tag ids here)
Explanation:
We join the products
table with the join table products_tags
and then join its result with the tags
table, then we filter the result of these two joins by using the where
clause.
There might be other ways of doing this, but here I'm going to demonstrate this using scopes.
# Product.rb
scope :filter_by_tag, ->(tag_ids_arr) { joins(:tags).where( tags: { id: tag_ids_arr } ) }
Now when you want to perform this filtering, do
tag_ids = [1,2,3,4,5]
result = Product.filter_by_tag tag_ids
We can do a bit more advanced filtering using scopes. Suppose our schema is
Table |--products--| |----products_tags----| |--tags--|
columns |id, category| | product_id , tag_id | |id, name|
and now what we want to get is that; We want All the products
of specific categories which have tags
with some specific name
.
Translating it to raw SQL
SELECT *
FROM products
INNER JOIN products_tags
ON products.id = products_tags.product_id
INNER JOIN tags
ON tags.id = products_tags.tag_id
WHERE products.category IN (comma separated category values here) AND tags.name = 'some_name'
Our scope for this will become
# Product.rb
scope :custom_filtering, ->(categories_arr, tag_name) { joins(:tags).where(products: { category: categories_arr }, tags: { name: tag_name }) }
To use this filter
categories = ['Electronics', 'Health']
tag_name = 'XYZ'
result = Product.custom_filtering categories, tag_name
Cover Image credits: Sajad Nori on Unsplash
Top comments (0)