Sequelize - Where clause through multiple joined table

Question

I am using sequelize orm in node to map objects to mysql db. Basically I have two tables product and category. One product belongs to one category but one category can have multiple products. In category table, I have a column "parent_id" which references to its own id. There can be three level hierarchy at most.

Main Objective is to find products which have any category that belongs to parent category **something, for instance 26. I can deduce query in sql like following.

select * from product as p join category as c on p.category_id = c.id join  
category as c1 on c1.id = c.parent_id join category as c2 
on c2.id = c1.parent_id where c.id = 26 or c1.id = 26 or c2.id = 26

In sequelize, I tried eager loading by including references like below

{
        // upto three levels of category
        model : category,
        as : 'c',
        attributes : [ 'id' ],
        include : {
            model : category,
            as : 'c1',
            attributes : [ 'id' ],
            include : [ {
                model : category,
                as : 'c2',
                attributes : [ 'id' ]
            } ]
        }
    }

That join perfectly works but how to fit where clause

c.id = 26 or c1.id = 26 or c2.id = 26

Thanks in advance. Any help will be really appreciated.

Charlie Charlie · Accepted Answer · 2017-10-08T19:47:26

Try adding 'where objects' at each level of your query. The first where object applies to table c. The second where object, nested in the include object, applies to the table c1, and so forth.

{
    // upto three levels of category
    model : category,
    as : 'c',
    attributes : [ 'id' ],
    where { id : 26 },
    include : {
        model : category,
        as : 'c1',
        attributes : [ 'id' ],
        where { id : 26 },
        include : [ {
            model : category,
            as : 'c2',
            attributes : [ 'id' ],
            where { id : 26 }
        } ]
    }
}

Sequelize - Where clause through multiple joined table

1 Answers