SERVER SIDE PAGINATION PROBLEM WITH AGREGATE COLUMNS

[carlost89]

Summary of problem or feature request

SERVER SIDE PAGINATION PROBLEM WITH AGREGATE COLUMNS

Hi, sorry if this is an duplicate issue but I couldn't find the solution in the others issues and forums.

I'm using the server side mode of datatables and dynamically passing values from the view to the controller and that's working good.

I'm also making a select of an aggregate SUM field with an alias in order to be able to sort the table by that column. Here's the query I'm passing to Datatables:

$query = DB::connection($this->connection)
                    ->table('clientes AS c')
                    ->select([
                        DB::raw("SUM(d.saldo) as deuda_acumulada"),
                        DB::raw("COUNT(CASE WHEN d.saldo > 0 then 1 else null end) as total_facturas"),
                        'c.id_cliente',
                        'c.name',
                        'c.rif',
                        'c.inactivo',
                        'c.campo1',
                        'c.campo5',
                        'd.moneda',
                        'z.zon_des',
                    ])
                    ->leftJoin('docum_cc AS d', 'c.id_cliente', '=', 'd.id_cliente')
                    ->leftjoin('zona AS z', 'c.co_zon', '=', 'z.co_zon')
                    ->where('d.tipo_doc', 'FACT')
                    ->groupBy(['c.id_cliente','c.name','c.rif','c.inactivo','c.campo1','c.campo5','d.moneda','z.zon_des']);

In the javascript side i'm ordering by default by deuda_acumulada column in descending order and data is being rendered good in the table:

Here's is how I'm defining columns in javascript side:

"columns":[
                    {data: "campo5", name: "c.campo5", searchable: true, orderable: true},
                    {data: "zon_des", name: "z.zon_des", searchable: true, orderable: true},
                    {data: "id_cliente", name: "c.id_cliente", searchable: true, orderable: true},
                    {data: "name", name: "c.name", searchable: true, orderable: true},
                    {data: "rif", name: "c.rif", searchable: true, orderable: true},
                    {data: "inactivo", name: "c.inactivo", searchable: true, orderable: true},
                    {data: "moneda", name: null, searchable: false, orderable: true},
                    {data: "deuda_acumulada", name: null, searchable: false, orderable: true},
                    {data: "total_facturas", name: null, searchable: false, orderable: true},
                    {data: "campo1", name: "c.campo1", searchable: true, orderable: true},
                ],

Notice that I set NULL in the name of data: "deuda_acumulada" because i'm not sure what name I have to put there. If I set name attribute to "deuda_acumulada" it doesnt solve the problem.

With that config, where I draw the first 10 records of the table everything is working and I'm also able to change the order of every column. The problem is when I try to change the page, it throwns an error saying: Exception Message:\n\nSQLSTATE[HY000]: General error: 20018 Invalid column name 'deuda_acumulada'

I think I'm missing something and I would like to know what is the name I have to use when referencing alias columns in order to get pagination working.

Thanks in advance.

Code snippet of problem

System details

• Operating System: 4.15.0-60-generic #67-Ubuntu SMP Thu Aug 22 16:55:30 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux
• PHP Version: v7.2.21-1+ubuntu18.04.1+deb.sury.org+1
• Laravel Version: Laravel Framework 5.7.28
• Laravel-Datatables Version: "datatables.net": "^1.10.15",

[carlost89]

Laravel-Datatables Version: "yajra/laravel-datatables-oracle": "~8.0"

[carlost89]

I followed this documentation and it isn't working:

https://datatables.yajrabox.com/eloquent/count

When I select other page the query generated for dataTables changes adding row_number() over (order by [suma] desc) but it doesn't work

The first time the page renders the sql query is using a TOP 10 without OVER clause and the aliased column sorts correctly.

I tested the second query in the database and it only works ad expected I I pass the SUM(saldo) to the OVER CLAUSE. So, I put {data: "suma", name: 'sum(d.saldo)', searchable: false, orderable: true}, in the javascript side and it doesn't work either.

[carlost89]

This is the first query when the datatables renders the first time, This is working:

