Filter by relationship field and latest fails

[Sarunas-v-ipxo]

Hello Eric, thanks for a nice filtering package.

I'm using it to filter two related tables one to many. Lets call it users and orders and both have status fields. Lets say I need to filter all users with status 'active' and having latest orders with status 'completed'. I have and SQL that is filtering data correctly, but I cant convert it into EloquentFilter correctly. I have UsersFilter with method:

    public function status(string $value): self
    {
        return $this->where('users.status', '=', $value);
    }

and in the same UsersFilter method:

    public function order(string $value): self
    {
        return $this
            ->join(
                DB::raw(
                    '(SELECT * FROM `orders` WHERE `id` IN (SELECT MAX(`id`) FROM `orders` GROUP BY `user_id`) AND `deleted_at` IS NULL) AS o'
                ),
                'users.id',
                '=',
                'o.user_id'
            )
            ->where('o.status', '=', $value);
    }

But this is not returning required results.

[Sarunas-v-ipxo]

After lots of experiments I have found a way how to get what I need. The method looks like this now:

    public function order(string $value): self
    {
        return $this
            ->whereHas('order', function (Builder $query) use ($value) {
                $query
                    ->whereRaw('order.id = (SELECT MAX(id) FROM orders WHERE orders.user_id = users.id)')
                    ->where('order.status', '=', $value);
            });
    }

[Sarunas-v-ipxo]

UPDATE: eventually this is not working with MariaDB 10.3. version - pagination count function cause error:

SQLSTATE[42000]: Syntax error or access violation: 1235 This version of MariaDB doesn't yet support 'SUBQUERY in ROW in left expression of IN/ALL/ANY' (SQL: select count(*) as aggregate from `users` where `users`.`status` = pending and exists (select * from `orders` where `users`.`id` = `orders`.`user_id` and orders.id = (SELECT MAX(id) FROM orders WHERE orders.user_id = users.id) and `orders`.`status` = rejected and `orders`.`deleted_at` is null) and `users`.`deleted_at` is null)

[Tucker-Eric]

@Sarunas-v-ipxo Looks like the issue is with how you're constructing the query and not the way this package is filtering relations?