Not simple filters in Laravel. Is there a solution?

Question

Laravel

Hi all!
There is an entity - for example, a product.
It is necessary to implement an ajax filter, for example, to filter by criteria such as quantity, in stock, and so on.
It's very easy to do, I do it like this:

$users->where(function ($query) use ($filter) {
    foreach ($filter as $item) {
        $query->orWhereIn($item[0], $item[1]);
    }
});

but when I need to compare prices and the prices are in another table, a one-to-many relationship does not work for me,
so I add the code below

foreach ($filter as $item) {
    if ($item[0] == 'Rates') {
        $users->join('rates', function ($join) use ($item) {
            $join->on('users.id', '=', 'rates.user_id')
                ->where(function ($query) use ($item) {
                    foreach ($item[1] as $rates) {
                        $query->where('rates.time', '=',  '1')
                            ->where('rates.full_rates', '>=', 0)
                            ->where('rates.full_rates', '<=', 150);
                    }
                });
        });
    }
}

This option filters, but it turns out that the filter works either by price or by value
. Is it possible to combine the selection?

Answer 1

Taki figured it out

$usersFiltersOther = $users->where(function ($query) use ($filter) {
                foreach ($filter as $item) {

                    $query->orWhereIn($item[0], $item[1]);

                }
            });

            $selectUsers = null;

            foreach ($filter as $item) {
                if ($item[0] == 'Rates') {
                    $selectUsers = App\User::whereHas('rate', function ($q) use ($item) {
                        $q->where(function ($query) use ($item) {
                            foreach ($item[1] as $rates) {

                                $range = explode(' / ', $rates);


                                $query->orWhere('rates.time', '=', str_replace('s', '', $range[1]))
                                    ->where('rates.incall_rates', '>=', $from)
                                    ->where('rates.incall_rates', '<=', $to);

                            }
                        });

                    })->union($usersFiltersOther)->get();
                }
            }

you can combine selections through the union method , the article https://laravelinfo.com/otladka-zaprosov-v-laravel
also helped

Answer 2

Solved the problem
SELECT a.id, u.`group`, u.`id`, a.*
FROM `ankets` a
INNER JOIN anket_group ag ON ag.anketa = a.id AND ag.`status` = '1' AND (ag.`group` = '3' OR ag.`group` = '5')
LEFT JOIN `anket_user` au ON au.`anketa` = a.`id` AND au.`status` = '1' AND (SELECT COUNT(*) FROM `users` us WHERE us.id = au.`user` AND (us.`group` = '3' || us.`group` = '5')) > '0'
LEFT JOIN `users` u ON u.`id` = au.`user`
WHERE a.`delete` = 0 AND a.`status` = 2 AND u.`group` IS NULL GROUP BY a.`id`

Answer 3

Hello.
If you give descriptions of the tables, then you can try to make a query more accurately.
Complex queries need to be composed in parts. Let's first combine all the tables you've given.
Look at an example. It's still conditional.
SELECT * FROM ankets AS a
INNER JOIN anket_group AS ag ON a.id = aq.aId
INNER JOIN ankets_users AS au ON a.id = au.aId
INNER JOIN users as u ON au.uId =
u.id . I could be wrong. Refine it and post the real request.
Next, you need to make such a filter:
You need to add filters, too, in parts. It's hard to follow the logic all at once. In addition, the definition of "have access" is not clear. How is it expressed in tables?
First write a filter 1) and test only it, then 2) and so on. to 4.
1) who are sent to group "3" or "5"
2) then consider adding this: and define users who have group "3" or "5"
3) then add the following filter: and have access to this profile
- which are sent to group "3" or "5"
4) and finally the last filter: but not assigned to users with group "3" or "5"
UPDATE:
SELECT u.`group`, u.`id`, a .*
FROM `ankets` a
INNER JOIN anket_group ag ON ag.anketa = a.id AND ag.`status` = '1'
INNER JOIN `anket_user` au ON au.`anketa` = a.
WHERE (ag.`group` = 3 OR ag.`group` = 5) AND ((u.`group` = 3 OR u.`group` = 5) OR NOT (u.`group` = 3 OR u. `group` = 5))