sql - 如何在 trac 报告中显示截止日期列？

Question

我有trac安装用于管理我的在线项目。我还安装了一个日期插件 DateFieldPlugin在里面。我无法在自定义报告中显示此归档日期。

以下代码生成正常报告。

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

我想要这样的东西:

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

上面的代码向我显示了截止日期列，尽管它多次显示一个任务行。

有人可以帮我吗？

Answer 1

Derek 已经不在了。然而，这是工作的:

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

只有您需要替换 c.name 值以匹配您的日期字段名称，如果它不是我配置中的“due_date”。

背景:'ticket_custom' 表定义每行只有一个键值对，正如对模式的调用所揭示的那样:

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

请注意主键不仅是票号，还是票号(编号)和(自定义列)名称的组合。因此，在 JOIN 上需要一个额外的 WHERE 子句，否则您仍然会得到多行 - 所有匹配票证的每个自定义字段都有一个。这与主表“票”形成对比，其中一张票的所有信息都在一行中。