Re: [rt-users] Is it possible to do a table join in a query?
On Mon, Mar 26, 2012 at 22:04, Paul Tomblin ptomb...@xcski.com wrote: I would like to put a group name into a custom field, and then see only the tickets in a certain queue where that group is a group that I belong to. I know how to do this from pgsql: select t.* from tickets t join queues q on t.queue = q.id join objectcustomfieldvalues ocfv on objecttype = 'RT::Ticket' and objectid = t.id join customfields on customfields.id = ocfv.customfield join groups on groups.name = ocfv.content join groupmembers on groupmembers.groupid = groups.id where q.name = 'rtqueue1' and customfields.name = 'group_assigned' and groupmembers.memberid = 24 and t.disabled = 0 and q.disabled = 0 and ocfv.disabled = 0; but is it possible to do this sort of thing in a custom query? If you want this available in the query builder then you have to extend Tickets.pm to support new TicketSQL. You can do this in code without TicketSQL, but component would be custom and less flexible. -- http://www.linkedin.com/in/paultomblin http://careers.stackoverflow.com/ptomblin -- Best regards, Ruslan.
Re: [rt-users] Is it possible to do a table join in a query?
On Mon, Mar 26, 2012 at 02:04:46PM -0400, Paul Tomblin wrote: I would like to put a group name into a custom field, and then see only the tickets in a certain queue where that group is a group that I belong to. I know how to do this from pgsql: I assume you mean From TicketSQL, as available in the Advanced tab in the Query Builder ? Unfortunately, you can't do arbitrary joins from TicketSQL but you could do it from perl space (see the docs on DBIx::SearchBuilder for Join syntax). -kevin select t.* from tickets t join queues q on t.queue = [1]q.id join objectcustomfieldvalues ocfv on objecttype = 'RT::Ticket' and objectid = [2]t.id join customfields on [3]customfields.id = ocfv.customfield join groups on [4]groups.name = ocfv.content join groupmembers on groupmembers.groupid = [5]groups.id where [6]q.name = 'rtqueue1' and [7]customfields.name = 'group_assigned' and groupmembers.memberid = 24 and t.disabled = 0 and q.disabled = 0 and ocfv.disabled = 0; but is it possible to do this sort of thing in a custom query? pgpVwzFwiOlLV.pgp Description: PGP signature
[rt-users] Is it possible to do a table join in a query?
I would like to put a group name into a custom field, and then see only the tickets in a certain queue where that group is a group that I belong to. I know how to do this from pgsql: select t.* from tickets t join queues q on t.queue = q.id join objectcustomfieldvalues ocfv on objecttype = 'RT::Ticket' and objectid = t.id join customfields on customfields.id = ocfv.customfield join groups on groups.name = ocfv.content join groupmembers on groupmembers.groupid = groups.id where q.name = 'rtqueue1' and customfields.name = 'group_assigned' and groupmembers.memberid = 24 and t.disabled = 0 and q.disabled = 0 and ocfv.disabled = 0; but is it possible to do this sort of thing in a custom query? -- http://www.linkedin.com/in/paultomblin http://careers.stackoverflow.com/ptomblin
