server-side sort with php editor lib

DocNC

Hello
Im confused about sorting server side with Editor and editor php library.
I set complex views which return JSON fields such as
{"ens": "CONTACT COMMUNICATION", "nom": "SARL CONTACT COMMUNICATION"}
or
[{"id": 3483, "date": "2020-10-07", "text": "*** some text*** ", "type": "tel + mail"}, {"id": 2962, "date": "2019-06-12", "text": "envoi offre 4%", "type": "mail"}, {"id": 1457, "date": "2016-07-26", "text": "*** some text ***", "type": "téléphone"}]

And of course I would like to sort by values such as "ens" or "date" rather than by whole field.
Is there a way to do that in
->order
part of the field initialisation ?
Thanks
Michel

allan

Hi Michael,

Can you show me how you are using the Editor PHP libraries? They should do the ordering automatically if you are using server-side processing.

Allan

DocNC

Hi Allan,
thanks for your time
I use in a fairly simple way

$table = 'vclients';
$id_organisation = ($_REQUEST['id_organisation'] ?? 0);
// Table's primary key
$primaryKey = 'id_organisation';

$sql_details = [
    'type' => 'Mysql', // Database type: "Mysql", "Postgres", "Sqlserver", "Sqlite" or "Oracle"
    'user' => 'root', // Database user name
    'pass' => 'mysqlroot', // Database password
    'host' => 'localhost', // Database host
    'port' => '', // Database connection port (can be left empty for default)
    'db' => 'CCC3', // Database name
    'dsn' => 'charset=utf8mb4', // PHP DSN extra information. Set as `charset=utf8mb4` if you are using MySQL
    'pdoAttr' => [], // PHP PDO attributes array. See the PHP documentation for all options
];

// DataTables PHP library
include '../../Editor/lib/DataTables.php';
include '../../lib/Formatters.php';

// Alias Editor classes so they are easy to use
use
    DataTables;
use DataTables\Editor;
use DataTables\Editor\Join;
use DataTables\Editor\Field;
use DataTables\Editor\Format;
use DataTables\Editor\Mjoin;
use DataTables\Editor\Options;
use DataTables\Editor\Upload;
use DataTables\Editor\Validate;
use DataTables\Editor\ValidateOptions;
use    DataTables\Editor\SearchPaneOptions;

// Build our Editor instance and process the data coming from _POST
// $db is build by Datatables.php using $sqldetails
Editor::inst($db, $table, $primaryKey)
    ->fields(
        Field::inst( 'id_organisation' )
            
            ,
Field::inst( 'nom' )
        ->getFormatter('format_Nom')
        ->setFormatter('format_Nom')
            
            ,
Field::inst( 'g_contact' )
        ->getFormatter('format_Contact')
        ->setFormatter('format_Contact')
            
            ,
Field::inst( 'date_creation' )
            ->validator( Validate::dateFormat( 'Y-m-d' ) )
            ->getFormatter( dateSqlToFormat( 'Y-m-d' ) )
            ->setFormatter( dateFormatToSql('Y-m-d' ) )
            
            ,
Field::inst( 'g_approche' )
        ->getFormatter('format_Approche')
        ->setFormatter('format_Approche')
            
            ,
Field::inst( 'descriptif' )
            
            
    )
    ->where(
        function ($q) use ($id_organisation) {
            if ($id_organisation > 0) {
                $q->where('id_organisation', $id_organisation);
            }
        }
    )
    ->process($_POST)
    ->json();

But when my data is complex like

[{"id": 3483, "date": "2020-10-07", "text": "*** some text*** ", "type": "tel + mail"}, {"id": 2962, "date": "2019-06-12", "text": "envoi offre 4%", "type": "mail"}, {"id": 1457, "date": "2016-07-26", "text": "*** some text ***", "type": "téléphone"}]

the automatic sorting is irrelevant.
I should sort by something like

ORDER BY JSON_EXTRACT(g_approche,"$[0],date")

How should I configure the ->order item to implement this ?
Thanks
Michel

allan

Hi Michel,

Thanks for the explanation. I'm afraid there is no option for that in Editor at this time. The ordering is always a simple ORDER BY {columnName} {asc|desc}.

It would require a custom implementation of server-side processing if that is what you require I'm afraid.

Allan

DocNC

thanks Allan I will do that.