Incorrect query for "all" and "has" filter

[wellingguzman]

The all and has filter is generating a bad query.

[beac0n]

using something like filter[locatios.id][eq]=1, where locations is a many-to-many relation, also leads to a bady query.

@wellingguzman : I'd like to help, as I have run into this issue myself multiple times.
I could provide a pull request, if you would point me into the right direction, where to look in the codebase :-)

[wellingguzman]

@beac0n what's the bad query you are getting?

To point you to the right direction, the filters are created here in the doFilter method.

I don't know where is the actual problem, but here is the QueryBuilder this is the object that creates the query based on the filters passed from the doFilter method.

[sebj54]

Hi!
I'm having this problem too! And I'd like to help too :)

First of all, what's the correct query? As I never made it work, I'm not sure what I'm doing is correct.

I have a master collection inspirations which has a many-to-many field called saisons (sorry for French names, I had to!). It is linked with the saisons table via the ìnspirations_saisons` associative table.

So we have:

----------------   ------------------------   -----------
| inspirations |   | inspirations_saisons |   | saisons |
|--------------|   |----------------------|   |---------|
|              |   | id                   |   |         |
| id           | ← | inspiration          |   |         |
|              |   | saison               | → | id      |
----------------   ------------------------   -----------

If I want to search for all inspirations with 1 as ID, what should be my API query?

First attempt: saisons.saison.id

http://local.directus.com/_/items/inspirations?fields=*.*,saisons.saison.*&filter[saisons.saison.id][has]=1

When I hit this URL, I get the following error:

{
    "error": {
        "code": 202,
        "message": "Unable to find field \"saison\"",
        "class": "Directus\\Database\\Exception\\FieldNotFoundException",
        "file": "C:\\wamp64\\www\\directus\\src\\core\\Directus\\Database\\SchemaService.php",
        "line": 333
    }
}

Second attempt: saisons

http://local.directus.com/_/items/inspirations?fields=*.*,saisons.saison.*&filter[saisons][has]=1

When I hit this URL, I get the following error:

{
    "error": {
        "code": 9,
        "message": "Failed generating the SQL query. Statement could not be executed (42000 - 1066 - Not unique table\/alias: 'inspirations')",
        "query": "SELECT `inspirations`.`id` AS `id`,`inspirations`.`id` AS `id`,`inspirations`.`titre` AS `titre`,`inspirations`.`couverture` AS `couverture`,`inspirations`.`accroche` AS `accroche`,`inspirations`.`pays` AS `pays`,`inspirations`.`groupe` AS `groupe`,`inspirations`.`duree` AS `duree`,`inspirations`.`type` AS `type`,`inspirations`.`activite` AS `activite`,`inspirations`.`encadrement` AS `encadrement`,`inspirations`.`vol` AS `vol`,`inspirations`.`tags` AS `tags`,`inspirations`.`repas` AS `repas`,`inspirations`.`transport` AS `transport`,`inspirations`.`budget` AS `budget`,`inspirations`.`a_savoir` AS `a_savoir`,`inspirations`.`reference` AS `reference`,`inspirations`.`image_contenu_1` AS `image_contenu_1`,`inspirations`.`image_contenu_2` AS `image_contenu_2`,`inspirations`.`image_contenu_3` AS `image_contenu_3`,`inspirations`.`image_contenu_4` AS `image_contenu_4`,`inspirations`.`image_contenu_5` AS `image_contenu_5`,`inspirations`.`avis_expert` AS `avis_expert`,`inspirations`.`texte_fin` AS `texte_fin`,`inspirations`.`texte_debut` AS `texte_debut`,`inspirations`.`texte_milieu` AS `texte_milieu`,`inspirations`.`carte` AS `carte` FROM `inspirations` WHERE `inspirations`.`id` IN (SELECT `inspirations`.`saisons` AS `saisons` FROM `inspirations` RIGHT JOIN `inspirations` ON `inspirations`.`id` = `inspirations`.`saisons` GROUP BY `inspirations`.`saisons` HAVING COUNT(*) >= '1') ORDER BY `inspirations`.`id` IS NULL,`inspirations`.`id` ASC,`inspirations`.`id` ASC LIMIT 200 OFFSET 0",
        "class": "Directus\\Database\\Exception\\InvalidQueryException",
        "file": "C:\\wamp64\\www\\directus\\src\\core\\Directus\\Database\\TableGateway\\BaseTableGateway.php",
        "line": 732
    }
}

If you don't understand my example, I followed this guide: https://docs.directus.io/app/admin
/relationships.html#many-to-many.
Just replace:

• movies by inspirations
• genres by saisons
• movie_genres by inspirations_saisons

Thanks in advance for your help!

[sebj54]

I tried to make it work all this afternoon without success. From what I understood, there is actually no handling at all for filters on a M2M field, that's right?

And to answer myself, I guess the correct filter is my first attempt, but I'm not getting the same message now.

I created a test table with a many to many relation with types table.

--------   --------------   ---------
| test |   | test_types |   | types |
|------|   |------------|   |-------|
|      |   | id         |   |       |
| id   | ← | test       |   |       |
|      |   | type       | → | id    |
--------   --------------   ---------

Now, when I hit this URL: http://local.directus.com/_/items/test?fields=*.*&filter[types.type.id][has]=2, I get this error:

{
    "error": {
        "code": 12,
        "message": "Operator \"has\" only works for one-to-many fields",
        "class": "Directus\\Exception\\UnprocessableEntityException",
        "file": "C:\\wamp64\\www\\directus\\src\\core\\Directus\\Database\\TableGateway\\RelationalTableGateway.php",
        "line": 1404
    }
}

I tried to change some code in QueryBuilder like you said @wellingguzman but I think the problem comes from RelationalTableGateway, one of these two methods: parseDotFilters or doFilter.

[benhaynes]

So sorry for the late reply @sebj54 — @wellingguzman will be checking this out today to offer some guidance!

[wellingguzman]

@sebj54 you are correct, the QueryBuilder uses whatever the doFilter add to the query builder conditionals value. (Ref 1, Ref 2).

I don't know if you shared me your schema before, but I either way I would like if you share the one you are using so I can help you better understand the filters based on your schema.

You are on the right path.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[theharshin]

Hey @benhaynes, @BJGajjar is looking into this. Just mentioning here to keep this issue alive 😄

[benhaynes]

Excellent!! This would be a great one to get resolved.

[itsmerhp]

Hello @benhaynes
I have a question about M2M filter expected behaviour.
For eg: Collection C1 has a many-to-many field cm_relate which is linked with collection C2 and junction collection is JC.

Question : Filter on M2M field cm_relate(c1?fields=*.*&filter[cm_relate.id][eq]=1 OR c1?fields=*.*&filter[cm_relate][like]=search_term) should be applied on junction collection JC or related collection C2?

Actually, based on current structure in M2M relationship, C1 is related with junction collection JC with O2M relationship, and due to that filter is applying currently on junction collection.

[benhaynes]

It's an interesting question! Directus technically treats a M2M as: O2M+M2O. This is nice since it simplifies how many relational types we have, and allows for data management on the junction table.

For example, you might have a schema like this:

C1

• id
• title
• description

JC

• id
• c1_id
• c2_id
• quantity

C2

• id
• title
• description

The important field above is jc.quantity. So we need to make sure that our platform supports managing the junction collection data too.

Admins not using this feature might be confused by why they need *.*.* (three levels) to access items related through a M2M (they might only expect 2 levels). But I think it's best to be consistent and follow the actual data model.

Soooo, in summary: I think we should use c1?fields=*.*.*&filter[cm_relate.c2_id.id][eq]=1 and apply all filters to the junction. If a user wants to filter on the related data they would go one level deeper.

Another idea (for the future) would be to add a new parameter as a helper for M2M filtering to keep syntax shorter/cleaner. eg: c1?m2m[cm_relate.id][eq]=1 — maybe this could even automatically get the field depth so you don't need to include the fields param. But that's a different discussion.

@rijkvanzanten or community thoughts?

[itsmerhp]

Hello @benhaynes
Thank you for your explanation.

Please go through following two cases :

1. c1?fields=*.*.*&filter[cm_relate.quantity][eq]=X
   Expected : Filter should apply on quantity field of Junction Collection JC and records should be fetched from collection C1 accordingly.
2. c1?fields=*.*.*&filter[cm_relate.c2_id.title][like]=X
   Expected : Filter should apply on title field of C2 collection and records should be fetched from collection C1 accordingly.

Please correct me if anything wrong in above both cases.

And if these two cases are correct, then it is already implemented in this PR, please review it.

[benhaynes]

That looks correct to me!

Does your PR change the basic way that the API works though? I ask because then this is a breaking change, which is a big deal for backwards compatibility. Also, it's a bummer that we need to reference all m2m data in a third relational level... but I can't think of a better way to handle this.

@BJGajjar @rijkvanzanten — thoughts?

[binal-7span]

@benhaynes

The logic which @itsmerhp explains seems correct to me too! I will check the PR ASAP.

[binal-7span]

Fixed in #926