Issues with Editor Options Instance and order by
Issues with Editor Options Instance and order by
rf1234
Posts: 2,992Questions: 87Answers: 421
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.
This discussion has been closed.
Replies
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:
Allan
I also run MySQL 5.7.22.
This works
This doesn't work:
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
Got it now - thanks. I'll get that sorted out for the next release!
Allan