Support 'and' in many-to-many relationship (post tags)

[ErisDS]

There is currently a limitation on using 'and' when trying to posts by specific tag slugs.

Using OR will work: {{#get "posts" filter="tag:quick-guide,tag:spa"}}
So will IN: {{#get "posts" filter="tag:[quick-guide,spa]"}}

However, and will not work {{#get "posts" filter="tag:quick-guide+tag:spa"}}

The reason it will not work, is that tags are only ever joined into posts once, meaning there is only one tag per row. As no row has multiple tags, no row can match.

There are two potential solutions:

1. join tags once per item we need to check
2. use an 'IN' and 'having count(*) > items query.

The first is more complex to do, the latter has potential performance issues in MySQL

Explanation in more detail here: http://stackoverflow.com/questions/1054299/sql-many-to-many-table-and-query

[vislamov]

Also this type of query doesn't work, should be related:

{{#get "posts" filter="tag:quick-guide+tag:-platform" debug="true"}}

The result includes all documents tagged with quick-guide along with those tagged with platform tag, which should be omitted.

QUERY:
select * 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" = 'quick-guide' and "tags"."slug" != 'platform') group by "posts"."id" order by "posts"."status" ASC, "posts"."published_at" DESC, "posts"."updated_at" DESC, "posts"."id" DESC limit 15

[ErisDS]

Yeah it's exactly the same query, just with a not, no AND query across multiple tags will work because tags only exist once in the row.

[johnny94]

After digging the code base I think there are two possible ways to fix the bug.

The first is to modify the implementation of GQL. The reason is that I found much of the query is constructed in

// core\server\models\plugins\filter.js

this.query(function (qb) {
   gql.knexify(qb, self._filters);
});

Before executing this line of the code, the raw query is something like select * from "posts", then
it will become:
select * from "posts" where ("posts"."status" = 'published' and "posts"."page" = false) and (other query...)
So If we want to implement the solution like the stackoverflow mentioned, modifying the GQL is necessary.

The second way is to extract 'tag related query' from filter object before executing gql.knexify.
Suppose that the filter is filter="tag:new-tag,hello-ghost" The filter object is something like:

 group undefined
  { op: '=', value: 'published', prop: 'posts.status' }
  { op: '=', value: false, prop: 'posts.page', func: 'and' }
 group and
  { op: '=', value: 'new-tag', prop: 'tags.slug' }
  { op: '=', value: 'hello-ghost', prop: 'tags.slug', func: 'or' }

before executing gql.knexify

Maybe we can extract the tag part before executing gql.knexify.
After then we run the special query rule for remaining tag related query. With this way, we don't need to modify the GQL. But I am not sure if this solution is possible (maybe it will be very 'hack').

[ErisDS]

This is definitely something that should be solved in GQL, not in Ghost. IMO GQL needs to become aware of relationship types, so that it is able to handle many-to-many relationships.

[vislamov]

Hey guys, any updates on this one? It's really limiting query language and needs to solved eventually. Workarounds are clunky and always require extra network bandwidth, extra data etc. etc.

[ErisDS]

I'm closing all OAuth and most API issues temporarily with the later label.

RE: OAuth, for the next 2-3 months we'll be implementing an official Ghost OAuth login system, providing global access to all Ghost blogs with a single login. We'll be opening issues around this system soon, and I don't want to cause confusion with OAuth for the API.

JSON API Overhaul & OAuth access are currently scheduled next on the roadmap