Lighthouse has a new layout. Prefer the old one? Return to the old layout, and switch back any time from the link at the top of each page.

This project is archived and is in readonly mode.

Query broken in Rails 3 (?)

#6679

This used to work for me in latest Rails 2.3, but it's broken on Rails 3.

@opinion_polls = @organizer.opinion_polls.all(
  :select => "DISTINCT(opinion_polls.event_id), opinion_polls.name, opinion_polls.id,  opinion_polls.random_key", 
  :joins => :opinion_poll_recipients, 
  :conditions => ["opinion_poll_recipients.preview = false AND opinion_poll_recipients.last_sent_at > ? ", 7.days.ago.beginning_of_day])

Query generated in Rails 2.3:

SELECT DISTINCT(opinion_polls.event_id), opinion_polls.name, opinion_polls.id, opinion_polls.random_key FROM `opinion_polls` INNER JOIN `events` ON `opinion_polls`.event_id = `events`.id INNER JOIN `opinion_poll_recipients` ON opinion_poll_recipients.opinion_poll_id = opinion_polls.id WHERE (opinion_poll_recipients.preview = false AND opinion_poll_recipients.last_sent_at > '2011-03-30 00:00:00' ) AND ((`events`.organizer_id = 2766))

Query generated in Rails 3:

SELECT `opinion_polls`.*, DISTINCT(opinion_polls.event_id), opinion_polls.name, opinion_polls.id, opinion_polls.random_key FROM `opinion_polls` INNER JOIN `opinion_poll_recipients` ON `opinion_poll_recipients`.`opinion_poll_id` = `opinion_polls`.`id` INNER JOIN `events` ON `opinion_polls`.event_id = `events`.id WHERE ((`events`.organizer_id = 6)) AND (opinion_poll_recipients.preview = false AND opinion_poll_recipients.last_sent_at > '2011-03-30 03:00:00' )

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(opinion_polls.event_id), opinion_polls.name, opinion_polls.id,  opinion' at line 1: SELECT `opinion_polls`.*, DISTINCT(opinion_polls.event_id), opinion_polls.name, opinion_polls.id,  opinion_polls.random_key FROM `opinion_polls` INNER JOIN `opinion_poll_recipients` ON `opinion_poll_recipients`.`opinion_poll_id` = `opinion_polls`.`id` INNER JOIN `events` ON `opinion_polls`.event_id = `events`.id WHERE ((`events`.organizer_id = 6)) AND (opinion_poll_recipients.preview = false AND opinion_poll_recipients.last_sent_at > '2011-03-30 03:00:00' )

The error is because we are asking a DISTINCT field after selecting every field in the table (opinion_polls.*).

Reported by Emilio Tagua · April 6th, 2011 @ 03:25 PM

State: new
Milestone: 3.x
Assigned to: Aaron Patterson Aaron Patterson
Importance: Low

Activity

  1. Aaron Patterson
    Aaron Patterson
    • Assigned user set to Aaron Patterson

    April 6th, 2011 @ 05:01 PM