NQL date handling is in an unmanageable state.

We have to manage across the following variables:

• The format you use to insert into a DB (e.g. in fixtures) is different to what DBs return
• The formats you can use to do a comparison like > or < is not consistent between SQLite and MySQL
• The formats we return from the DB, and from our API endpoints can all be different
• JSON comparisons seem to work in some cases and not others, but really we should be using ISODate
• The mai use cases for dates are:

1. get helpers, where the real use case looks like this:
   {{#get "posts" filter="published_at:<='{{published_at}}'+id:-{{id}}" limit="3"}}
   this means it depends what comes out of the API as to what goes back in...
   ref: https://ghost.org/docs/themes/helpers/get/#filter
2. Prev & Next helpers which are builtins that do essentially the same thing, but they enforce that the right format
   ref: https://github.com/TryGhost/Ghost/blob/1cc38733ba6df8b8340420cff8fc7b643704c6ae/core/frontend/helpers/prev_post.js#L21
3. relative date queries like last_seen_at>now-1d
   ref: #26

We need to make sure that dates work consistently in these 3 cases.

Background

nql-lang introduced aliases support mid of 2018. nql-lang is able to replace keys in a filter string.

State of NQL 0.2.1

A month a go we added the missing NQL features (e.g. relations, mongo json utility etc.). This is what you have to know:

Aliases are now called: expansions. We no longer use the wording aliases. The reason is that there are two requirements for replacing stuff in a filter:

1. replace a key name e.g. filter=tags:x -> {tags.slug:x}
2. expand a statement if a key name matches e.g. filter:primary_tag:de -> {$and: [{tags.slug:de}, {sort_order:0}]} (optional)

That means, the expansions feature is more than just a simple string replacement. It can expand the mongo JSON query.

When the merge json utility logic was moved over from Ghost to NQL, i noticed that we no longer use nql-lang to replace the aliases/expansions. The expansion logic does it now.

Why?

All string filters are parsed by nql-lang as first step. And afterwards we pass the parsed filters into the utility. As soon as a key matches the configured expansions, the expansion logic replaces the key in the mongo json object and adds/extends the expansion filter to the target statement

e.g.

filter=tags:bla+primary_tag:blub

should be

filter=tags.slug:bla+(tags.slug:blub+posts_tags.sort_order:0)

If nql-lang would replace the name independently of the expanded filter, we first would get:

{$and: [tags.slug:bla, tags.slug:blub]}

Now you don't know which part you need to expand?

Discussion/Research

We have not spend further time on this. Is it possible to use nql-lang again?
This needs a bit playing & thinking.

This issue lists Renovate updates and detected dependencies. Read the Dependency Dashboard docs to learn more.

Repository problems

These problems occurred while renovating this repository. View logs.

• WARN: Using npm packages for Renovate presets is now deprecated. Please migrate to repository-based presets instead.

Awaiting Schedule

These updates are awaiting their schedule. Click on a checkbox to get an update now.

• Lock file maintenance

Open

These updates have all been created already. Click a checkbox below to force a retry/rebase of any.

• Update dependency nconf to v0.12.1
• Update dependency sqlite3 to v5.1.7
• Update Test & linting packages (c8, eslint-plugin-ghost, mocha, sinon)
• Update dependency knex to v2.5.1
• Update dependency lerna to v7.4.2
• Update Test & linting packages (major) (c8, eslint-plugin-ghost, sinon)
• Update codecov/codecov-action action to v4
• Update dependency date-fns to v3
• Update dependency knex to v3
• Update dependency lerna to v8
• Update dependency mingo to v6
• Click on this checkbox to rebase all open PRs at once

Detected dependencies

• Check this box to trigger a request for Renovate to run again on this repository

There is an error with this repository's Renovate configuration that needs to be fixed. As a precaution, Renovate will stop PRs until it is resolved.

Error type: Cannot find preset's package (@TryGhost:quietJS)

NQL works mostly on literals - strings that don't need to be quoted because they are obviously strings.

E.g. tag:photo, we don't need to do tag:'photo', it's totally redundant!

However, if a string contains a character that has another purpose in NQL, we have to use quotes (or escaping) to make it clear the whole thing is intended as a string.

E.g. in a date string published_at:<2017-09-01 12:45:12, both the space and the colons mean that we cannot use a literal, and we need to use quotes e.g. published_at:<'2017-09-01 12:45:12'.

NOTE: should colons be allowed inside of literals?! Or should they be allowed as escaped-chars!??!

This works absolutely wonderfully if the filter lives in a URL encoded URL string, or in JSON, but poses a problem in javascript code:

Example from the prev/next helper in Ghost:

  apiOptions = {
            include: 'author,tags',
            order: 'published_at ' + order,
            limit: 1,
            filter: "slug:-" + slug + "+published_at:" + op + "'" + publishedAt + "'", // jscs:ignore
        }

Normally we would use single quotes around the code, but we can't because this would clash with NQL.

In other languages, you'd switch to using double quotes, but NQL doesn't support this.

If we switch to using template literals:

  apiOptions = {
            filter: `slug:-${slug}+published_at:${op}'${publishedAt}'`, // jscs:ignore
}

The single quotes end up escaped like \' inside the string, which NQL also doesn't seem to understand - or it goes wrong somewhere on the way to NQL - need to test this better to understand what's happening - it definitely should work

TODO:

• At least support escaped single quotes (this is a bug)
• Consider supporting double quotes (this is an enhancement)

Problem

We are currently adding support for filtering on relations.

With every case, the converter becomes:

• harder to read
• a little bit hacky to fulfil the cases

Reasons

• everything lives in one file
• the relation cases are quite challenging, the complexity is high

Goal

Try to add all relation cases and then refactor the converter.js.
We should first learn from the use cases and see which refactoring direction makes sense.

I think what we need is a detection in the code base what this case is and then react on it.
Currently, the converter.js is too generic, which makes it hard to deal with all the cases.
IMO be explicit and more readable.

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 👍

Similarly to https://github.com/NexesJS/NQL/issues/9, config should be moved to test directory.

The configuration in this project is test specific and should only live within the test directory. Related comments here - TryGhost/NQL-old#5 (comment)

Originally reported at TryGhost/GQL#24 & TryGhost/Ghost#8433

To reproduce:

• On a local blog...
• Create a post with the title A, the slug will be a. Publish the post.
• Create a post with the title AB, the slug will be ab. Publish the post.
• Visit the frontend of the blog to grab the client secret.
• Open postman
• Try doing a get request for http://localhost:2368/ghost/api/v0.1/posts?client_id=ghost-frontend&client_secret=[client-secret]&filter=slug:ab - see that it works
• Try doing a get request for http://localhost:2368/ghost/api/v0.1/posts?client_id=ghost-frontend&client_secret=[client-secret]&filter=slug:a - see that it doesn't work

I believe this is due to the plus sign here: https://github.com/TryGhost/GQL/blob/master/src/gql.l#L22

The badcharsincnot regex matches a single character, and then the second group matches a second character. Therefore there must be at least 2 chars for a match.

Fixing this may have other implications, if so, we should close this issue and instead, add a rule that literals must be at least 2 characters to the documentation, provide tests to demo this limitation + if possible, improve the error message.

Currently a literal is defined as:

Any character string which follows these rules:
- Cannot start with - or : but may contain them
- Cannot contain any of these symbols: '"+,()><=[] unless they are escaped
- Cannot contain whitespace

• reconsider not allowing : at the start
• reconsider if non-quote symbols can be allowed inside, but not at the start/end without escaping
• reconsider whitespace

When reconsidering we must:

• look for valid use cases
• write lots of tests in both the lexer and parser

Description

NQL should support counting relations.

The SQL strategy is currently covered in TryGhost/NQL-old#1.

e.g. You can pass a filter into NQL filter=count.posts>:0 and mongo-knex will receive a mongo JSON object and needs to interpret this object with the help of TryGhost/mongo-knex#4. It has to attach the correct SQL queries.

Tasks

• add implementation for counting relations

At the moment, there are multiple places in converter.js that use the following structure:

 if (debugExtended.enabled) {
   debug(`(buildRelationQuery) ...`);
}

This could be extracted to a method like debug(extended) to avoid having if's in multiple
places.

Related discussion

Problem

In Ghost we want to be able to filter posts that have tags, or do not have tags.

Solution

IMO the syntax should be filter=tags:null or filter=tags:-null.

This doesn't cause an error, but it also doesn't do what you'd expect.

filter=tags:null - should return all posts with no tags
filter=tags:-null - should return all posts with at least one tag

Alternatively it could perhaps be done by supporting filtering on counts e.g.

filter=count.tags:0
filter=count.tags:>0

But this I think is a lot less intuitive.

There's been a long-time desire to support LIKE queries in Ghost.

From a user perspective, this would allow us to do "contains", "startsWith" and "endsWith" type filters.

Reference implementations

• validate that mingo can convert $regex and $not correctly
• validate that we could handle regex in our knex query builder
• very old implementation of LIKE by a contributor

NQL Syntax

• ~ contains
• ~^ starts with
• ~$ ends with

All 3 can be prefixed with - for not

Examples

• members.email:~@gmail - contains "@gmail"
• members.email:~^fred@ - starts with "fred@"
• members.email:[email protected] - ends with "@gmail.com"
• members.email:-~@gmail - does not contain "@gmail"

Mongo Syntax

Under the hood, NQL gets converted into Mongo's JSON query representation.

To check “contains” in mongo you need to do one of the below ref:

{ email: /fred@/i }

{ email: { $regex: /fred@/i } }

Not contains is one of:

{ email: { $not: /fred@/i } }

{ email: { $not: { $regex: /fred@/i } } }

The i is used to make the query case insensitive.

To make this easy to parse and process, we'll use the following two patterns:

{ email: { $regex: /fred@/i } }
{ email: { $not: /fred@/i } }

Starts with and ends with will become modifications on the regex:

{ email: { $regex: /^fred@/i } }
{ email: { $regex: /@gmail.com$/i } }

Have confirmed this works in mingo (the tool we use to query JSON directly).

A gotcha here is it will treat characters as regex characters, so we probably have to escape regex chars in the transform from NQL → mongo and then unescape them in the transform from mongo → SQL 🤔

SQL Syntax

SQL has two operators we could use here - LIKE or REGEXP:

Neither of these is case insensitive by default. LIKE only does wildcard matches, REGEXP allows for a wider range of queries & is therefore slower. We don’t need that power, complexity or risk right now, nor has there ever been a request for it, so we will stick to LIKE.

In order to use LIKE and case insensitive matching the SQL queries we need are:

select * from members.email where LOWER(email) LIKE LOWER('%fred@%'); // contains
select * from members.email where LOWER(email) LIKE LOWER('fred@%'); // starts with
select * from members.email where LOWER(email) LIKE LOWER('%@gmail.com'); //ends with

Originally reported at TryGhost/GQL#36

See: TryGhost/Ghost#9258 & TryGhost/Casper#382

I've seen this pop up a few times. If you use dynamic queries, there's no guarantee that you will get anything output, and then NQL will throw a parse error.

Short term the solution is to put guards around dynamic queries, but I think we can do better in NQL Lang, by handling the concept of an empty clause, and removing the whole clause if empty.

The bigger the test suite for integration tests becomes, the harder it's to achieve a certain state for a test case. Opening this issue as a ground for discussion on how things could be improved.

As an example, checking if a certain record belongs to a specific relational filter takes to look up:

1. posts collection in base.json,
2. compare it with records in many-to-many.json
3. go back to base.json and check tags collection there

When there are multiple records/properties matching this 3 step process becomes over cumbersome.

One of the thoughts around making things easier to check would be not having a global state like base.json but a set of helper functions that would set up the state in few steps. The downside of this approach is more work in the test case setup, but than it's a lot more clear which cases are being expected to be returned. Hard to find a good middle ground here 🤷‍♂️

Don't have any specific recepie and current approach is good enough :) Just opening this issue as a discussion to try improve future test suite maintenance and development.

NQL returns a wrong parsed result if filters are wrapped with outer brackets.

Let's assume you pass this filter: filter=featured:true,(tags:animal+tags:classic)

It results in:

{"$or":[{"featured":true},{"$and":[{"tags":"animal"},{"tags":"classic"}]}]}

Which is correct!

But if you pass a filter like this: filter=(tags:animal+tags:classic)

It results in:

{"yg":{"$and":[{"tags":"animal"},{"tags":"classic"}]}}

Which is wrong.

Reference: yg expression, see.

We have a need for relative date handling in NQL.

The exact solution I propose is do the translation from a relative date into an absolute date in the NQL language. This means adding a new syntax to NQL to represent a relative date and then processing that into an exact date inside the parser, and storing an absolute query in our intermediary format (Mongo query JSON). Our intermediary format would then represent a simple absolute date query to perform on the database, which we can already do.

This solution does not add relative date handling all the way to the database / JSON
lookup. The query that is performed will be absolute, but in the URL/API query we will be able to represent this as relative. The reason for this is that neither mongo query/mingo nor knex have support for relative dates.

Proposed Syntax:

last_seen_at:>=now-1d (since yesterday)

last_seen_at:<=now+1d (before tomorrow)

In detail:

>= or <= are NQL’s greater than or less than operators.

now a keyword that represents now

-/+ plus or minus, used to indicate dates since or dates before now

1d represents “one day” - an interval to add or subtract from now

We can support different intervals:

• now-30d (30 days ago)
• now-4w (4 weeks ago)
• now-12M (12 months ago)
• now-1y (1 year ago)
• now-1h (1 hour ago)
• now-1m (1 minute ago)
• now-1s (1 second ago)

Note: M is capitalised for Months as per datetime formatters in most languages, where m represents minutes.

last_seen_at:>=now-1d would become the following Mongo Query JSON:

{
  "last_seen_at": {"$gt": "2022-02-27T10:45:00Z"}
}

Originally reported at TryGhost/GQL#3

Currently, there's a single commented out test in the 'Initial commit' PR:

https://github.com/TryGhost/GQL/pull/2/files#diff-40ba893e5d10c536e8f08afc0dceb9e6R84

Currently the Lexer treats true_thing as a literal, but true-thing as a true, a not and a literal. This slight inconsistency is somewhat annoying, and could be considered to be a bug, however I'm not sure how/if it can be mitigated and whether it matters enough to spend time on.

Note: surrounding both in quotes results in them correctly being treated as a string value - the string form should always behave slightly better than the literal form, I think that's to be expected?

Do we document that including true, false or null in literals is bad form / may have unexpected results, do we change the lexer to reliably treat these values as bad when in a literal, or is this a kink that can be ironed out?