Ordering on joined table does not work with mysql > 5.7.5

[brutus333]

Hello!

In our app we are using the ordering via joined table as instructed in https://github.com/bogdan/datagrid/wiki/Columns#ordering

Since we migrated to Mysql 5.7.x we have the following error on grids that contain ordering via joined table:

ActionView::Template::Error (Mysql2::Error: Expression #1 of ORDER BY clause is not in SELECT list, 
references column 'keystone.local_user.name' which is not in SELECT list; this is incompatible with 
DISTINCT: SELECT  DISTINCT `user`.* FROM `user` INNER JOIN `local_user` ON 
`local_user`.`user_id` = `user`.`id` INNER JOIN `assignment` ON `user`.`id` = 
`assignment`.`actor_id` WHERE `assignment`.`target_id` = '5ec2a8cadace43a4a3381c576a0cc39b' 
AND `user`.`enabled` = 1 ORDER BY local_user.name LIMIT 25 OFFSET 0):

We found an workaround by changing the sql_mode of the mysql database to exclude ONLY_FULL_GROUP_BY restriction (http://drib.tech/programming/turn-off-sql-mode-only_full_group_by-mysql-5-7) but we are interested in a future-proof solution.

The "guilty" code is:

column_name :keystone_user_link, :name, "Name", proc { |scope|
  scope.joins(:local_user).order("local_user.name") }

[bogdan]

Datagrid can not overcome the SQL limitations of your database: it is only an SQL generator, so if you would figure out how to solve your problem at the SQL level, like rewriting the query to have the same result but without specified issue, you can let Datagrid know how to generate it.

I would recommend to remove the DISTINCT from the query by avoiding the join that generates it. I believe it should come from INNER JOIN assignment. If it is true, rewrite it as a nested query instead:

SELECT  `user`.* FROM `user` INNER JOIN `local_user` ON 
`local_user`.`user_id` = `user`.`id` WHERE  
user.id in (select a.actor_id from assignment a where a.target_id =  
   '5ec2a8cadace43a4a3381c576a0cc39b')
AND `user`.`enabled` = 1 ORDER BY local_user.name LIMIT 25 OFFSET 0):