Issues with Editor Options Instance and order by

rf1234

This Options Instance

Field::inst( 'report.report_type_id' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) )
      ->options( Options::inst()
          ->table('report_type')
          ->value('id')
          ->label('label')
          ->order('number asc')
           //where clause MUST be a closure function in Options!!!
           ->where( function($q) {
               $q ->where( 'user_id', $_SESSION['id'] );
           } )
       ),

generates this SQL Statement:

...
SELECT DISTINCT  `id` as 'id', `label` as 'label' FROM  `report_type` WHERE (`user_id` = 64 ) ORDER BY `number`  asc

This causes an error in some DBMS's because the ORDER BY field "number" is not in the SELECT field list. In my case it works fine locally with XAMPP and MariaDB but does not work on the server side with MySQL which is particularly annoying because you think everyhting is fine when testing locally. For the time being I found this work around.

Field::inst( 'report.report_type_id' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) )
      ->options( Options::inst()
          ->table('report_type')
          ->value('id')
          ->label( array('label', 'number') )  //we need to get number into the field list otherwise SQL error with the server MySQL-Version. 
          ->render( function ( $row ) { return $row['label']; } )
           ->order('number asc')
           //where clause MUST be a closure function in Options!!!
           ->where( function($q) {
               $q ->where( 'user_id', $_SESSION['id'] );
           } )
       ),

This forces Editor to put "number" in the SELECT field list even though I don't need the field returned to the client.
@Allan: could you make a change in Editor that it always puts all ORDER BY fields in the SELECT field list please. This would be very helpful. Many thanks.

allan

I'm surprised that query would cause an issue. What version of MySQL are you using?

I've just tried this locally (MySQL 5.7.22) and it worked without any issues:

select first_name from users order by last_name;

Allan

rf1234

I also run MySQL 5.7.22.

This works

select firstname from user order by lastname

This doesn't work:

select distinct firstname from user order by lastname

So it is the "distinct" that causes the error in MySQL 5.7.22

Error Code: 3065. Expression #1 of ORDER BY clause is not in SELECT list, references column 'lgffin.user.lastname' which is not in SELECT list; this is incompatible with DISTINCT

Both statements work with MariaDB version 10.1.13

allan

Got it now - thanks. I'll get that sorted out for the next release!

Allan