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)