Modify

Opened 6 years ago

Closed 4 years ago

Last modified 15 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 5 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 15 months ago by hasienda

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

Add Comment

Modify Ticket

Action
as closed .
as The resolution will be set. Next status will be 'closed'.
to The owner will be changed from rjollos. Next status will be 'closed'.
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.