SQL is constructed incorrectly when using groupBy() to make Query Builder

[CarterZhou]

The package may have a bug when it constructs SQL that uses group by.

I have two models: Channel and Program, and a Channel could have many Programs. Use case: Display how many programs each channel has in channel list.

A very common way to achieve this is to use count() and group by in SQL.

So in php I define a query builder as follows:

$channels = Channel::select(
            array(
                'channels.id',
                'channels.name_en',
                'channels.name',
                DB::raw('COUNT(programs.id) AS number_of_programs')
            )
        )
        ->leftJoin('programs','programs.channel_id','=','channels.id')
        ->groupBy('channels.id');

And then let the package to generate JSON for us:

return Datatables::of($channels)->make(true);

On the surface it works well, my view showing number of programs of each channel as expected. However, when I typed in search box, like 'sport', Datatables.js complains that there is something wrong when it fetched data and did not render the table.

I dug into laravel.log to see what exactly happened. It turns out that bllim constructed the SQL incorrectly like so:

select count(*) 
as aggregate from 
(select '1' as row from `channels` 
left join `programs` on `programs`.`channel_id` = `channels`.`id` 
where `channels`.`deleted_at` is null 
and 
LOWER(channels.id) LIKE %sport% or LOWER(channels.name_en) LIKE %sport% or LOWER(channels.name) LIKE %sport% or LOWER(COUNT(programs.id)) LIKE %sport%
group by `channels`.`id`) 
AS count_row_table

where all those LIKE operators did not compare with strings, let alone it even generates something is syntax error, like LOWER(COUNT(programs.id)) LIKE %sport%.

Next I did some searching in program list view. The underlying program query builder is simpler without groupBy():

$programs = Program::select(
            array(
                'programs.id',
                'programs.name',
                'programs.name_en',
                'channels.name as channel_name',
                'programs.start_date',
                'programs.end_date',
            )
        )->leftJoin('channels','channels.id','=','programs.channel_id');

This time Datatables.js rendered view correctly. Again, I checked laravel.log and I found no syntax error in the SQL constructed:

select count(*) as aggregate 
from (select '1' as row from `programs` 
left join `channels` on `channels`.`id` = `programs`.`channel_id` 
where `programs`.`deleted_at` is null and (LOWER(programs.id) LIKE '%ebert%' or LOWER(programs.name) LIKE '%ebert%' or LOWER(programs.name_en) LIKE '%ebert%' or LOWER(channels.name) LIKE '%ebert%' or LOWER(programs.start_date) LIKE '%ebert%' or LOWER(programs.end_date) LIKE '%ebert%')) 
AS count_row_table

where term ebert was properly quoted.

[straube]

+1

[Ardakilic]

+1, having same issue, too

[MarkVaughn]

as noted on the main git readme page, anyone who is willing to make a pull request please do