Sequelize : Get Subquery/Raw Query as model for include

Question

I have gone through the Sequelize doc, but can't find anything helpful

What I want to do is to add raw query or custom model in include, is it possible ?

model.Post.findAll({
            include: [{
                    model: model.User,
                    attributes: ['fullname', 'profilepic'],
                },
                {
                    model: model.PostComments,
                },
                {
                    model: "Raw Query"
                }
            ]
        }

What I want to achieve is like :

Select post_id, count(*) as total_likes from post_likes group by post_id

I can't achieve this by using simply include, so what I want to do is create a table/model from above query and then use it inside include.

If I use group by withing include it gives group by from top level, and I want to apply group by just for the post_like table.

Please let me know, if it found confusing or not clear.

Answer 1

I was looking to do the same, use a raw query dynamically formed inside the include but there's no possible way to use include without a model https://sequelize.org/master/class/lib/model.js~Model.html#static-method-findAll .

For the purposes of my MySQL I turned my inner join (include) into a where in. I was doing the inner join to avoid the exception This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery . I get this exception because I have a subquery with LIMIT applied.

If it helps anyone:

let rows = await j_model.findAll({
        attributes: [...j_columns, `${association}.meta_key_id`],
        include: [
            {
                model: um_model,
                as: association,
                attributes: ['um_id'],
                on: {'j_id' : {$col: 'j.j_id'} }
        ],
        where: {'j_id': {$in: sequelize.literal(`(SELECT * FROM (${massive_inner_raw_query}) as tmp)`)}},
        logging: console.log
    });

The actual magic is in this line:

where: {'j_id': {$in: sequelize.literal(`(SELECT * FROM (${massive_inner_raw_query}) as tmp)`)}}

The SELECT * FROM removes that exception and lets me do a where in instead of the wanted INNER JOIN. Maybe you can apply a similar deal to your problem.