drupal view how to find top one in all category

Question

i have a content type event with following fields date,type and using fivestar module for voting. The type takes 3 possible values 'art', 'entertainment', 'iq'. i try to generate a block that should display top event (by votes) in each category. any one have idea ??

Answer 1

You should be able to do this relatively easy in a custom module, I have a hard time seeing how you would do this in views with the UI.

You need a query that looks something like this

SELECT nid FROM {voting_api} AS v
LEFT JOIN {content_content_type} AS c on v.content_id = c.nid
WHERE c.field_name = 'art'
AND v.function = 'count'
AND c.content_type = 'node'
ORDER BY v.value
LIMIT 1;

You need to run a query for each value, art, entertainment and iq. If you want to make it more reliable, you should use content_fields() and content_database_info() to get the table name and column name of your CCK field (which can change over time).