I'm trying to query for events with a unique title and reviews with ratings higher than 1. I would like to return the earliest event with each unique title and I only would like to return a limited number of results. I constructed the query below, but it appears to yield erroneous results and I think it may also be inefficient. This happens when the :include refers to a has_many relationship. It doesn't happen on has_one relationships.
I'm running Rails 1.2.3 with MySQL 5.0.37 and Ruby 1.8.6 on OS X 10.4.9
When I run this query I get the expected results
Event.find(:all, :include=>:reviews, :conditions=>['reviews.rating > ?', 1], :group=>'events.title', :order=>'events.created_at ASC')
It generates the expected SQL:
SELECT events.`id` AS t0_r0, events.`title` AS t0_r1, ...
FROM events LEFT OUTER JOIN reviews ON reviews.event_id = events.id WHERE (reviews.rating > 1) GROUP BY events.title ORDER BY events.created_at ASC
However, when I add the :limit parameter, another query is run first that counts the number of records. This counting query ignores the :group clause and returns the wrong event ids (ie will give me duplicate titles)
Event.find(:all, :include=>:reviews, :group=>'events.title', :order=>'events.created_at ASC', :conditions=>[reviews.rating > ?', 3], :limit=>4)
SELECT DISTINCT events.id FROM events LEFT OUTER JOIN reviews ON reviews.event_id = events.id
WHERE (reviews.rating > 1) ORDER BY events.created_at ASC LIMIT 4
SELECT events.`id` AS t0_r0, events.`title` AS t0_r1, ...
FROM events LEFT OUTER JOIN reviews ON reviews.event_id = events.id
WHERE (reviews.rating > 1) AND events.id IN ('197', '564', '592', '617')
GROUP BY events.title
ORDER BY events.created_at ASC
I would expect it to generate only one query like
SELECT events.`id` AS t0_r0, events.`title` AS t0_r1, ...
FROM events LEFT OUTER JOIN reviews ON reviews.event_id = events.id
WHERE (reviews.rating > 1) AND events.id IN ('197', '564', '592', '617')
GROUP BY events.title
ORDER BY events.created_at ASC
LIMIT 4
Or, if two queries are required for some reason, I would expect the query below for the first and for the second a simple query on IDs without the WHERE and GROUP criteria again. I'm not sure whether the ORDER BY or the LEFT OUTER JOIN are required on the second query either.
SELECT DISTINCT events.title, events.id FROM events LEFT OUTER JOIN reviews ON reviews.event_id = events.id
WHERE (reviews.rating > 1) GROUP BY events.title ORDER BY events.created_at ASC LIMIT 4
SELECT events.`id` AS t0_r0, events.`title` AS t0_r1, ...
FROM events LEFT OUTER JOIN reviews ON reviews.event_id = events.id
WHERE events.id IN ('197', '564', '592', '617')
ORDER BY events.created_at ASC
