Problem using aggregate functions like group concat.

[feedchannel]

I'm having issues using group_concat with datatables. The query runs perfectly and the results are shown as expected. The problem appears when I do a search.

When using aggregate functions, the filtering should be done with "having" instead of "where" and, at the moment, it seems that it's not supported.

There's no way of "extending" the package and overriding the filtering method as all methods are private, so I was wondering if there's a solution for this predicament.

[MarkVaughn]

Thanks for sharing. What is the error you are seeing from laravel/php? Could you possibly submit a pull with changes you propose. We could also increase the the methods to protected instead of private, however the code changes you want would possibly be beneficial for everyone.

[feedchannel]

So here's my query (simplified):

select `posts`.`id`, GROUP_CONCAT(languages.code SEPARATOR ', ') AS postlanguages, `posts`.`status`
from `posts` 
left join `language_post` on `posts`.`id` = `language_post`.`post_id` 
inner join `languages` on `language_post`.`language_id` = `languages`.`id` 
group by `posts`.`id`

And this is the error I get when i try to filter by postlanguages SQLSTATE[HY000]: General error: 1111 Invalid use of group function (SQL: select posts.id, GROUP_CONCAT(languages.code SEPARATOR ', ') AS postlanguages, posts.status from posts left join language_post on posts.id = language_post.post_id inner join languages on language_post.language_id = languages.id where GROUP_CONCAT(languages.code SEPARATOR ', ') LIKE %en% group by posts.id)

I'm not sure if there's another way of doing this but anyway I cleaned up my code a bit and I'm going to submit a pull request with my changes as soon as possible.

[gcphost]

You have to exclude the concated column from your search http://legacy.datatables.net/usage/columns#bSearchable

Also noticed it did not like select(DB::raw()) so I had to select(db::raw()) on my specific column for the group concat.

[ktunkiewicz]

Added to TO DO list for 1.4.1 release #122

[rmsj]

Hey guys, my query is a little bit more complex:

$users = User::where('users.confirmed', '=', 1) ->leftjoin('assigned_roles', 'assigned_roles.user_id', '=', 'users.id') ->leftjoin('roles', 'roles.id', '=', 'assigned_roles.role_id') ->leftjoin('licences', 'licences.user_id', '=', 'users.id') ->select(array('users.id', 'users.username', 'users.email', 'users.fullname', DB::raw('(SELECT fullname FROM users b WHERE b.id = users.reseller_id) as reseller_name'), 'roles.name as rolename', DB::raw('SUM(CASE WHEN status = "'.Licence::STATUS_PENDING_ACTIVATION.'" THEN 1 ELSE 0 END) as counting_pending'), DB::raw('SUM(CASE WHEN status = "'.Licence::STATUS_ACTIVE.'" THEN 1 ELSE 0 END) as counting_active'), DB::raw('SUM(CASE WHEN status = "'.Licence::STATUS_ACTIVE.'" AND (device_key = "" OR device_key IS NULL) THEN 1 ELSE 0 END) as counting_available'), 'users.confirmed', 'users.created_at')) ->groupBy('users.id', 'users.username', 'users.email', 'users.fullname', 'users.confirmed', 'users.created_at');

It works fine up to the point you try to use the search field. Then because of the SUM(CASE WHEN... stuff it breaks. I fixed by excluding the "SUM" columns from the filtering function when sSearch is present.

[ktunkiewicz]

The first thing you should do when something doesnt work is to examine the resulting SQL queries. This package generates 3 queries:

• counting without filters
• counting with filters
• getting results

Often the 2'nd query breaks because the algorithm of creating counting query is quite primitive (I will be working on this in future).

Open the browser's networking console window and check what's in there. Here is an example of what you can see in Chrome:

Chrome truncates long lines in this view, you can get the full query in response tab. Here you see that my filters works as expected:

Do the same with your queries, find the second and third query and post it here so we can find some solution how to handle this.

[aborza]

I had the same problem today and I solved by creating an temp table where I insert the search results and after parse results from this table to the Datatables, example of my code:

$query_builder = Department::select(DB::raw('departments.id, departments.name, department_types.name as type,GROUP_CONCAT(DISTINCT brands.name) AS brands_list , departments.created_at')) ->leftJoin('department_types','department_types.id','=','departments.department_type_id') ->leftJoin('department_models','department_models.department_id','=','departments.id') ->leftJoin('models','models.id','=','department_models.model_id') ->leftJoin('ranges','ranges.id','=','models.range_id') ->leftJoin('brands','brands.id','=','ranges.brand_id') ->groupBy('departments.id');

    $query = $query_builder->toSql();

    $temp_table_delete = DB::unprepared( DB::raw( "DROP TABLE IF EXISTS temp_departments;" ) );
    $temp_table_create = DB::insert( DB::raw( "

                                        CREATE TEMPORARY TABLE temp_departments (
                                        id INT NOT NULL
                                       ,name VARCHAR(255) NOT NULL
                                       ,type VARCHAR(255) NOT NULL
                                       ,brands_list VARCHAR(255) NOT NULL
                                       ,created_at VARCHAR(255) NOT NULL
                                       );
                                    "
                            ) );
    //insert results into temp table
    DB::insert("INSERT INTO temp_departments ".$query);

    $departments = DB::table('temp_departments')->select('id', 'name', 'type', 'brands_list','created_at');

    return Datatables::of($departments)
            ->add_column('actions', 
                    '<a href="{{{ URL::to(getApp().\'/admin/stores/\' . $id . \'/edit\' ) }}}" class="btn btn-default btn-xs iframe" >{{{ trans(\'button.edit\') }}}</a>
                     <a href="{{{ URL::to(getApp().\'/admin/stores/\' . $id . \'/delete\' ) }}}" class="btn btn-xs btn-danger iframe delete">{{{ trans(\'button.delete\') }}}</a>
                    ')
            ->edit_column('created_at', '{{date("d/m/Y", strtotime($created_at)) != "01/01/1970"?date("d/m/Y", strtotime($created_at)):""}}')  
            ->edit_column('type', '{{strtoupper($type)}}')  
            ->remove_column('id')
            ->make();

[hhvn]

aborza : I want you to know that your example helped me greatly. I am new to Laravel and PHP, but I know about database queries. I have a similar problem, the search function never completing, but it's no big deal since it is not customer-facing form. I still wanted to find out why it happens so when I do create customer-facing forms they are fully functional.

[mihai-scurtu]

Are there any updates on this? Using a temp table isn't the best solution..

[playstation9]

yajra/laravel-datatables-oracle version 5.1 supports collections and searching collections works without using any workarounds.

Example:

this will not work: $query = Users::leftJoin( .... your query with GROUP_CONCAT and CONAT is here ...) return Datatables:of($query)->make(true);

this will work: $query = Users::leftJoin( .... your query with GROUP_CONCAT and CONAT is here ...) return Datatables:of($query)->make(true)->get();