Support relations

[kirrg001]

Description

NQL currently does not support relations filtering. It generates the wrong SQL query e.g. if you pass filter=tags:[video].

select * from posts where posts.tags in ('video')

Problems to be mitigated when substituting GQL

GQL supports relations, but they are broken:

• The queries are not working correctly for multiple tags Ghost#9923
• Support 'and' in many-to-many relationship (post tags) Ghost#6158

General bug categories:

1. negation (tag:-en)
   • when filtering for example like tag:-en, the returned filter didn't include entities which had tag = null
   • when filtering by tag:-en and the entity had other tags e.g.: es, en it still returned this entity (didn't work as they were being achieved through having count() on individual tags)
2. “and” conjunction (tag:en, es)
   • current approach in GQL only works for or conjunction. the query that is being build uses in (...) which only accounts for or schenarios (it is missing having count to support and)
3. ralational count filtering (count.posts:>0)
   • theres no support for such operation in GQL

Why was GQL broken?

GQL used the following strategy:

• add joins of the relations
• use IN operator to compare the input values
• group by to return distinct results

Example GQL query:

select `posts`.*
from `posts`
	left outer join `posts_tags` on `posts_tags`.`post_id` = `posts`.`id`
	left outer join `tags` on `posts_tags`.`tag_id` = `tags`.`id`
where (`posts`.`status` = 'published'
	and `posts`.`page` = false)
	and (`tags`.`slug` not in ('article', 'unit', 'activity'))
group by `posts`.`id`

The query works for some cases, but not for all cases.

• IN operator is an instrument to satisfy OR conjunctions
• the query does not look at each invidivual relations, which results in bugs
• the query only looks at one single join result
• group by will consider all tags in return, but the respect of conjunctions is then missing
• futhermore it can't handle a result if there is no match e.g. tag:-en, would not return results which have no relation attached, because tags.slug is null (!)
• the query is just not the right one to fulfil the cases

How relational filtering could be achieved

Based on our research the most viable solution would be generating sub-queries. Example raw generated query for posts with tag:en filter would be:

where 'en'
in (
    select t.slug 
    from posts_tags as pt 
    inner join tags as t 
    on t.id = pt.tag_id 
    where pt.post_id=p.id
)

this could be generated with followign equivalent in knex:

qb
    .whereIn(knex.raw('\'en\''), function() {
        return this.select('t.slug')
            .from('posts_tags as pt')
            .innerJoin('tags as t', 't.id', '=', 'pt.tag_id')
            .where('pt.post_id', '=', knex.raw('`p`.`id`'))
    })

alternatively for performance reasons query could be also built with following structure to avoid performance degradation:

where p.id 
not in (
    select post_id 
    from posts_tags as pt 
    inner join tags as t 
    on t.id = pt.tag_id 
    where t.slug IN ('en', 'pt')
);

List of queries that prove all of the relational filtering types can be achieved with subqueries - https://gist.github.com/gargol/05aea3902981516c5ed6074b5664197f

Post implementation cleanup notes

These test cases will have to be adjusted/expanded upon when working on this issue:

• https://github.com/NexesJS/NQL/blob/cf7f988e123aaff440d07c81edcc227e15beea6d/test/api.test.js#L83-L97
• https://github.com/NexesJS/NQL/blob/cf7f988e123aaff440d07c81edcc227e15beea6d/test/api.test.js#L78-L79

[kirrg001]

This is done. The feature was added in mongo-knex.