Sample report to sort tickets by the number of votes

[anonymous]

Provide some sample SQL report which allows to sort the tickets according to the number of votes, also displaying the number of votes.

I don't know the database fields which are introduced by the voteplugin ...

[andrew.vanpernis@…]

Here's a basic SQL query that should return all the tickets that have been voted on sorted by the total number of votes. I'm not a SQL expert, so perhaps this can be improved or simplified.

SELECT  p.value AS __color__,
   id AS ticket, summary, component, version, milestone, t.type AS type, 
   owner, status,
   SUM (v.vote) AS votes,
   changetime AS _changetime,
   description AS _description,
   reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  LEFT OUTER JOIN votes v ON (v.resource = 'ticket/'||t.id)
  WHERE status <> 'closed' AND v.vote IS NOT NULL
  GROUP BY id 
  ORDER BY votes DESC, p.value, time

[mauzer_tim]

I'd vote for this ticket. BTW the SQL query above doesn't work. When I save it trac displays: ERROR: column "p.value" must appear in the GROUP BY clause or be used in an aggregate function

[avanpernis]

Replying to mauzer_tim:

I'd vote for this ticket. BTW the SQL query above doesn't work. When I save it trac displays: ERROR: column "p.value" must appear in the GROUP BY clause or be used in an aggregate function

I wrote the query for

Trac	0.11.2.1
Python	2.5.2
SQLite	3.3.4

It works for me, so maybe it's a configuration difference.

[anonymous]

Here's the query I used with PostgreSQL:

SELECT p.value AS __color__,
   id AS ticket, summary, component, version, milestone, t.type AS type, 
   owner, status,
   votes,
   changetime AS _changetime,
   description AS _description,
   reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  join (select resource, sum(vote) as votes from votes group by resource) as v
     ON (v.resource = 'ticket/'||t.id)
  WHERE status <> 'closed'
  ORDER BY votes DESC, p.value, time

[rjollos]

Reassigning ticket to new maintainer.

[rjollos]

This report exists on t.e.o: ​t:report:34

[rjollos]

(In [8473]) Added SQL query for a report that sorts tickets by the number of votes. SQL is taken from the t.e.o. site. Fixes #3655.

[rjollos]

#4364 is a duplicate.

[hasienda]

(In [13080]) VotePlugin: Update sample SQL reports following changes in [13079], refs #3655.