WhereConcat to concatenate multiple columns

[snake03]

The problem

I often find myself chaining multiple fields to make a search query.
There are scenarios in which it is not necessary to rely on a more robust search system, such as Scout; instead it is sufficient to concatenate multiple fields and see if there is a match with a search key.
Eg:

Model::where(DB::raw("CONCAT_WS(' ', 'first_name', 'last_name', 'email')"), "like", "%{$query}%");

The problem with using such a function is that it is tied to the underlying database engine.
I often use Sqlite locally, the same thing for testing, while in production I use Mysql. In Sqlite doesn't exists CONCAT_WS, so there isn't a "quick" way to achieve that.

The solution

My suggestion is to introduce function like this in Eloquent:

Model::whereConcat(['field_1', 'field_2'], $operator, $value);

If you think it can be interesting I can propose a PR.

Thanks!

[netpok]

I think this should be the last resort for a search like this, generally using functions in where is a bad idea in SQL since they cannot be indexed.

[snake03]

Hi @netpok! I know that's not ideal if we talk abount performance. But I think that's a common use case to concatenate two fields (eg first_name and last_name) and you want to give the user the ability to search in a single "full_name" field.
You can't rely on mutators, since they are compiled after the sql query, so I don't recall any other way to achieve that without using Scout or more robust way (but often "too much" for this simple thing)

[netpok]

If you really need to search in both of them at the same time (meaning where fname like value or lname like value is not enough) then your best option would be something like generated columns: https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html, fullname as concat... index it then search in it