SQL query to get a ticket's requestor?

RT Users
1

Hi,

Does someone can give me a SQL query enabling me to get a ticket’s
requestor ?

Regards.

Jean-Pierre FORCIOLI OpenPGP: 1024D/CF173713
Cyber Networks http://www.cyber-networks.fr/
Tél : +33 (0)1 42 04 95 89 Fax : +33 (0)1 42 04 95 87

signature.asc (187 Bytes)

2

Hi,

Does someone can give me a SQL query enabling me to get a ticket’s
requestor ?

Regards.

Jean-Pierre FORCIOLI OpenPGP: 1024D/CF173713
Cyber Networks http://www.cyber-networks.fr/
Tél : +33 (0)1 42 04 95 89 Fax : +33 (0)1 42 04 95 87

3

Jean-Pierre FORCIOLI wrote:

Hi,

Does someone can give me a SQL query enabling me to get a ticket’s
requestor ?
Yeh.

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;

Will select watchers of ticket #6

Descripttion:
t1.id = 6 AND
limit tickets records to ticket #6 (1)

g2.Domain = ‘RT::Ticket-Role’ AND g2.Instance = t1.id AND
join groups to it and limit it to only ticket’s(1)
role groups(2)

cgm3.GroupId = g2.id AND
each group(3) has members
join CGM table and found members of (3)

p4.id = cgm3.MemberId AND
all users and groups has principal(4) record with same id
lets find all this principal records for our members(3)

p4.Disabled = 0 AND
principal(4) shouldn’t be disabled

p4.PrincipalType = ‘User’ AND
it(4) should be user

u5.id = p4.id;
and finaly join user(5) info to result set.

Additions:

if you want particular type of watcher then add clause:
	g2.Type = 'Requestor'

See also:
http://wiki.bestpractical.com/?DBSchema
SQL ANSI’92
MySQL/PostgreSQL docs

Is this enough?

				Best regards. Ruslan.