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 tickets" UNION Select "", othercolumns where "My tickets" But I think it make queries too complicated to read. ________________________________ De: [email protected] [mailto:[EMAIL PROTECTED] En nombre de Wetterman, Christopher Enviado el: Miércoles, 22 de Agosto de 2007 01:50 p.m. Para: [email protected] Asunto: [Trac] Re: Taking "Active Tickets, Mine First" to the Next Level Well, I managed 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: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of De La Camara Juan Matias Sent: Friday, August 17, 2007 8:50 AM To: [email protected] Subject: [Trac] 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" report ( {7} 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. ________________________________ De: [email protected] [mailto:[EMAIL PROTECTED] En nombre de Wetterman, Christopher Enviado el: Jueves, 16 de Agosto de 2007 07:19 p.m. Para: [email protected] Asunto: [Trac] Re: Taking "Active Tickets, Mine First" to the Next Level 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: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of De La Camara Juan Matias Sent: Thursday, August 16, 2007 5:18 PM To: [email protected] 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) ________________________________ De: [email protected] [mailto:[EMAIL PROTECTED] En nombre de Wetterman, Christopher Enviado el: Jueves, 16 de Agosto de 2007 02:04 p.m. Para: [email protected] Asunto: [Trac] Taking "Active Tickets, Mine First" to the Next Level I like the separation of the tickets you own and the remaining ones out there. With that I would like a further break down separating out the components under Active Tickets, but alas my python SQL knowledge isn't up to par. Does anyone know how the SQL query would be formatted for this? For example, lets say when creating a ticket you have the option to choose "GUI", "Component 1", "Component 2" and "Component 3" from the components drop down. Now when you view the report it'll be organized something like this. I also have custom fields on each ticket that I would like to include as well. - My Tickets ticket 1 ticket 2 ticket 3 -Active Tickets -- GUI ticket 4 ticket 5 -- Component 1 ticket 6 -- Component 2 ticket 7 ticket 8 ticket 9 ticket 10 -- Component 3 ticket 11 In addition, if there are no tickets for a particular component can you add a row under that header saying "No bugs reported."? Thanks. Chris <BR <BR --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Trac Users" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/trac-users?hl=en -~----------~----~----~----~------~----~------~--~---
