Sequelize Associations Causing Sub-Query

Question

I have a feature within my application that acts as an activity feed with blog posts (similar to Tumblr). In the route for this page, my primary query is on my blog post table and I am joining tables containing additional meta information and comments to this table for a fully featured card for each blogpost. These tables are from my category (categorizing the post), topic (topic of interest), file (files/images) attached, and comment (commenting on post) table.

With my current query I am able to join all of these tables without any issue, but I run into an issue with my where clause associated with the parent query on the blog post. When the file and comment tables are joined there is a sub-query that occurs, which I believe is related to the type of associations between blog posts and those tables and somehow affecting the results.

Provided are the Schema's and associations:

blog_post

• blog_id
• date
• content
• category_id
• topic_id

Associations

• belongsTo(user, { foreignKey: 'user_id' });
• belongsTo(category, { foreignKey: 'category_id' });
• belongsTo(topic, { foreignKey: 'topic_id' });
• hasMany(file, { foreignKey: 'blog_id' });
• hasMany(comment, { foreignKey: 'blog_id' });

user

• user_id
• first_name
• last_name
• organization_id

category

• category_id
• category

Associations

• hasMany(blog_post, { foreignKey: 'category_id'})

topic

• topic_id
• topic

Associations

• hasMany(blog_post, { foreignKey: 'topic_id'})

File and Comment tables do not have associations within their Schema files related to this issue.

Provided is the query (Please not camel casing are aliased column names above)

models.BlogPost.findAll({
            order: 'Date DESC',
            include: [{
                model: models.User,
                where: { organizationId: req.user.organizationId },
                attributes: ['userId', 'firstName', 'lastName'],
                required: false,
            },
            {
                model: models.Topic,
                attributes: ['topic'],
                required: false
            },
            {
                model: models.Category,
                attributes: ['category'],
                required: false
            },
            {
                model: models.File,
                attributes: ['file'],
                required: false
            },
            {
                model: models.Comment,
                include: [{
                    model: models.User,
                    attributes: ['userId','firstName', 'lastName']
                }],
                required: false
            }],
            limit: 10
        })

UPDATE with Sub Query:

SELECT `blog_post`.*, `user`.`user_id`, `user`.`first_name`, `user`.`last_name`, `topic`.`topic_id`, `topic`.`topic`, `category`.`category_id`, `category`.`category_name`, `file`.`file_id`, `file`.`file`, `comment`.`comment_id`, `comment`.`comment`, `comment`.`blog_id`, `comment`.`user_id`, `comment`.`created_at`, `comment`.`updated_at`, `comment`.`blog_id`, `comment`.`user_id`, `comment.user`.`user_id`
FROM (SELECT `blog_post.`blog_id`, `blog_post.`date`, `blog_post.`title`, `blog_post.`content`, `blog_post.`topic_id`, `blog_post.`category_id`, `blog_post.`user_id`, `blog_post.`created_at`, `blog_post.`updated_at`, `blog_post.`user_id`, `blog_post.`topic_id`, `blog_post.`category_id` FROM `blog_post LIMIT 10) AS `blog_post 
LEFT OUTER JOIN `user` AS `user` ON `blog_post.`user_id` = `user`.`user_id` AND `user`.`organization_id` = 1 
LEFT OUTER JOIN `topic` AS `topic` ON `blog_post.`topic_id` = `topic`.`topic_id` 
LEFT OUTER JOIN `category` AS `category` ON `blog_post.`category_id` = `category`.`category_id` 
LEFT OUTER JOIN `file` AS `file` ON `blog_post.`blog_id` = `file`.`blog_id` 
LEFT OUTER JOIN `comment` AS `comment` ON `blog_post.`blog_id` = `comment`.`blog_id` 
LEFT OUTER JOIN `user` AS `comment.user` ON `comment`.`user_id` = `comment.user`.`user_id` 
ORDER BY date DESC;

Answer 1

You probably need to add subQuery: false in the options object of findAll method. However, this option is not present in sequelize documentation, you can find it in the source code. If it is not set, it defaults to true and causes addition of the sub-query you mentioned when the LIMIT option is present in query.

Answer 2

i was having the same issue. i used the subQuery:false with the where statement object.

like:--

{
include:[{model:childrens,attributes:['id','firstName']}],
where:{'$childrens.id$':12}
subQuery:false
}

but you need to keep in mind that if you are using inner table at the where statement then you will have to use like:--

where:{'$childrens.id$':12}

because if you don't use '$' it will create automatic like:

abcd(main table).childrens.id=12

so to check for id value on childerns id then you will have to use '$'.

That's. Hope it will work for you.