Admin: 2 revisions imported

2016-04-06T20:11:16Z

2 revisions imported

New page

The following query select [[Watcher]]s 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

GetWatcherInfoByTicket - Revision history

Admin: 2 revisions imported