php - Adding brackets to BETWEEN Query in Laravel?

This is a shortened version of model query:

User::select('users.*')
    ->when(!empty($this->searchInput['search[term]']), function ($query) {
        return $query->where(
            'biography', 'LIKE', '%' . $this->searchInput['search[term]'] . '%'   
        );
    })
    ->when(!empty($this->searchInput['search[location]']), function ($query) {
        return $query->where(
            'country_id',
            $this->searchInput['search[location]']
        );
    })
    ->when(!empty($this->searchInput['engagement']), function ($query) {
        foreach ($this->searchInput['engagement'] as $key => $value) {
            if ($key === array_key_first($this->searchInput['engagement'])) {
                $query->whereBetween('engagement_rate', $value);
            } else {
                $query->orWhereBetween('engagement_rate', $value);
            }
        }
    });
Copy code

(In case you are wondering, I am using$key === array_key_first to ensure the firstBETWEEN usesAND, and afterwards usesOR depending on the user's selections)

This equates to the following SQL query:

SELECT
    `users`.*
FROM
    `users`
WHERE
    `country_id` = '31'
    AND `engagement_rate` BETWEEN '6' AND '20' OR `engagement_rate` BETWEEN '3.5' AND '6'
Copy code

ThewhereBetween in Eloquent does not include brackets, and as such the country_id selector gets ignored. I need the final SQL statement to be the below (with brackets):

SELECT
    `users`.*
FROM
    `users`
WHERE
    `country_id` = '31'
    AND (`engagement_rate` BETWEEN '6' AND '20' OR `engagement_rate` BETWEEN '3.5' AND '6')
Copy code

My question:

How can I change my Eloquent query to reflect the above?

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.