2

I have trac installed for managing my online projects. I have also installed a date plugin DateFieldPlugin in it. I am unable to show this date filed in the custom reports.

The following code generates the normal report.

SELECT p.value AS __color__,
owner AS __group__,
id AS ticket, severity, summary, component, milestone, t.type AS type, time AS created,
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'
WHERE status = 'assigned'
ORDER BY owner, p.value, t.type, time

and I want something like this:

SELECT p.value AS __color__,
owner AS __group__,
id AS ticket, severity, summary, component, milestone, t.type AS type, time AS created,
changetime AS _changetime, description AS _description,
reporter AS _reporter, c.value AS Duedate
FROM ticket t
LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority', ticket_custom c
WHERE status = 'assigned'
ORDER BY owner, p.value, t.type, time

The above code is showing me the due date column though it is showing one task row as multiple times.

Can anybody help me in this?

Joe White
  • 94,807
  • 60
  • 220
  • 330
SachinKRaj
  • 183
  • 2
  • 18

2 Answers2

7

Derek is quit there. Nevertheless, here is the working one:

SELECT p.value AS __color__,
owner AS __group__,
id AS ticket, severity, summary, component, milestone, t.type AS type, time AS created,
changetime AS _changetime, description AS _description,
reporter AS _reporter, c.value AS Duedate
FROM ticket t
LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
LEFT OUTER JOIN ticket_custom c ON t.id = c.ticket AND c.name = 'due_date'
WHERE status = 'assigned'
ORDER BY owner, p.value, t.type, time

Only you will have to replace the c.name value to match with your date fields name, if it's not 'due_date' like in my configuration.

Background: The 'ticket_custom' table definition is just one key-value-pair per row, as the call for the schema reveals:

sqlite> .schema ticket_custom
CREATE TABLE ticket_custom (
    ticket integer,
    name text,
    value text,
    UNIQUE (ticket,name)
);

Please note the primary key is not just the ticket number, but ticket (number) and (custom column) name combined. Hence an additional WHERE clause is required on the JOIN or you'll still get multiple rows - one for each custom field of all matching tickets. This is in contrast to the main table 'ticket', where all information for one ticket is in a single row.

hasienda
  • 2,390
  • 1
  • 13
  • 16
  • Thanks a lot. The exact code didn't work, though I just removed the AND clause (AND c.name = 'due_date') from the query and it seems work. Let me try it out in a bit different format. The table is already there for due date as ticket_custom, it was created by the plugin I installed for new custom filed (date). – SachinKRaj Aug 22 '11 at 07:29
  • Well, if the custom field name is 'date' then 'AND c.name='date' is what you'll need. As outlined above you can't just join table ticket_custom, at least with >1 custom fields per ticket. You'll need to select by name, what values should be JOINed as additional column to the ticket table - uh, just noticed my error ... corrected the query by moving the name selector where it belongs: in the JOIN part. Try again please. – hasienda Aug 23 '11 at 20:22
  • The duedate column is coming blank? – SachinKRaj Aug 25 '11 at 11:14
  • With this query, **SELECT p.value AS __color__, owner AS __group__, id AS ticket, severity, summary, component, milestone, t.type AS type, time AS created, changetime AS _changetime, description AS _description, reporter AS _reporter, date(c.value) AS duedate FROM ticket t LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority' LEFT JOIN ticket_custom c ON t.id = c.ticket WHERE status = 'assigned' ORDER BY owner, p.value, t.type, time** I am able to print the due date but its format is not as current date. How can I make current date and due date format same? – SachinKRaj Aug 25 '11 at 11:23
  • Can't do much to date/time format of your custom field, since it's a formatted string. This is a DateFieldPlugin limitation. I'm working with a patched Trac here, and save date/time like Trac (time, changetime): in POSIX microseconds. Then you could use the special column names as explained in http://trac.edgewall.org/wiki/TracReports#Automaticallyformattedcolumns (or same page in your own Trac wiki docs). Better store true timestamps for searching as well - as soon as it is available in the official branch. – hasienda Aug 26 '11 at 19:37
  • And there is another caveat to note from studying Trac wiki docs: Use LEFT OUTER JOIN instead of LEFT JOIN, "If you have tickets in the database before you declare the extra fields". - from http://trac.edgewall.org/wiki/TracReports#Reportingoncustomfields | I changed my code above accordingly. – hasienda Aug 26 '11 at 20:00
2

You aren't specifying a join condition on ticket_custom. Therefore, it's doing a CROSS JOIN, which joins every record from ticket_custom to every record in the rest of your query. So if your ticket left join enum originally returned 100 rows, and ticket_custom has 20, you'll have 2000 resulting rows in your 2nd query.

You need to determine how ticket_custom relates to the other tables and provide an appropriate predicate. Most likely, it's something like this:

SELECT p.value AS __color__,
owner AS __group__,
id AS ticket, severity, summary, component, milestone, t.type AS type, time AS created,
changetime AS _changetime, description AS _description,
reporter AS _reporter, c.value AS Duedate
FROM ticket t
LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
LEFT JOIN ticket_custom c ON t.id = c.ticket_id
WHERE status = 'assigned'
ORDER BY owner, p.value, t.type, time

(See new LEFT JOIN line). The columns are a guess - you'll need to substitute whichever columns correctly relate the 2 tables.

Derek
  • 21,828
  • 7
  • 53
  • 61