GetWatcherInfoByTicket

From Request Tracker Wiki
Jump to navigation Jump to search

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