Many-to-many conjunctive (AND operator) queries

[Mickael-van-der-Beek]

This issue was already reported by @ErisDS on the main TryGhost/Ghost repository, issue reference:

TryGhost/Ghost#6158.

I actually really needed this feature to make my company's use-case work so I went ahead and implemented it in this branch of my fork:

Mickael-van-der-Beek/GQL#many-to-many-conjunctive-queries

And here's the diff compared to the current master branch:

https://github.com/TryGhost/GQL/compare/master...Mickael-van-der-Beek:many-to-many-conjunctive-queries?expand=1

As you can see the implementation is absolutely disgusting and that's also why I'm not trying to submit it as a PR. I just thought that maybe if someone worked on it in the future, they could use it a reference starting point.

A few thoughts about this implementation in no particular order:

• it uses the slower SQL aggregation way of solving many-to-many queries (as discussed in the original issue as well as the StackOverflow answer)
• it hard codes the SQL grouping column in the having() call
• it assumes (as a shitty heuristic) that the WHERE ... IN () query is done on the column that is most used
• will remove any other mentions of the chosen column from the query
• has to use a module-global variable to store the having parameters as a workaround to scope limitations in the arguments passed to processFilter() (this one is not too important and can be fixed easily)

But it the end it passes the test suite and seems to work well. For the use-case we had (which was a hack in itself using Ghost's tagging system), neither Ghost nor Ghost's API are public facing so that meant that this solution was sufficient for now.

After thinking about the problem at hand during the day, I don't think that it's possible to automatically generate the SQL query if the parent application doesn't hint at which column specifically will be used for the WHERE ... IN () clause. Also, the HAVING clause has a hard dependency on the GROUPed column(s) which means that for Ghost's use-case where query composition is done it will be hard to implement it in a non-hardcoded way if GQL stays in it's current form.

[ErisDS]

@Mickael-van-der-Beek wow, thanks for taking the time to come and show what you have done. If you'd be up for writing some tests, I think your version is very, very worthy of a PR. Your self-analysis of your code reminds me of myself 😆

Nothing in GQL is particularly pretty, it's all a bit experimental, but in my experience when trying to tackle something this fiddly the nice solutions really don't fall out until you have implementations for all the common use cases and have a full picture to work with.

So yeah, please do submit a PR!

[bethanyr]

Hi - I've opened a PR with a possible solution for this issue - after reviewing the changes that Mickael-van-der-Beek made to solve this issue - it didn't appear to support 1 of my tag filter scenarios .

I have 2 tag filtering scenarios that my team uses in our custom Ghost template: filter="tag:sports+tag:football" and filter="tag:sports+tag:-football".

I took the approach of creating sub-queries for each tag filter using knex to build the subquery.

[Toub]

@ErisDS 2 years later, what is the current status of this one?
is it still hard to implement AND filters?

Edit from https://forum.ghost.org/t/api-how-to-filter-articles-having-tag-a-and-tag-b-both-of-them/2121/2?u=toub

It’s a very tricky one to fix, and one that I am actively working on. No ETA though - if I manage it will be part of a major rewrite and shipped in Ghost 2.0 when that lands.

[kirrg001]

GQL was replaced by NQL. GQL is deprecated now.

This bug was fixed in Ghost 2.8.0 with the help of NQL 0.2.1.