"query": "select top 50 sum(docum_cc.saldo) suma, count(CASE WHEN docum_cc.saldo > 0 then 1 else null end) total, [clientes].[co_cli], [clientes].[cli_des], [clientes].[rif], [clientes].[inactivo], [clientes].[campo1], [clientes].[campo5], [docum_cc].[moneda], [zona].[zon_des] from [docum_cc] inner join [clientes] on [clientes].[co_cli] = [docum_cc].[co_cli] inner join [zona] on [clientes].[co_zon] = [zona].[co_zon] where [docum_cc].[tipo_doc] = ? group by [clientes].[co_cli], [clientes].[cli_des], [clientes].[rif], [clientes].[inactivo], [clientes].[campo1], [clientes].[campo5], [docum_cc].[moneda], [zona].[zon_des] order by [suma] desc",`

But this is the second query when I select another page, this isn't working:

select * from (select sum(docum_cc.saldo) suma, count(CASE WHEN docum_cc.saldo > 0 then 1 else null end) total, [clientes].[co_cli], [clientes].[cli_des], [clientes].[rif], [clientes].[inactivo], [clientes].[campo1], [clientes].[campo5], [docum_cc].[moneda], [zona].[zon_des], row_number() over (order by [suma] desc) as row_num from [docum_cc] inner join [clientes] on [clientes].[co_cli] = [docum_cc].[co_cli] inner join [zona] on [clientes].[co_zon] = [zona].[co_zon] where [docum_cc].[tipo_doc] = FACT group by [clientes].[co_cli], [clientes].[cli_des], [clientes].[rif], [clientes].[inactivo], [clientes].[campo1], [clientes].[campo5], [docum_cc].[moneda], [zona].[zon_des]) as temp_table where row_num between 51 and 100 order by row_num

It throws this error:

SQLSTATE[HY000]: General error: 20018 Invalid column name 'suma'. [20018] (severity 16)

suma column is being selected like this:

\DB::raw('sum(docum_cc.saldo) suma'),

[yajra]

Have you tried executing the generated SQL directly on your DB browser? For cases like this, I usually test the query manually first then convert it to Larvel SQL version. If it works on DB browser, it should work with the package too. Unless there are some edge cases that the package may not support yet.

[coltisor]

This is the first query when the datatables renders the first time, This is working:

"query": "select top 50 sum(docum_cc.saldo) suma, count(CASE WHEN docum_cc.saldo > 0 then 1 else null end) total, [clientes].[co_cli], [clientes].[cli_des], [clientes].[rif], [clientes].[inactivo], [clientes].[campo1], [clientes].[campo5], [docum_cc].[moneda], [zona].[zon_des] from [docum_cc] inner join [clientes] on [clientes].[co_cli] = [docum_cc].[co_cli] inner join [zona] on [clientes].[co_zon] = [zona].[co_zon] where [docum_cc].[tipo_doc] = ? group by [clientes].[co_cli], [clientes].[cli_des], [clientes].[rif], [clientes].[inactivo], [clientes].[campo1], [clientes].[campo5], [docum_cc].[moneda], [zona].[zon_des] order by [suma] desc",`

But this is the second query when I select another page, this isn't working:

select * from (select sum(docum_cc.saldo) suma, count(CASE WHEN docum_cc.saldo > 0 then 1 else null end) total, [clientes].[co_cli], [clientes].[cli_des], [clientes].[rif], [clientes].[inactivo], [clientes].[campo1], [clientes].[campo5], [docum_cc].[moneda], [zona].[zon_des], row_number() over (order by [suma] desc) as row_num from [docum_cc] inner join [clientes] on [clientes].[co_cli] = [docum_cc].[co_cli] inner join [zona] on [clientes].[co_zon] = [zona].[co_zon] where [docum_cc].[tipo_doc] = FACT group by [clientes].[co_cli], [clientes].[cli_des], [clientes].[rif], [clientes].[inactivo], [clientes].[campo1], [clientes].[campo5], [docum_cc].[moneda], [zona].[zon_des]) as temp_table where row_num between 51 and 100 order by row_num

It throws this error:

SQLSTATE[HY000]: General error: 20018 Invalid column name 'suma'. [20018] (severity 16)

suma column is being selected like this:

\DB::raw('sum(docum_cc.saldo) suma'),

I have the same problem.

My problem is also when I sort table, then try to change the page.

The exact place of error is this: `select

[i].[code] as [item], 
SUM(s.stock) as quantity,
 MAX(s.date) as last_date, 
row_number() over (order by quantity asc) as row_num <--- HERE

from `

It is because of using alias in row_number over what is not allowed in SQL Server. Do anyone have ideas?

Is there a way to make custom ordering?

[benyaminl]

I followed this documentation and it isn't working:

https://datatables.yajrabox.com/eloquent/count

When I select other page the query generated for dataTables changes adding row_number() over (order by [suma] desc) but it doesn't work

The first time the page renders the sql query is using a TOP 10 without OVER clause and the aliased column sorts correctly.

