Difference between revisions of "GetTicketInfoByWatcher"

From Request Tracker Wiki
Jump to navigation Jump to search
m (1 revision: Import (2/2) from Kwiki on wiki.bestpractical.com)
m (2 revisions imported)
(No difference)

Latest revision as of 16:11, 6 April 2016

The following query selects Tickets by id of a Watcher

MySQL variant of the query


    u.Name user_name,
    g.Type role,
    t.id ticket_id,
    t.Subject subject
    Users u,
    CachedGroupMembers cgm,
    Groups g,
    Tickets t
    u.id = 12
    AND cgm.MemberId = u.id
    AND g.id = cgm.GroupId
    AND g.Domain = 'RT::Ticket-Role'
    AND t.id = g.Instance


  • limit users by id, however you can use other restrictions: u.id = 12
  • user to be watcher should be a member of role-groups, so join with CachedGroupMembers: AND cgm.MemberId = u.id
  • only Groups table has info we need, so join it: AND g.id = cgm.GroupId
  • Role groups of a ticket have Domain = 'RT::Ticket-Role' AND g.Domain = 'RT::Ticket-Role'
  • 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...): AND t.id = g.Instance

That's all. If you want to see tickets where the user is requestor only then you can add:

AND g.Type = 'Requestor'

See also