Querying for multiple tags generates the wrong query

[richtera]

Welcome to Ghost's GitHub repo! 👋🎉

We use GitHub only for bug reports 🐛

Anything else should be posted to https://forum.ghost.org 👫

🚨For support, help & questions use https://forum.ghost.org/c/help
💡For feature requests & ideas you can post and vote on https://forum.ghost.org/c/Ideas

If your issue is with Ghost CLI, please report it on the CLI repo ➡️ https://github.com/TryGhost/Ghost-CLI/issues/new.

Issue Summary

When doing a filter like "tags:one+tags:two" it will not returns posts which have both one and two as tags.

The generated sql is:

select `posts`.id 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` = 'one' and `tags`.`slug` = 'two')
group by
    `posts`.id, `tags`.slug
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
limit 15

When doing multiple queries for a field it should generate a new outer join with an alias and the condition or use a subquery on the join.

To Reproduce

Try to filter multiple tags using {{#get}} for example.

Technical details:

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

[ErisDS]

This is a duplicate of TryGhost/GQL#16, it only affects the old GQL (used when using the API directly, or with the get helper).

It does not affect NQL+JSON (used in dynamic routing).

We are slowly moving over to NQL (but still need to implement joins for NQL+SQL, correctly this time).

[richtera]

@ErisDS How do I use NQL. My filter is part of dynamic routing.

[richtera]

@ErisDS I don't think this is working. I just added this route.

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

And the SQL it produces 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

This does not remove items with those tags from the listing.

[kirrg001]

This bug was fixed in Ghost 2.8.0.