V8-archive: Filter on many to many relation where match equals all

[maartenvanbenthem]

Feature Request

Adding the possibility to filter collections with a many to many relation

What problem does this feature solve?

I have a page collection and a tag collection. I would like to filter pages that have a relation with a tag with title values Tag 1 AND Tag 2 AND Tag 3.

My pages table:

My tags table:

The many to many relational table (page_tags):

How do you think this should be implemented?

No idea, but the following query is giving me the right result:

SELECT p.*
  FROM page p
 WHERE EXISTS (SELECT NULL
                 FROM page_tags tg
                 JOIN tags t ON t.id = tg.tags_id
                WHERE t.title IN ('Tag 1','Tag 2','Tag 3')
                  AND tg.page_id = p.id
             GROUP BY tg.page_id
               HAVING COUNT(DISTINCT t.title) = 3)

HAVING COUNT(DISTINCT t.title) = 3 makes sure I only get a result with ALL values:

Here is my DB
somedb (1).sql.gz

Would you be willing to work on this?

[benhaynes]

Hey @maartenvanbenthem — it seems like this is already an issue. Can you check out this other ticket and see if it's what you're looking for? If it's not I'll re-open this one...

https://github.com/directus/api/issues/576

[maartenvanbenthem]

Hi @benhaynes it is not what I am looking for. The all and has operators can only be applied while filtering on a one to many relationship. And in my case I am using a many to many relationship.

[rijkvanzanten]

To achieve better clarity/visibility, we are now tracking feature requests within the Feature Request project board.

This issue being closed does not mean it's not being considered.

[lennerd]

Do you have a link where this issue is worked on? Having problems finding the issue in your project board.

[benhaynes]

This ticket? It is in proposed here: https://github.com/directus/api/projects/40

[lennerd]

I'm sorry to revive this ticket but am currently having similar problems with many to many fields and the Directus API not filtering correctly.

I have a mentor collection with multiple categories (many to many) to be able to filter mentors by category. The relational path to the categories is mentor.categories.category_id. When filtering by category I only want to receive mentors containing all (and) categories. What are the correct endpoint and query parameters for applying a filter here?

I already tried the following URLs for filtering all mentors having a relation to category 1 and 10 with no success:

• /_/items/mentors?&filter[categories.categories_id][eq]=1,10
• /_/items/mentors?&filter[categories.categories_id.id][eq]=1,10
• /_/items/mentors?&filter[categories.categories_id.id][eq][]=1&filter[categories.categories_id.id][eq][]=10

They all return mentors having a relation to category with id 1 but ignoring the id 10. What am I'm doing wrong?

[lennerd]

@benhaynes meaning? 😄

[benhaynes]

Hahah, I re-opened this thinking it was a bug, but @rijkvanzanten is tracking it as an enhancement... so it stays closed and exists within our Feature Request Board instead.

[lennerd]

Okay, soo … tracking this issue as a feature request does it mean there is no built in way to filter by many to many relations in directus? Or am I missing something?

[benhaynes]

I believe that is correct. You can work around this by filtering directly on the junction table, but I don't think you can (as of now) filter from the parent item->junction->child

[Chrisstar56]

Sorry to revive this, but do I understand correctly that currently you cannot achieve this? I'm having a situation matching the original post almost exactly: A Post Collection and a Tag Collection, where every post can have multiple tags.
Is there some work around for this? @benhaynes I don't see how your solution works? Wouldn't that require two separate requests, one for the tag ids and another one for the posts after merging the data from the first request manually?
Or is there a way I can have the relationship data only stored in the Post collection (like a list), as I don't need the two-way relationship?

[benhaynes]

It might take two requests — the proper way to do this is not currently supported, which is why this is a feature request. If it gets enough attention, we might be able to build it into the upcoming Laravel refactor, but that's more up to @rijkvanzanten and @WoLfulus.

[somu22081980]

I have two tables as follows.

table1; meters_test

table2 : meter_consumptions

How can write a rest api filter for the following query based on the above tables?
These two tables are related with each other meter_id, meters_ test is having PK of meter_id and meter_consumptions having FK of meter_id, which referes the table meters_test.

SELECT a.meter_id,a.consumpton,b.zone_id,b.circle_id from meter_consumptions as a, meters_test as b where b.zone_id="Zone2" and a.meter_id=b.meter_id;