-- ## 16: Popular Tickets by user's rating ## --

-- Tickets ordered by rating (<> 0)
-- 
-- You must be [/register registered] to increase or decrease rating.

SELECT v.ticket_votes as rating, t.id, t.summary, t.status, t.type, t.priority, t.milestone, t.component
FROM ticket t,
    (SELECT CAST ( substring (resource from 8) as int8) as ticket_id,
            sum(vote) as ticket_votes
     FROM votes WHERE resource LIKE 'ticket/%' GROUP BY resource) as v
WHERE 
     t.id = v.ticket_id
 AND t.status IN ('accepted','assigned', 'new', 'reopened', 'testing')
ORDER BY v.ticket_votes DESC , t.id