Group by in Editor and search

MaxValembois

Hi all,
I am trying to use the method from this topic (https://datatables.net/forums/discussion/61256#Comment_168732) to get a "group by" function in one of my datatable.
It works but the created column is sorted according to the original field ( id) not to the formated one.
Any idea how to then make the sorting to work ?
thanks in advance

my serveur side script (with the specific field highlited in the middle) :

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

$inclureAdherentsRadies= managerOptionsGenerales::getValeurNum(53) ;
$editor=Editor::inst($db,$_SESSION['table_prefix'] .'usagers as us')->fields(
                Field::inst('us.id','id' ),
                Field::inst('us.id_adherent','id_adherent'),
                Field::inst('us.nom','nom'),
                Field::inst('us.prenom','prenom'),
                Field::inst('us.date_naissance','date_naissance'),
                Field::inst('FLOOR(DATEDIFF( DATE( NOW()) , us.date_naissance  ) / 365.25)', 'age'),
                Field::inst('CONCAT("U-",RIGHT(CONCAT ("000000",us.id),6))', 'codeBarreUsager'),
                Field::inst('if(us.sexe=1,"masculin", if(2=2,"feminin",""))','sexe'),
                Field::inst('us.sexe','sexeEdit'),
                Field::inst('us.date_interdiction_emprunt','date_interdiction_emprunt'),
                Field::inst('us.interdiction_emprunt','interdiction_emprunt'),
                Field::inst('us.notes_freq','notes_freq'),
                Field::inst('us.date_suppression','date_suppression'),
                Field::inst('us.option1','option1'),
                Field::inst('us.option2','option2'),
                Field::inst('us.option3','option3'),
                Field::inst('us.option4','option4'),
                Field::inst('us.option5','option5'),
                Field::inst('ad.nom', 'adherent_nom'),
                Field::inst('if(ad.nom!="Adhérent oublié",if(ad.date_radiation< NOW(),"radié" , "actif" ),"oublié")', 'statutAdherent')

// the field we talk about is here when sorting, it sort by us.id -----------------------------------

              , Field::inst( 'us.id','prets' )
                    ->getFormatter( function($val, $data, $opts) use ( $db ) {
                        $stmt = ('SELECT count(id) as emprunts
                                    FROM '.$_SESSION['table_prefix'].'prets
                                     WHERE id_us = :id AND date_retour="0000-00-00"'); 
                        $result = $db ->raw()
                                      ->bind(':id', $val)
                                      ->exec($stmt);
                        $row = $result->fetch(PDO::FETCH_ASSOC);
                        if ( (bool)$row ) {
                            return $row["emprunts"];
                        }
                        return 0;
                    })```

// end of the issue --------------------------------------------

                , Field::inst( 'us.id','retard_pret' )
                    ->getFormatter( function($val, $data, $opts) use ( $db ) {
                        $stmt = ('SELECT if(SUM(if(date_retour_prevu<NOW(),1,0))>0,"en retard","") as retard_pret
                                    FROM '.$_SESSION['table_prefix'].'prets
                                     WHERE id_us = :id AND date_retour="0000-00-00"'); 
                        $result = $db ->raw()
                                      ->bind(':id', $val)
                                      ->exec($stmt);
                        $row = $result->fetch(PDO::FETCH_ASSOC);
                        if ( (bool)$row ) {
                            return $row["retard_pret"];
                        }
                        return 0;
                    })
                , Field::inst( 'us.id','frequentations' )
                    ->getFormatter( function($val, $data, $opts) use ( $db ) {
                        $stmt = ('SELECT sum(nombre) as frequentations
                                    FROM '.$_SESSION['table_prefix'].'frequentation
                                     WHERE id_usager = :id'); 
                        $result = $db ->raw()
                                      ->bind(':id', $val)
                                      ->exec($stmt);
                        $row = $result->fetch(PDO::FETCH_ASSOC);
                        if ( (bool)$row ) {
                            return $row["frequentations"];
                        }
                        return 0;
                    })

        )
        ->leftJoin( $_SESSION['table_prefix'] .'adherents as ad',   'ad.id',   '=', 'us.id_adherent' );






        $i=0;
       foreach ($optionsPersonnalisees as $optPersonalisee) {
                            if ($optPersonalisee['affichage'] == 1) {
                                $nomoptiontmp=$optPersonalisee['nom'];
                                $nomoptiontmp= str_replace('é', 'e', $nomoptiontmp);
                                $nomoptiontmp = str_replace('è', 'e', $nomoptiontmp);
                                $nomoptiontmp = str_replace('ê', 'e', $nomoptiontmp);
                                $nomoptiontmp2= 'option_'.$nomoptiontmp;

                                $editor->leftJoin($_SESSION['table_prefix'].'view_options as opt'.$i, 'us.id', '=', 'opt'.$i.'.id_associe AND opt'.$i.'.type="usager" AND opt'.$i.'.nom="'.$nomoptiontmp.'" AND opt'.$i.'.valeur !=""')
                                  ->Fields(Field::inst('opt'.$i.'.valeur',$nomoptiontmp2));
                            }
                            $i++;
                        }



      $editor->where( function ( $q ) use ( $inclureAdherentsRadies ) {
                $q->and_where(function($r)  use ( $inclureAdherentsRadies ){
                    $r->where('us.nom', 'Oublié', '<>' );
                    $r->where('us.date_suppression', '0000-00-00', '=' );
                    $r->where('ad.date_radiation', 'DATE_SUB(CURDATE(), INTERVAL ' . $inclureAdherentsRadies . ' DAY)', '>', false );
            } );
      })
        ->process($_POST)
        ->json();

allan

The only way to make GROUP BY work with Editor is through an SQL VIEW.

You really don't want to execute SQL queries inside a get formatter. That would be executed for every single row in the table. It looks like you've got at least three get formatters doing queries, so if you have 100 rows, that is 300 extra queries to the DB just to load the page.

A VIEW which gives you the data in the format you want will be massively faster.

Allan

MaxValembois

Thanks Allan for the Answer, looks like I will make a view.
I thought view were a no go in term of efficiency but you convinced me

allan

I think it purely depends upon the VIEW and what it is doing (hundreds of sub-queries for example would be bad). It certainly won't be less efficient than hitting the db with 300 individually compiled and executed queries though.

Allan