Rails 4, ActiveRecord::Base, MySQL and DISTINCT

06 Jan 2017 12:04

Interesting, ActiveRecord joins issues today. Recently upgraded to Rails 4.0 and working on clearing odd occasional bugs.

Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT grapes.id, grapes.* FROM `grapes` INNER JOIN `tickets` ON `z' at line 1: SELECT `pledges`.`id` AS t0_r0, ...., DISTINCT grapes.id, grapes.* FROM `grapes` INNER JOIN `tickets` ....

We specifically need a DISTINCT in here so that we don’t end up with duplicate rows. However, overwriting the select for *eager_load*ed statements isn’t going to work, in fact it appears to just append our select causing the above error. So…

-        scope = scope.joins(:tickets).select('DISTINCT grapes.id, grapes.*')
+        scope = scope.joins(:tickets).distinct('grapes.id')

Don’t use .select(‘DISTINCT … when eager_loading is likely to kick in. You’ll end up with something like.

SELECT `grapes`.`id` AS t0_r0, ..... DISTINCT(grapes.id), grapes.* FROM grapes;

Which will break, since you can’t have two DISTINCT in a SELECT.