SQL Query

michelek

Hi,

Where I have to insert SQL command GROUP BY

I have try this but does not work:

Editor::inst( $db, 'persone', GROUP BY DATE_FORMAT(start_date, "%Y%m%d") )
  ->field( 
  Field::inst( 'persone.id' )
)

michelek

Where I have to insert GROUP BY DATE_FORMAT(start_date, "%Y%m%d") ?

michelek

Simply I need this but does not work:

$data = $db->sql( "
SELECT id, start_date, lastname
FROM persone GROUP BY DATE_FORMAT(start_date, '%Y%m%d')
" )->fetchAll();

->process($_POST)
    ->json();

zess

I hope this is helpful for you:

Field::inst( 'adressen.nachname' )
         ->options( function () use ( $db ) {
            // https://datatables.net/forums/discussion/25280/how-to-create-a-multiple-where-using-php-editor-1-4-0
            $userList = $db->sql( 'SELECT 
                  adressen.id, 
                  CONCAT(anrede.anrede," ",adressen.vorname," ", adressen.nachname) AS Name 
               FROM adressen
               INNER JOIN anrede
                  ON anrede.id = adressen.anrede
               WHERE adressen.anstellung = "Ang"
                  AND adressen.in_aktiv = 1
               ORDER BY adressen.nachname');
            $out = array();

            while ( $row = $userList->fetch() ) {
               $out[] = array(
                  "value" => $row['id'],
                  "label" => $row['Name']
               );
            }

            return $out;
         } ),

allan

Hi,

We discussed this by e-mail (OP): Unfortunately there is no "group by" option in Editor at the moment. If that is something that is required, a custom SQL statement would need to be used - but Editor wouldn't be able to directly use it to edit the database since it needs to "understand" the SQL (which it currently doesn't for a GROUP BY). This is something I plan to add in future, but it isn't available yet. Sorry.

Allan