I tested the second query in the database and it only works ad expected I I pass the SUM(saldo) to the OVER CLAUSE. So, I put {data: "suma", name: 'sum(d.saldo)', searchable: false, orderable: true}, in the javascript side and it doesn't work either.

seems @yajra set the count as non search data. If it's non searchable, then how we deal with this aggregate data if we want to search it? EDIT : Seems its answered on #616 and on https://datatables.yajrabox.com/eloquent/post-column-search But I use GET and it works, does it normal or it will have some drawbacks?

    $('#users-table').DataTable({
        processing: true,
        serverSide: true,
        ajax: 'https://datatables.yajrabox.com/eloquent/count-data',
        columns: [
            {data: 'id', name: 'users.id'},
            {data: 'name', name: 'users.name'},
            {data: 'email', name: 'users.email'},
// non search
            {data: 'count', name: 'count', searchable: false},
// non search
            {data: 'created_at', name: 'users.created_at'},
            {data: 'updated_at', name: 'users.updated_at'}
        ]
    });

I got problem with IFNULL, it will use the alias as column name. hmm..

also see this

/**
     * Khusus untuk data Tables!
     */
    public function listDataTables() {
        // @see https://yajrabox.com/docs/laravel-datatables/master/engine-query
        $db = DB::table("tsaldo_membership")
                ->join("mhcustomer", "tsaldo_membership.nomormhcustomer", "=", 'mhcustomer.nomor')
                // @see https://laravel.com/docs/5.8/queries#raw-expressions
                ->select(["tsaldo_membership.nomor", 
                            DB::raw("IFNULL(transaction_kode,'-') as transaction_kode"), 
                            "mhcustomer.nama", "tsaldo_membership.jumlah", "tsaldo_membership.datetime_dapat", 
                            "tsaldo_membership.remark"])
                ->where("tsaldo_membership.tablereferensi", "=", "Inject Adminpanel");
        return DataTables::of($db)->toJson();
    }

it will strangely change the mhcustomer.nama as tsaldo_membership.nama, or I need to specify the table on the DataTables jQuery?

Exception Message:\n\nSQLSTATE[42S22]: Column not found: 1054 Unknown column 
'tsaldo_membership.nama' 
in 'where clause' 
(SQL: select count(*) as aggregate from (select '1' as `row_count` from `tsaldo_membership` inner join `mhcustomer` on `tsaldo_membership`.`nomormhcustomer` = `mhcustomer`.`nomor` where `tsaldo_membership`.`tablereferensi` = Inject Adminpanel 
and (LOWER(`tsaldo_membership`.`nomor`) LIKE %de% or LOWER(`tsaldo_membership`.`transaction_kode`) LIKE %de% or 
LOWER(`tsaldo_membership`.`nama`) LIKE %de% or 
LOWER(`tsaldo_membership`.`datetime_dapat`) LIKE %de% or 
LOWER(`tsaldo_membership`.`jumlah`) LIKE %de% or 
LOWER(`tsaldo_membership`.`remark`) LIKE %de% or 
LOWER(`tsaldo_membership`.``) LIKE %de%)) count_row_table)

[sghernandez]

For me worked this way.

            $diezmos = DB::table('diezmos')
            ->selectRaw('diezmos.id, diezmos.observacion, diezmos.diezmo, diezmos.ofrenda, diezmos.entregado, diezmos.fecha, users.document, users.lastname, users.name') // call every field with its table name
            ->when($inicio, function($query) use ($inicio) { $query->where('fecha', '>=', $inicio); })             
            ->when($limite, function($query) use ($limite) { $query->where('fecha', '<=', $limite); })              
            ->when($user_id, function($query) use ($user_id) { $query->where('users.id', $user_id); })                           
            ->join('users', 'diezmos.user_id', '=', 'users.id')       
            ->orderBy('fecha', 'DESC');                 

            return DataTables::of($diezmos)
                    ->addIndexColumn()
                    ->addColumn('name', function($row){
                        return $row->name. ' '. $row->lastname;
                    })  
                    ->addColumn('document', function($row){
                        return $row->document; // overwrite the field
                    })                         
                    ->addColumn('entregado', function($row){ return $row->entregado ? 'Sí' : 'No'; })                        
                    ->addColumn('action', function($row){
                         return '<a href="javascript:void(0)" class="edit btn btn-primary btn-sm">Ver</a>';        
                    })
                    ->rawColumns(['action'])
                    ->make(true);

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[github-actions[bot]]

This issue was closed because it has been inactive for 7 days since being marked as stale.