“...I've been working since 2008 with Ruby / Ruby on Rails, love a bit of Elixir / Phoenix and learning Rust. I also poke through other people's code and make PRs for OpenSource Ruby projects that sometimes make it. Currently working for InPay who are based in Denmark...”

Rob Lacey
Senior Software Engineer, UK

Rails 4, ActiveRecord::Base, MySQL and DISTINCT

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.