filter on relation pivot

[GautierDele]

Hello there,

On a Many To Many resource it's written that you can filter by pivot value but when you want to filter on a pivot of a relation it seems like it's not working:

{
    "filters": [
        {
            "field": "task_type_id",
            "operator": "=",
            "value": "2d1399a2-2e61-4af5-b2ff-7c0726141998"
        },
        {
            "type": "and",
            "nested": [
                {
                    "field": "customFields.id",
                    "operator": "=",
                    "value": "96c2d325-5980-42fd-8a05-25cb68b45557"
                },
                {
                    "field": "customFields.pivot.value",
                    "operator": "like",
                    "value": "%bb%"
                }
            ]
        }
    ]
}

Basically, Orion is trying to access the pivot relation on "customFields" model here. "pivot relation can not be found".
Did we go on a not covered functioning ?

Thanks,

[GautierDele]

I investigated and it seems like this isn't covered right now:

@alexzarbn I this something you had in mind or not at all ?

[alexzarbn]

Hi @GautierDele,

It is mentioned in the docs, but I agree that it could be more descriptive.

As for the issue, could you please check, if it persists even outside of the "nested" query? There are tests covering this functionality, which means it is most likely an issue with the "nested" queries.

[GautierDele]

I don't think this is related to nested queries since "relation" gives the whole string before the field and the check is strict to "pivot" so when you want to join "pivot.field" it works but when it is "relation.pivot.field" it doesn't.

I'll have a check on my spare time to implement this since it's required for us and would be really usefull for people.

[GautierDele]

I did investigate but it would need much more time than i tought because the code isn't prepared for this.

If someone wants to take a look, i let this open.

FYI: needs to impact pivot action on: "filtering", "sorting" and "qualifyScopedFilterField"