[Rt-devel] Subclause Aggregation with SearchBuilder

[Jon Wood]

Hi,

This may be the wrong list for this, but it does seem to be relevant to
RT.

I'm trying to extract a list of Transactions on a ticket which are
either a status update, or a change to a specific customfield. Currently
I'm using this:

                                                                                                                                            
 my $txs =$tkt->Transactions;
 $txs->Limit(FIELD => "Type", 
             VALUE => "Status", 
             SUBCLAUSE => "status");
                                                                                                                                                                                                                                                                      
 $txs->Limit(FIELD => "Type", 
             VALUE => "CustomField", 
             SUBCLAUSE => "cf");

 $txs->Limit(FIELD => "ReferenceType", 
             VALUE => "RT::ObjectCustomFieldValue", 
             ENTRYAGGREGATOR => "AND", 
             SUBCLAUSE => "cf");

 $txs->Limit(FIELD => "CustomField", 
             VALUE => "4", 
             ENTRYAGGREGATOR => "AND", 
             SUBCLAUSE => "cf");

Which gives me the following SQL:

 SELECT DISTINCT main.* 
 FROM Transactions main 
 JOIN Tickets Tickets_1  ON ( Tickets_1.id = main.ObjectId ) 
 WHERE (main.Type = 'Status') 
   AND (main.Type = 'CustomField' 
        AND main.ReferenceType = 'RT::ObjectCustomFieldValue' 
        AND main.CustomField = '4') 
   AND (main.ObjectType = 'RT::Ticket') 
   AND (Tickets_1.EffectiveId = '6')
 ORDER BY main.Created ASC, main.id ASC

Which is not going to work at all, since there will never be a
transaction which matches both types. I've hit a brick wall on getting
to be:

 SELECT DISTINCT main.* 
 FROM Transactions main 
 JOIN Tickets Tickets_1  ON ( Tickets_1.id = main.ObjectId ) 
 WHERE (main.Type = 'Status') 
    OR (main.Type = 'CustomField' 
        AND main.ReferenceType = 'RT::ObjectCustomFieldValue' 
        AND main.CustomField = '4') 
   AND (main.ObjectType = 'RT::Ticket') 
   AND (Tickets_1.EffectiveId = '6')
 ORDER BY main.Created ASC, main.id ASC

I'm sure there must be a way to do it, but no combination of
ENTRYAGGREGATOR parameters seems to be giving me the desired result.

Could somebody point me in the right direction please?

Thanks,
-- 
Jon Wood
SYSTEMS DEVELOPER

Claranet Limited
21 Southampton Row
London - WC1B 5HA
United Kingdom

+44 (0)20 7685 8643
jonathan.wood at uk.clara.net
www.uk.clara.net

Company registration no: 3152737
Place of registration: England

All the information contained within this this electronic message from
Claranet Ltd is covered by the disclaimer at
http://www.uk.clara.net/disclaimer
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 197 bytes
Desc: This is a digitally signed message part
Url : http://lists.bestpractical.com/pipermail/rt-devel/attachments/20090217/c258fe83/attachment.pgp