CODEWITHSUNDEEP
phplaravel
John Cliven
John Cliven

Reputation: 1231

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);
            }
        }
    });

(In case you are wondering, I am using $key === array_key_first to ensure the first BETWEEN uses AND, and afterwards uses OR 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'

The whereBetween 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')

My question:

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

Upvotes: 1

Views: 325

Answers (1)

porloscerros Ψ
porloscerros Ψ

Reputation: 5098

Add a parameter group to the whereBetween and orWhereBetween clauses:

User::select('users.*')
// ...
->when(!empty($this->searchInput['engagement']), function ($query) {

    query->where(function ($q) {
        foreach ($this->searchInput['engagement'] as $key => $value) {
            if ($key === array_key_first($this->searchInput['engagement'])) {
                $q->whereBetween('engagement_rate', $value);
            } else {
                $q->orWhereBetween('engagement_rate', $value);
            }
        }
    });

});

Upvotes: 1

Related Questions