[Rt-devel] Subclause Aggregation with SearchBuilder

[Ruslan Zakirov]

Subclause is the way to group multiple conditions with different
aggregators, something like:

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

 $txs->_OpenParen("XXX");

 $txs->Limit(FIELD => "Type",
            VALUE => "CustomField",
            ENTRYAGGREGATOR => "OR",
            SUBCLAUSE => "XXX");

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

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

 $txs->_CloseParen("XXX");

I think it should work, but I have not tested.

2009/2/17 Jon Wood <jonathan.wood at uk.clara.net>:
> 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
>
> _______________________________________________
> List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel
>
>



-- 
Best regards, Ruslan.