datatables server side filter and sort

Question

datatables server side filter and sort

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I have a situation with datatables using server-side script

Table localidad
localidad origen
localidad destino

In $ joinQuery: (server-side)

..
LEFT JOIN localidad GO ON A.mxv_loc_ori_fk = GO.loc_id
LEFT JOIN localidad GD ON A.mxv_loc_dest_fk = GD.loc_id
..

In $ columns:

array(
       array( 'db' => '('GO'.'loc_nombre') as origen', 'dt' => "origen", 'field' => 'origen'),
       array( 'db' => '('GD'.'loc_nombre') as destino', 'dt' => 'destino', 'field' => 'destino' )

);

In JS:

"columns": [
    {"data":"origen"},
    {"data":"destino"}
]

All right up to this point. The columns show the info correctly. The problem is when I try to filter or sort, that's where it does not work.

jquery datatables

asked by Enzo Trucchi 26.04.2018 в 17:30

source

2 answers

Question Prestashop 1.7 JavaScript function with parameters does not respond

score 0 · Answer 1

I leave my resolution to the problem in case someone has the same problem:

Create a view of the table that is being double-JOINed. Example

CREATE VIEW localidad_vista (locv_id,locv_nombre,locv_prov_fk)

AS select loc_id, loc_name, loc_province_fk FROM location;

Then, we can change the SQL query. Instead of double JOIN to the table, we do a JOIN to the table for destination, and a JOIN to the view for origin.

At the moment it seems to work fine, since we have two different names for each column, and we do not need to use aliases, which is where Datatables does not seem to work well in the sort and filter.

Greetings!

score 0 · Answer 2

To order and filter in datatable you can grab it on the global configuration of the table.

seacrh: true,
sort: true,
columns: [
    {data:"origen"},
    {data:"destino"}
]

Or you can do it with each column, depending on your needs.

columns: [
    {data:"origen", seacrh: true, sort: true },
    {data:"destino", seacrh: true, sort: true}
]

In either case, default of search and sort are true.