filtering on pivot seems to not work properly

[FXLaroche]

Laravel Rest Api Version

2.8.7

Laravel Version

11

PHP Version

8.3

Database Driver & Version

No response

Description

Considering the following example:

I want to search a team, and join to the result the users who are part of this team, and amongst these users, those who have the value 'left_at' at NULL on the pivot table. Here is my payload:

{
    "search": {
        "filters": [
            {
                "field": "id",
                "operator": "=",
                "value": "9d6ceb1a-3573-4ad7-837b-424637fa4905"
            },
            {
                "field": "users.pivot.left_at",
                "operator": "=",
                "value": null
            }
        ],
        "includes": [
            {
                "relation": "users",
                "filters": 
                [
                    {
                        "field": "teams.pivot.left_at",
                        "operator": "=",
                        "value": null
                    }
                ]
            }
        ],
        "sorts": [
            {
                "field": "label",
                "direction": "asc"
            }
        ],
        "page": 1,
        "limit": 10
    }
}

For my tests I've filtered on the object and its relation independently, as well as on both.

However in any case I get results where 'left_at' is not null.

{
    "current_page": 1,
    "data": [
        {
            "id": "9d6ceb1a-3573-4ad7-837b-424637fa4905",
            "label": "Team1 ",
            "created_at": "2024-11-06T16:41:03.000000Z",
            "updated_at": "2024-11-06T16:41:03.000000Z",
            "users": [
                {
                    "id": "9d6ceb1a-3da8-4e72-ad49-f3d81bbd63c1",
                    "firstname": "Arthur",
                    "lastname": "Mercier",
                    "email": "[email protected]",
                    "pivot": {
                        "left_at": "2024-10-15",
                    },
                },
                {
                    "id": "9d6ceb1a-4958-4efd-884a-43ae870596cc",
                    "firstname": "Matthieu",
                    "lastname": "Rousset",
                    "email": "[email protected]",
                    "pivot": {
                        "left_at": null,
                    },
                },
                {
                {
                    "id": "9d6ceb1a-4f0f-44a9-8392-421642d0a794",
                    "site_id": "9d6ceb16-e4c6-41fc-a4a4-6230e49467ec",
                    "firstname": "Roger",
                    "lastname": "Potier",
                    "email": "[email protected]",
                    "pivot": {
                        "left_at": null,
                    },
                },
                {
                    "id": "9d6ceb1a-435a-47e6-97a3-9c3b0897b354",
                    "site_id": "9d6ceb16-e4c6-41fc-a4a4-6230e49467ec",
                    "firstname": "Yves",
                    "lastname": "Marion",
                    "email": "[email protected]",
                    "pivot": {
                        "left_at": "2024-05-08",
                    },
                }
            ]
        }
    ],
    "from": 1,
    "last_page": 1,
    "per_page": 10,
    "to": 1,
    "total": 1,
    "meta": []
}

It seems that the filter on the pivot table is not working at all, or that I misunderstood the way it works.
I've found a way around using a scope, but it would be nice to have it filter properly.

Steps To Reproduce

Make a search request on a model with a many-to-many relationship, inluding the relationship and filtering on a data on the pivot table.

[GautierDele]

This could be related to include, feels strange to me
I'll cover this with an unit test and come back to you depending on the result

[GautierDele]

@FXLaroche I added an unit test to cover this, seems to be working
Am I missing something ?
#144

[FXLaroche]

I think the difference is that I am filtering on a many to many relationship, and that I am using two filters.

I looked at the SQL request:
select `users`.* from `users` where (exists (select *, `teams`.* from `teams` inner join `team_user` on `teams`.`id` = `team_user`.`team_id` where `users`.`id` = `team_user`.`user_id` and `teams`.`id` = '9d6ceb1a-3573-4ad7-837b-424637fa4905') and exists (select *, `teams`.* from `teams` inner join `team_user` on `teams`.`id` = `team_user`.`team_id` where `users`.`id` = `team_user`.`user_id` and `team_user`.`left_at` is null)) and `users`.`deleted_at` is null order by `users`.`id` desc

it creates two 'exists' subqueries, one for each filter. Ideally I'd rather have both filters in the same subrequest. I tried to put them in a 'nested' structure but it doesn't make much difference.
With the two subqueries, as long as users have 'left_at' = null in a team_user line they will be returned.

[GautierDele]

I'm not able to reproduce this please reopen the issue if you have something helping me reproducting this