GetWatcherInfoByTicket

The following query select Watchers of Ticket #6

MySQL version of the query

SELECT DISTINCT
   t1.id Ticket_id,
   g2.id RoleGroup_id,
   g2.Type Role_Type,
   cgm3.MemberId RoleMember_id,
   p4.PrincipalType,
   u5.Name
FROM
   Tickets t1,
   Groups g2,
   CachedGroupMembers cgm3,
   Principals p4,
   Users u5
WHERE
   t1.id = 6 AND
   g2.Domain = 'RT::Ticket-Role' AND g2.Instance = t1.id AND
   cgm3.GroupId = g2.id AND
   p4.id = cgm3.MemberId AND
   p4.Disabled = 0 AND
   p4.PrincipalType = 'User'
   AND u5.id = p4.id;

Postgres version of the same query

SELECT DISTINCT
   t1.id AS Ticket_id,
   g2.id AS RoleGroup_id,
   g2.Type AS Role_Type,
   cgm3.MemberId AS RoleMember_id,
   p4.PrincipalType,
   u5.Name
FROM
   Tickets t1,
   Groups g2,
   CachedGroupMembers cgm3,
   Principals p4,
   Users u5
WHERE
   t1.id = 10880 AND
   g2.Domain = 'RT::Ticket-Role' AND g2.Instance = t1.id AND
   cgm3.GroupId = g2.id AND
   p4.id = cgm3.MemberId AND
   p4.Disabled = 0 AND
   p4.PrincipalType = 'User'
   AND u5.id = p4.id;

Description

• limit tickets records to ticket #6 (1)

t1.id = 6 AND

• join groups to it and limit it to only ticket's(1) role groups(2)

g2.Domain = 'RT::Ticket-Role' AND g2.Instance = t1.id AND

• each group(3) has members, join CGM table and found members of (3)

cgm3.GroupId = g2.id AND

• all users and groups has principal(4) record with same id, lets find all this principal records for our members(3)

p4.id = cgm3.MemberId AND

• principal(4) shouldn't be disabled

p4.Disabled = 0 AND

• it(4) should be user

p4.PrincipalType = 'User' AND

• and finaly join user(5) info to result set.

u5.id = p4.id;

Additions

• if you want particular type of watcher then add clause:

g2.Type = 'Requestor'

See also

GetTicketInfoByWatcher

DBSchema, SQL ANSI'92 standard, MySQL/PostgreSQL docs