Adding a project column to custom reports

[endquote]

I wanted to modify some of the default reports such as "my tickets" to include a project column. To do that I simply added an additional select:

(
  SELECT smp_project.name
  FROM smp_project, smp_component_project, smp_milestone_project
  WHERE (t.milestone = smp_milestone_project.milestone AND smp_milestone_project.id_project = smp_project.id_project)
  OR (t.component = smp_component_project.component AND smp_component_project.id_project = smp_project.id_project)
  LIMIT 1
) AS project

This works, but it's probably not very efficient. But there is another problem...

I then created a new project, but didn't create any components or milestones for it. Then created a ticket for that project. Of course the above selection doesn't return anything in that case. However if I do a custom query with project is myproject, the ticket comes up correctly.

So my question is how a project gets associated with a ticket, if not via the component/milestone tables.

[endquote]

It would also be good to filter out tickets assigned to closed projects.

[anonymous]

why not using the query language? It will look like this:

query:?owner=%24USER
&
status=accepted
&
status=assigned
&
status=needs_work
&
status=new
&
status=reopened
&
status=testing
&
group=userfinish
&
col=id
&
col=summary
&
col=status
&
col=type
&
col=priority
&
col=component
&
col=project
&
col=remaininghours
&
col=userfinish
&
report=34
&
order=priority

[anonymous]

Replying to endquote:

So my question is how a project gets associated with a ticket, if not via the component/milestone tables.

The whole stuff with custom ticket-fields is stored in db table ticket_custom with a (ticket,name,value)-record, and that column 'name' in the middle is the ticket field name. All changes to fields are logged in db table ticket_change with a (ticket,time,author,field,oldvalue,newvalue)-record.

In your case, an SQL query must select all records from ticket_custom where name=='project' and value=='myproject', which results in a list of ticket IDs you can use to look up the db table ticket to get their details.

[endquote]

Thanks, I didn't know about the ticket_custom table. Adding this to my query gets what I need.

(SELECT value FROM ticket_custom WHERE ticket = t.id AND name = 'project') AS project,