Rails - search records by group
I would like to construct a search query that groups the records by their 'tag' attribute, and finds a certain number of records for each tag group. My 'place' table is as follows:
| ID | name | geom | rating_av | tag |
There are four different tags that a place can be assigned - "city", "food", "hotel" and "attraction". I would like my search to be able to return the top five records for each tag group, ordered by rating_av
, with an additional geometric constraint. I can query each tag at a time with the following (which seems to work fine):
Place.find(:all, :limit => 5, :conditions => ["geom && ? and tag = ?", Polygon.from_coordinates([[[xMinLng, yMinLat], [xMinLng, yMaxLat], [xMaxLng, yMaxLat], [xMaxLng, yMinLat], [xMinLng, yMinLat]]], 4326), "food"])
However, I'd like to be able to retrieve the first five records for each tag in a single query. What is the best way to construct such a query? Should I think about adding the 'tag' column as an index to aid searching? I've tried adding :group => 'tag'
to 开发者_Python百科the query, but I'm not too sure how to use it correctly, as I got the following error back:
ActiveRecord::StatementInvalid (PGError: ERROR: column "places.id" must appear in the GROUP BY clause or be used in an aggregate function
Any help is greatly appreciated, thanks!
Place.all(
:joins => "INNER JOIN (
SELECT a.id, COUNT(*) AS ranknum
FROM places AS a
INNER JOIN places AS b ON (a.tag = b.tag) AND (a.rating_av <= b.rating_av)
GROUP BY a.id
HAVING COUNT(*) <= 5
) AS d ON (places.id = d.id)",
:order => "places.tag, d.ranknum"
)
For a detailed explanation of this query look at this article.
精彩评论