GetTicketInfoByWatcher

From Request Tracker Wiki
Revision as of 16:11, 6 April 2016 by Admin (talk | contribs) (2 revisions imported)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

The following query selects Tickets by id of a Watcher

MySQL variant of the query

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

Description

  • limit users by id, however you can use other restrictions:
u.id = 12
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

GetWatcherInfoByTicket