Replace GQL with NQL (filter plugin)

[kirrg001]

Goal

As soon as NQL is feature complete, we would like to replace GQL with NQL in the model layer. Ghost uses a filter plugin to hook into the database query.

Why?

GQL has a couple of bugs - especially with filtering on relations.

e.g. #9950

Strategy

The filter plugin is a little messy currently.

Ghost creates joins after GQL has finished. Ghost should not be responsible to create the joins. We should pass a proper configuration object to NQL and based on this information, NQL attaches the correct queries.

Ghost merges custom filters (from user), default and enforced filters. We need to keep this logic. NQL has to offer an interface.

The filter plugin needs to be touched if we want to replace GQL.

Tasks

• basic dirty version to show how GQL can be replaced (🐛 Fixed known filter limitations #10159)
• replace options.where logic with extra filters (see) (Replaced options.where GQL statements with filter notation #10160)
• add support for merging filters (Relplace enforced/default/custom/extra filter merging logic #10167), enable one test case in 🐛 Fixed known filter limitations #10159
• Replace GQL with NQL (filter plugin) #10105 (comment) refactor this piece
• filter by posts_tags.sort_order=0 with NQL (Filter by multiple relation columns mongo-knex#14)
• handle all aliases
• replace GQL in the raw knex query
• NQL must be feature complete to support all cases
• remove the GQL dependency completely
• ensure the existing bug reports are now resolved
  • Inverse tag filtering in dynamic routing works incorrectly #9950
  • [route] collection filters do not apply correctly on /: #10230
  • Support 'and' in many-to-many relationship (post tags) #6158
• close GQL issues (https://github.com/TryGhost/GQL/issues)
• close all GQL reports/ filter reports
• leave a comment on closed issues too
• track yg grouping cases on nql-lang (https://github.com/NexesJS/NQL-Lang/issues/7)
• track aliases not being used in nql-lang (https://github.com/NexesJS/NQL/issues/14)

Ghost: master

[kirrg001]

replace options.where logic with extra filters (see)

Started with this task. It's very important to get rid of options.where, because Ghost generates GQL statements. The functions should generate filters. String filters are independent from the implementation details. The goal is to keep the functionality, but to generate filter strings.

[kirrg001]

            this._filters.statements = gql.json.replaceStatements(this._filters.statements, {prop: /primary_tag/}, function (statement) {
                statement.prop = 'tags.slug';
                return {
                    group: [
                        statement,
                        {prop: 'posts_tags.sort_order', op: '=', value: 0},
                        {prop: 'tags.visibility', op: '=', value: 'public'}
                    ]
                };
            });

Just analysed this custom filter logic. - pre processed filter logic.

1. tag -> tags.slug is an alias. Will be covered by supporting/using alias config.
2. {prop: 'tags.visibility', op: '=', value: 'public'}. This is GQL statement format, which anyway doesn't work with NQL. This belongs to the default filter for tag model dependent on context. e.g. filter=visibility:public.
3. {prop: 'posts_tags.sort_order', op: '=', value: 0}. This is GQL statement format too. This case needs further testing. e.g. filter=tags.slug:en+visibility:public+posts_tags.sort_order:0?

[kirrg001]

 if (gql.json.findStatement(this._filters.statements, {prop: /^tags/, op: 'IN'})) {
   // TODO make this count the number of MATCHING tags, not just the number of tags
    this.query('orderByRaw', 'count(tags.id) DESC');
  }

Looking at this piece.

Meaning

Is only used when using IN operator e.g. tags:[x,y].
Any post which matches both tags should be returned first.

NQL?

This is nothing NQL should handle, because NQL only applies queries based on the filter string.
e.g. you can filter data. It does not have the purpose to order the result.

Ghost ?

There is already a TODO.

// TODO move the order handling to the query building that is currently inside pagination

The pagination plugin has a section to respect order definitions of the target model e.g. options.order or options.orderRaw. I think the order function of the post model needs to be conditional. e.g. if the filter wants to filter by tags using IN we should apply this specific order.

order by count(tags.id) DESC, CASE WHEN posts.status = 'scheduled' THEN 1 WHEN posts.status = 'draft' THEN 2 ELSE 3 END ASC,CASE WHEN posts.status != 'draft' THEN posts.published_at END DESC,posts.updated_at DESC,posts.id

[kirrg001]

FYI: 5 tasks are left, which are post cleanup tasks

[kirrg001]

This was shipped in Ghost 2.8.0.. The post cleanup tasks are done.