Laravel query builder when grouping

Question

I've started with Laravel for a week now and something bother me about the Query Builder and the when clause.

I'm using it like so:

$query->when($filters['full-time'] ?? false, fn ($query) =>
  $query->where('contract_type', JobPositionsEnum::class::FullTime)
);
      
$query->when($filters['location'] ?? false, fn ($query, $location) =>
  $query->where('location', 'like', '%' . $location . '%')
);

$query->when($filters['search'] ?? false, fn ($query, $params) => 
  $query->where('position', 'like', '%' . $params . '%')
    ->orWhere('description', 'like', '%' . $params . '%')
    ->orWhereHas('company', function ($query) use ($params) {
      $query->where('name', 'like', '%'.$params.'%');
    })
);

With this query I was hoping received an SQL request looking like this:

SELECT * FROM `jobs` 
WHERE `contract_type` = 'Full time' 
AND `location` like '%United%'
AND (`position` like '%S%' or `description` like '%S%' or EXISTS (SELECT * FROM `companies` WHERE `jobs`.`company_id` = `companies`.`id` and `name` like '%S%'))

But instead I'm receiving this:

SELECT * FROM `jobs` 
WHERE (
  `contract_type` = 'Full time' and 
  `location` like '%United%' and 
  `position` like '%S%' or `description` like '%S%' or EXISTS (
    SELECT * FROM 
      `companies` 
       WHERE `jobs`.`company_id` = `companies`.`id` 
       and `name` like '%S%'
   )
)

I'm not sure to understand why the when group my query when I don't want to, did you know what I'm missing here ?

Move your OR queries into a subquery closure.

aynber
– aynber

2022-09-27 12:51:25 +00:00
Commented Sep 27, 2022 at 12:51 — aynber
– aynber, Commented Sep 27, 2022 at 12:51

AbdulkadirFaghi · Accepted Answer · 2022-09-27 13:16:19Z

2

You should use an additional where to group the or conditions.

Your last when should look like this:

$query->when($filters['search'] ?? false, function ($query) use ($params) {
    $query->where(function ($query) use ($params) {
        $query->where('position', 'like', '%' . $params . '%')
            ->orWhere('description', 'like', '%' . $params . '%')
            ->orWhereHas('company', function ($query) use ($params) {
                $query->where('name', 'like', '%' . $params . '%');
            });
    });
});

edited Sep 27, 2022 at 13:16

answered Sep 27, 2022 at 13:10

AbdulkadirFaghi

961 silver badge5 bronze badges

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

1 Comment

Jérémie Bardon Over a year ago

Ow.. I have to go deep about this ORM! I'm gonna read the doc carefully, thank you!

Collectives™ on Stack Overflow

Laravel query builder when grouping

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related