GetWatcherInfoByTicket
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 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
DBSchema, SQL ANSI'92 standard, MySQL/PostgreSQL docs