Looping where's. Start with where, then orWhere.

Is there a better way to write this? Because of a change in 5.3 I can no longer orWhere everytime. The first orWhere must be a where?

protected function search(array $terms) : Collection
    {
        $method = 'where';
        return User::query()->where(function (Builder $q) use ($terms, &$method) {
            foreach ($terms as $t) {
                $q->$method('first_name', 'LIKE', '%' . $t . '%');
                $q->orWhere('last_name', 'LIKE', '%' . $t . '%');
                $method = 'orWhere';
            }
        })->with('brand')->get();
    }

jared0430

9 years ago

Level 3

~~Can't you use whereIn instead?~~

$q->whereIn('last_name', $terms);

Sorry just realised you are using a LIKE query so this won't work for you. Can you instead use the index to determine whether you are on the first iteration?

foreach ($terms as $index => $t) {
    if ($index === 0) {
        $q->where('last_name', 'LIKE', '%' . $t . '%');

        continue;
    }

    $q->orWhere('last_name', 'LIKE', '%' . $t . '%');
}

Snapey

9 years ago

Level 122

Some thoughts;

sometimes I include a where(1,1) as a 'do nothing' rule so that the rest of the query can use orWhere

If terms are words from the search box, then from a user experience, I would want the search to be more restrictive depending on how much I type. So, if I enter 'paul smith' I want to find

paul smith
pauline smith
paul roger smithers

and not all the paul somethings and all the something smiths

If terms are a comma separated list then your approach may be ok.

I have a similar function on one of my sites and I decided that the best approach was to have a 'forsearch' column against each person. Not ideal, but I would have a column with 'paulsmith', 'paulinesmith', 'paulrogersmith' (to use my previous examples) and then take the user's search and replace all spaces with % so I would be looking for '%paul%smith%' in the forsearch column. This seems to work well for users.

Ideally I would use Algolia (or similar) but I need my SaaS revenue to ramp up first.

jono20201

9 years ago

Level 3

@karlos545 Unless I am missing something, where is that $loop variable defined?

@Snapey The 1=1 idea might be a good one. The only reason I'd like to avoid it is that it seems hacky. The rest of your comments are correct, but this is purely an admin-sided user search, so no need for anything complex/expensive.

@jared0430 Might end up using your solution? The index of an Eloquent collection isn't the model ID I assume?

Thanks for all suggestions.

Snapey

9 years ago

Level 122

At least its easy to understand what is going on

protected function search(array $terms) : Collection
{
    $query = User::where(function($query) use ($terms) {
        $query->where(1,1);
        
        foreach($terms as $t) {
            $query->orWhere('first_name', 'LIKE', '%' . $t . '%');
            $query->orWhere('last_name', 'LIKE', '%' . $t . '%');
        }
    }

    return  $query->with('brand')->get();
}

Snapey

9 years ago

Level 122

Unless I am missing something, where is that $loop variable defined?

its a blade @foreach extension introduced in 5.3 so I don't think you can use it elsewhere

Please or to participate in this conversation.