MySQL variant of the query
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
- 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'