Filter records in a many to many relationship in rails

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

Blog

Filter records in a many to many relationship in rails

Masroor Hussain

Join Our Newsletter. No Spam, Only the good stuff.

Related