https://rt-wiki.bestpractical.com/index.php?title=GetTicketInfoByWatcher&feed=atom&action=historyGetTicketInfoByWatcher - Revision history2024-03-28T19:55:46ZRevision history for this page on the wikiMediaWiki 1.37.2https://rt-wiki.bestpractical.com/index.php?title=GetTicketInfoByWatcher&diff=1425&oldid=prevAdmin: 2 revisions imported2016-04-06T20:11:16Z<p>2 revisions imported</p>
<p><b>New page</b></p><div>The following query selects [[Ticket]]s by id of a [[Watcher]]<br />
<br />
== MySQL variant of the query ==<br />
<br />
SELECT DISTINCT<br />
u.Name user_name,<br />
g.Type role,<br />
t.id ticket_id,<br />
t.Subject subject<br />
FROM<br />
Users u,<br />
CachedGroupMembers cgm,<br />
Groups g,<br />
Tickets t<br />
WHERE<br />
u.id = 12<br />
AND cgm.MemberId = u.id<br />
AND g.id = cgm.GroupId<br />
AND g.Domain = 'RT::Ticket-Role'<br />
AND t.id = g.Instance<br />
<br />
== Description ==<br />
<br />
* limit users by id, however you can use other restrictions:<br />
<br />
u.id = 12<br />
<br />
* user to be watcher should be a member of role-groups, so join with [[CachedGroupMembers]]:<br />
<br />
AND cgm.MemberId = u.id<br />
<br />
* only Groups table has info we need, so join it:<br />
<br />
AND g.id = cgm.GroupId<br />
<br />
* Role groups of a ticket have Domain = 'RT::Ticket-Role'<br />
<br />
AND g.Domain = 'RT::Ticket-Role'<br />
<br />
* Values in the Instance column of the Groups table are ids of tickets. Note, that it only applies when you limit Domain, groups in other domains may point to other objects (Users, Queues...):<br />
<br />
AND t.id = g.Instance<br />
<br />
<br />
That's all. If you want to see tickets where the user is requestor only then you can add:<br />
<br />
AND g.Type = 'Requestor'<br />
<br />
== See also ==<br />
<br />
[[GetWatcherInfoByTicket]]</div>Admin