Re: Taking "Active Tickets, Mine First" to the Next Level

Ok, now you must work in two layers: data layer and presentation layer.

 

In the first one you retrieve data in this fashion (with your example):

 

Cat.   ;           ;    Ticket

 

My Tickets    ;    ticket 1

My Tickets    ;    ticket 2

My Tickets    ;    ticket 3

Active Tickets   ticket 4

 

Then in the presentation layer you show this as you want.

 

If you’ve problems w/SQL, the MySQL help site is very comprensible.

 

You can hack the TRAC reports queries to learn. I send here the query for “MyTickets̶1; report ( in TRAC):

SELECT p.value AS __color__,

   (CASE status WHEN 'assigned' THEN 'Assigned' ELSE 'Owned' END) AS __group__,

   id AS ticket, summary, component, version, milestone,

   t.type AS type, priority, 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 t.status IN ('new', 'assigned', 'reopened') AND owner = $USER

  ORDER BY (status = 'assigned') DESC, p.value, milestone, t.type, time

 

This select retrieve data in the data layer and sends it to presentation layer. Well, I hope this be useful.

 

 

I'm using MySQL. With the front-end app I'm using to view the data in the database I do see Views, Stored Procs, and Functions. Never used them and don't know what they are. I know this probably isn't too hard to do, it's just something new to me.

 

Chris

 

From: trac-usersgooglegroups.com [mailto:trac-usersgooglegroups.com] On Behalf Of De La Camara Juan Matias
Sent: Thursday, August 16, 2007 5:18 PM
To: trac-usersgooglegroups.com
Subject: [Trac] Re: Taking "Active Tickets, Mine First" to the Next Level

I think the solution depends on what kind of DBMS you are using.

A Basic fashion to solve this could be an GROUP sentence in your SQL query. So you can group the results and then show them by group (in the presentation layer, of course).

The other solutions depends on the fact if your DBMS has views or stored procedures.

So, what DBMS do you have, Chris?

Regards.

Juan Matias

(you can hack the TRAC reports, they have SQL queries to retrieve the data in this fashion)

 

 

 

<BR

I think this kind of stuff is up to the presentation layer. The query is just a query, it has not intelligence. Instead of complex query you could use a stored proc, view or presentation layer intelligence.

 

You can solve this problem using the UNION statement in this way:

 

Select summary, othercolumns where “active tickets221;

UNION

Select “221;, othercolumns where “My tickets221;

 

But I think it make queries too complicated to read.

 

Well, I managed&nbsp;to get the output I wanted and it consisted of multiple LEFT OUTER JOIN statements. My question now is are you able to show/hide certain columns in the output? For instance, with the default Active Tickets, Mine First can you not show the summary column under My Tickets but show it under Active Tickets?

 

Chris

 

From: trac-usersgooglegroups.com [mailto:trac-usersgooglegroups.com] On Behalf Of De La Camara Juan Matias
Sent: Friday, August 17, 2007 8:50 AM
To: trac-usersgooglegroups.com
Subject: [Trac] Re: Taking &quot;Active Tickets, Mine First"; to the Next Level

Ok, now you must work in two layers: data layer and presentation layer.

 

In the first one you retrieve data in this fashion (with your example):

 

Cat.   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp;  Ticket

 

My Tickets&nbsp; &nbsp; &nbsp;   ticket 1

My Tickets &nbsp; &nbsp; &nbsp;  ticket 2

My Tickets &nbsp; &nbsp; &nbsp;  ticket 3

Active Tickets&nbsp;  ticket 4

 

Then in the presentation layer you show this as you want.

 

If you’ve problems w/SQL, the MySQL help site is very comprensible.

 

You can hack the TRAC reports queries to learn. I send here the query for “MyTickets̶1; report ( in TRAC):

SELECT p.value AS __color__,

 &nbsp; (CASE status WHEN 'assigned' THEN 'Assigned' ELSE 'Owned' END) AS __group__,

 &nbsp; id AS ticket, summary, component, version, milestone,

 &nbsp; t.type AS type, priority, time AS created,

 &nbsp; changetime AS _changetime, description AS _description,

 &nbsp; reporter AS _reporter

  FROM ticket t

  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'

  WHERE t.status IN ('new', 'assigned', 'reopened') AND owner = $USER

  ORDER BY (status = 'assigned') DESC, p.value, milestone, t.type, time

 

This select retrieve data in the data layer and sends it to presentation layer. Well, I hope this be useful.

 

 

I'm using MySQL.&nbsp;With the front-end app I'm using to view the data in the database I do see Views, Stored Procs, and Functions. Never used them and don't know what they are. I know this probably isn't too hard to do, it's just something new to me.

 

Chris

 

From: trac-usersgooglegroups.com [mailto:trac-usersgooglegroups.com] On Behalf Of De La Camara Juan Matias
Sent: Thursday, August 16, 2007 5:18 PM
To: trac-usersgooglegroups.com
Subject: [Trac] Re: Taking &quot;Active Tickets, Mine First"; to the Next Level

I think the solution depends on what kind of DBMS you are using.

A Basic fashion to solve this could be an GROUP sentence in your SQL query. So you can group the results and then show them by group (in the presentation layer, of course).

The other solutions depends on the fact if your DBMS has views or stored procedures.

So, what DBMS do you have, Chris?

Regards.

Juan Matias

(you can hack the TRAC reports, they have SQL queries to retrieve the data in this fashion)

 

 

 

<BR

<BR