TryGhost / NQL

MIT License
4 stars 8 forks source link

Optimise sub queries #10

Open kirrg001 opened 5 years ago

kirrg001 commented 5 years ago

mongo-knex generates for some cases multiple sub queries in the where clause e.g. for

$and: [{'tags.slug': 'en'}, {'tags.slug': 'de'}]

But there are some cases where we currently generate multiple sub queries, where we don't have to.

This issue should simply collect use cases over time to optimise the performance of the attached queries. I will leave a comment with a use case i discovered in a bit.

Just leave a comment if you discover or want to discuss a case 👍

kirrg001 commented 5 years ago
const mongoJSON = {
    'tags.visibility': 'public',
    'tags.slug': {
        $ne: 'classic'
    }
};

(NOTE: ^ $and operator is default)

select * from `posts` 
where 
`posts`.`id` in (select `posts_tags`.`post_id` from `posts_tags` inner join `tags` on `tags`.`id` = `posts_tags`.`tag_id` where `tags`.`visibility` in ('public')) 
and 
`posts`.`id` not in (select `posts_tags`.`post_id` from `posts_tags` inner join `tags` on `tags`.`id` = `posts_tags`.`tag_id` where `tags`.`slug` in ('classic'))

IMO this case can use one sub query, because we have two different columns (visibility and slug).