RT Users - After some digging, I believe I've discovered why some tickets do not appear when searched by Requestor. It appears that some records are missing from CachedGroupMembers.
Can anyone recommend a way of querying for the requestor from GroupMembers and using that to populate CachedGroupMembers? GroupMembers contains the correct requestors -- but that doesn't seem to apply to the standard Search functions. Here's the dirt -- I have two tickets: * Ticket #1 -- Does not display a requestor in Simple.html and is not found using a Requestor search, but does show a Requestor in the Ticket View (under People). * Ticket #26 -- Shows a requestor and is found using a Requestor search. For Ticket #1 -- mysql> select id from Groups where instance='1' and Domain = 'RT::Ticket-Role' and Type = 'Requestor'; +----+ | id | +----+ | 52 | +----+ 1 row in set (0.00 sec) mysql> select MemberId from GroupMembers where GroupId = '52'; +----------+ | MemberId | +----------+ | 34 | +----------+ 1 row in set (0.00 sec) mysql> select EmailAddress from Users where id = '34'; +------------------------------+ | EmailAddress | +------------------------------+ | [EMAIL PROTECTED] | +------------------------------+ However, mysql> 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 = 1 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; Empty set (0.00 sec) On ticket #26 (the ticket that is discoverable using search), mysql> 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 = 26 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; +-----------+--------------+-----------+---------------+---------------+----------+ | Ticket_id | RoleGroup_id | Role_Type | RoleMember_id | PrincipalType | Name | +-----------+--------------+-----------+---------------+---------------+----------+ | 26 | 521 | Requestor | 124 | User | bubbleman | +-----------+--------------+-----------+---------------+---------------+----------+ 1 row in set (0.00 sec) Obviously the ticket view uses something more closely related to the initial query, and the Search functions use CachedGroupMembers. Any help is appreciated. I've modified Googlish.pm to do a fulltext search on Content, but that's a /really/ ugly hack. Thanks so much! _______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
