Modify

Opened 6 years ago

Closed 4 years ago

Last modified 19 months ago

#3655 closed defect (fixed)

Sample report to sort tickets by the number of votes

Reported by: anonymous Owned by: rjollos
Priority: normal Component: VotePlugin
Severity: normal Keywords:
Cc: Trac Release: 0.10

Description

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 ...

Attachments (0)

Change History (9)

comment:1 Changed 6 years ago by 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

comment:2 follow-up: Changed 6 years ago by 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

comment:3 in reply to: ↑ 2 Changed 6 years ago by 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

Trac0.11.2.1
Python2.5.2
SQLite3.3.4

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

comment:4 Changed 6 years ago by 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

comment:5 Changed 4 years ago by rjollos

  • Owner changed from athomas to rjollos

Reassigning ticket to new maintainer.

comment:6 Changed 4 years ago by rjollos

  • Summary changed from Provide sample report (SQL) to Sample report to sort tickets by the number of votes

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

comment:7 Changed 4 years ago by rjollos

  • Resolution set to fixed
  • Status changed from new to closed

(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.

comment:8 Changed 4 years ago by rjollos

#4364 is a duplicate.

comment:9 Changed 19 months ago by hasienda

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

Add Comment

Modify Ticket

Action
as closed The owner will remain rjollos.
The resolution will be deleted. Next status will be 'reopened'.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.