Write query builder with group_concat

Try using orWhereRaw:

->orWhereRaw("group_concat(value SEPARATOR '') LIKE ?", [$value])

->orWhereRaw("group_concat(value SEPARATOR '') LIKE ?", [$value]);

only this will never work, because group_concat needs select first and I need other conditions (that are in mysql query) after group_concat, and then to compare that with $value. But I do not know how to write that.

Maybe to explain better:

I have a table employee_contacts in which there are a columns channel and value . In channel column there can be values phone_dial, phone_number, email, etc. and in value column value for phone_dial, phone_number, email etc. So I need to get full phone number (from phone_dial and phone_number) and compare that to passed $value. Idea was to get full phone number with group_concat, query with mysql is working and I am getting a table with phone column. Then I want that to compare that against passed value.

Within subquery

$query->orWhereHas('contacts', function ($query) use ($value)  {

} 

I am already in employee_contacts table.

It was one additional comment, someone was asking what is the error that I am getting. Not sure why I can not see this comment anymore, but I will answer it.

I tried this:

->orWhere('employee_id', DB::raw("group_concat(value SEPARATOR '') as phone"))
                                     ->where('channel', 'phone_dial')->orWhere('channel', 'phone_number')
                                     ->groupBy('employee_id')->where('phone', 'LIKE', $value);

and god this error: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as phone and channel = ? or channel = ?) and

This is working:

     ->select('employee_id', DB::raw("group_concat(value SEPARATOR '') as phone"))
                                         ->where('channel', 'phone_dial')->orWhere('channel', 'phone_number')
                                         ->groupBy('employee_id')->having('phone', 'LIKE', $value);

but I can not add it after

$query->where('channel', 'email')->where('value', 'LIKE', $value)

Have you considered using the db facade, a good example:

https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder

Hi @snej you are correct, GROUP_CONCAT cannot be used in a WHERE clause.

This is due to GROUP_CONCAT being an aggregate function. As the WHERE clauses are processed before grouping, one cannot use it there, much like SUM, AVG, COUNT and other aggregate functions cannot be used in a WHERE clause. (other than fetching the data in a subquery on the FROM clause and them filtering them on the outer query).

When one needs to filter the results from an aggregate function, the HAVING clause should be used, as it is computed after grouping the results. So, please try:

$query
    ->select('employee_id', DB::raw("GROUP_CONCAT(value SEPARATOR '') as phone"))
    ->where('channel', 'phone_dial')
    ->orWhere('channel', 'phone_number')
    ->groupBy('employee_id')
    ->havingRaw("GROUP_CONCAT(value SEPARATOR '') LIKE ?", [$value]);

The downside of this approach is that you will filter by channel before grouping and then filter all the results by phone after grouping, hich might not be what you wanted. If you want to filter by channel (regardless of phone) OR phone (regardless of channel) you might want to move all conditions to the HAVING clauses as such:

$query
    ->select('employee_id', DB::raw("GROUP_CONCAT(value SEPARATOR '') as phone"))
    ->groupBy('employee_id')
    ->having('channel', 'phone_dial')
    ->orHaving('channel', 'phone_number')
    ->orHavingRaw("GROUP_CONCAT(value SEPARATOR '') LIKE ?", [$value]);

Notice that this will fetch all rows, group them using GROUP_CONCAT and finally filter them using the conditions in the HAVING clauses. If your table is huge in rows you could have some performance issues.

Other than moving the filters to the HAVING clause I don't know any other efficient method of mixing aggregate and non-aggregate filters.

Thank you very much. I tried both solution. But non of them are solving the issue of mixing filters. I ended up creating one more sub-query for the same table and added there query with selectand having like this(although knowing that this is a bad solution but it is working ):

->orWhereHas('contacts', function ($query) use ($value) {
	$query->where('channel', 'email')->where('value', 'LIKE', $value);
})
->orWhereHas('contacts', function ($query) use ($value) {
	$query->select('employee_id', DB::raw("group_concat(value SEPARATOR '') as phone"))
	->where('channel', 'phone_dial')->orWhere('channel', 'phone_number')
	->groupBy('employee_id')->having('phone', 'LIKE', $value);
})

If the sub query does the job, I think you might not need to GROUP_CONCAT the phone numbers, parsing through your solution I think this might work:

->whereHas('contacts', function ($query) use ($value) {
    $query->whereIn('channel', ['email', 'phone_dial', 'phone_number']);
    $query->where('value', 'LIKE', "%{$value}%");
});

Merging both query requirements:

1. channel can be either : email, phone_dial or phone_number
2. value must contain the search string ($value)

Taking your first approach as a basis I think you wanted to use GROUP_CONCAT to check only for a record, but as ->whereHas(...) uses an EXISTS sub-query condition, it doesn't matter how many records the sub-query yields, as long one matches the condition.

If you see this response (not many people check the threads after finding a solution), give it a try and, please, let me know if it works.

You really wanted to help so of course that I will check this, it's the least I can do. Something similar was one of my first tries.

I tried your solution exactly but when I am searching for the whole number +123 456789 (phone_dial value + phone_number value) it does not match anything. Additionally, when entering only a phone_number value which exists, it looks like it returns all results that does have something in value among channel's email, phone_dial and phone_number.

Thanks for the update, now I see that the two phone fields hold different parts of the same phone number.

As I didn't have the context I guessed that maybe those two phone related channels could hold different whole numbers.

As that is the case I guess the way to go for a DB-only solution is using the GROUP_CONCAT solution.

Have a nice day!