GetTicketInfoByWatcher

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

• 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

GetWatcherInfoByTicket