The queries are not working correctly for multiple tags

[richtera]

Welcome to Ghost's GitHub repo! 👋🎉

Issue Summary

SQL statements not correctly generated for tags. When a post has multiple tags or you're querying for multiple tags the resulting sql will not be correct. My Issue #9916 got closed but this is not working correctly as far as I can see.

To Reproduce

I have this routing:

routes:
  /units/:
    controller: channel
    filter: tags:unit
    template: units
    order: slug

collections:
  /unit/:
    permalink: /unit/{slug}/
    filter: tags:unit
    type: custom
    frontPageTemplate: units
    template:
      - unit

  /:
    permalink: /{slug}/
    filter: tags:-[article,unit,activity]
    template:
      - index

taxonomies:
  tag: /tag/{slug}/
  author: /author/{slug}/

The SQL being generated is:

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`
order by 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 DESC

Some of the sample posts have the following tags:

Post 1: unit-special, unit
Post 2: activity-unit-special, activity
Post 3: article-unit-special, article
Post 4:

I tried this with single special tags but that didn't work either. Basically the join should be multiple or an inner sub query rather than doing a group by to stitch it back together. Using the group by will consider each tag in turn and cannot do an AND or NOT OR across the different rows in the group by.

Technical details:

When going to the home page all 4 articles are considered and then filtered out. In the actual system I have about 2300 posts and the filter will filter out the first 50 and display no posts on my home page.

• Ghost Version: 2.1.4
• Node Version: 8.12
• Browser/OS: chrome mac
• Database: jawsdb mysql

[kirrg001]

Hey again!

https://docs.ghost.org/docs/dynamic-routing#section-filter

Can you pls read this section and adapt your routes.yaml? Currently, you have to use 100% inverse filters when using multiple collections.

[richtera]

That's precisely what I am trying to do. The inverse filter tags:-[tag1,tag2,tag3] does not generate the correct SQL for posts that have multiple tags themselves since it uses a join and a group by. It would need to use a sub-select across tags for it to work.
NOTE: Every tag other than "tag1", "tag2" and "tag3" will pass through as a TRUE.

[kirrg001]

Yeah can confirm. The actual SQL query is not correct when the post has multiple tags.

[kirrg001]

I'll close this issue in favor of #9950. It's the same report. I've left a comment and linked your report.

Ghost uses GQL since years and GQL generates the SQL queries for Ghost. These queries are broken for relations, tracked e.g. here.

Dynamic Routing uses NQL only to match JSON objects, but not to generate the SQL queries. That's why you are running into this bug, which is tracked since 2016.

We have to finish up the NQL implementation and replace GQL with NQL in Ghost (in the model layer).

NQL will take care of the challenge to generate the correct SQL queries for relations.

Thanks for your report 👋

[kirrg001]

This bug was fixed in Ghost 2.8.0.