DEV Community

Cover image for Filter records in a many to many relationship in rails
Masroor Hussain
Masroor Hussain

Posted on • Updated on

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  |
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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 } ) }
Enter fullscreen mode Exit fullscreen mode

Now when you want to perform this filtering, do

tag_ids = [1,2,3,4,5]
result = Product.filter_by_tag tag_ids
Enter fullscreen mode Exit fullscreen mode

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|
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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 }) }
Enter fullscreen mode Exit fullscreen mode

To use this filter

categories = ['Electronics', 'Health']
tag_name = 'XYZ'
result = Product.custom_filtering categories, tag_name
Enter fullscreen mode Exit fullscreen mode

Cover Image credits: Sajad Nori on Unsplash

Top comments (0)