If I have a manyway relationship, can I query the collection type to group by the IDs in that relationship? Let’s say I had a collection type called “Articles” and articles had manyway to “Tags”, how best to query for how often each tag was used and get the most used tags (top 5 for example)? Using graphql
This is one where you may need a custom resolver for GraphQL (or change the relationship over to a manyToOne), as we don’t currently have aggregations to support this currently.
Depending on the amount of tags you have you could run a really inefficient query to get a list of tags then run a simple count query (will need to add the count to schema as we don’t have it by default, see: Count with GraphQL - Strapi Developer Documentation) then you could construct the count using a tag filter.
I don’t think I can do manyToOne for tags, because a tag can have many articles and an article can have many tags. I’m trying to avoid a Many-to-Many if I can help it for things like tags and other metadata. It just seems like it’s frowned upon for performance and organizational reasons.
I guess I am struggling a bit with database design for analytics and statistical information (trying to capture and report things like most active user, most used tags, etc).
Is there a way to get more info on the benefits the new database system will bring? I’d hate to put in a bunch of workarounds if new options are on the horizon.
Normally yes your right (especially with many2many) and for tags yes it’s generally a good idea to use the manyWay but the downside is the analytics as you’ve figured out.
In your case, honestly a custom controller and directly accessing the database through custom queries (either mongoose or bookshelf depending on your database choice) is the better option.
You could build the controller and build a GraphQL resolver for it, but that’s really a lot of work for just an analytics response. REST in this case is much easier.
Is there a way to get at the manyWay table that’s created?
So in my example, I end up with an articles table and an articles__tags table with my manyWay tags listed. If there was an easy way to query articles__tags, I could very easily group by tagID and get a count for the top 5. It also lets me see an overall total of the number of tags used total by a simple table row count.
Perhaps I can add a custom route/controller to articles that gets that information form the articles__tags table?
Best way would be via a RAW SQL query (if you are using a Bookshelf DB) via strapi.connections.default.raw()