Laravel query filter

Question

There are three tables

workshops (column: id,name,phone,email)
services (column: id, name)
service_workshop (column: id, workshop_id, service_id, active)

Between tables workshops and services relationships many to many. For filtering in the model i use local scopes https://laravel.com/docs/8.x/eloquent#local-scopes

When choosing services on the front-end, I should get workshops that have the selected services active.But I don't get what I need(( Here is my method:

// Model Workshop
public function scopeWithFilters(Builder $query, array $filters)
{
    // services dynamic value
    $services = array_key_exists("services", $filters) ? $filters["services"] : [];

    return $query->when(count($services), function ($query) use ($services) {
        $query->whereIn('id', function ($query) use ($services) {
            $query->select('workshop_id')
                ->from('service_workshop')
                ->where('active', true)
                ->whereIn('service_id', $services);
        });
    });
}

With $services = [1,2] , the condition whereIn('service_id', $services) returns worksop_id 2 and 3, but should be 3...Because workshop_id = 2 service_id = 2 contains active = 0

Image table service_workshop

How to write a request correctly?

Arif Nurdiansyah · Accepted Answer · 2022-04-28 21:47:35Z

1

Can you please share the full rows of the result? If I am not mistaken. The result is valid because it's return workshop_id 2 due the condition of the first row or id (117)

The value is : service_id : 1 workshop_id : 2 active : 1

which means, you have workshop_id 2 with active value

Remember you add whereIn [1,2] which is valid as long as the service_id is 1 or 2

Update :

To make sure it's only return all workshop_id that contains all service_id with status active you need to group it and add having condition

Try this :

return $query->when(count($services), function ($query) use ($services) {
        $query->whereIn('id', function ($query) use ($services) {
            $query->select('workshop_id')
                ->from('service_workshop')
                ->groupBy('workshop_id')
                ->where('active', true)
                ->whereIn('service_id', $services)
                ->having(DB::raw('count(*)', >=, count($services)));
        });
    });

MySQL Query :

SELECT workshop_id FROM service_workshop WHERE service_id IN (1,2) AND active = 1 GROUP BY workshop_id HAVING COUNT(*) >= 2

Note : Change the condtion of having count to the length of the service condition

edited Apr 28, 2022 at 21:47

answered Apr 28, 2022 at 19:04

Arif Nurdiansyah

8011 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Goryyn Over a year ago

In the photo above, the entire service_workshop table

Goryyn Over a year ago

whereIn [1,2] yes I understand the expression.I don't understand how to correctly compose the request so that it turns out: active = 1 and (service_id = 1 and service_id = 2) because services contains dynamic values.

Arif Nurdiansyah Over a year ago

So you want to return workshop_id that contains service_id 1 active and service_id 2 active?

Goryyn Over a year ago

Yes, only the values to filter service_id , it's a dynamic array of values from the front-end.

Arif Nurdiansyah Over a year ago

Try this ``` return $query->when(count($services), function ($query) use ($services) { $query->whereIn('id', function ($query) use ($services) { $query->select('workshop_id') ->from('service_workshop') ->groupBy('workshop_id') ->where('active', true) ->whereIn('service_id', $services) ->having(DB::raw('count(*)', >=, count($services))); }); }); ```

Collectives™ on Stack Overflow

Laravel query filter

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related