[rt-users] SQL query to get a ticket's requestor ?

[Ruslan U. Zakirov]

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.

> 
> Regards.
>