[Rt-devel] Subclause Aggregation with SearchBuilder

[Ruslan Zakirov]

different subclauses are always joined with AND

On Wed, Feb 18, 2009 at 1:30 PM, Jon Wood <jonathan.wood at uk.clara.net> wrote:
> Using subclauses I've already got thing grouped correctly (the
> Open/CloseParen calls aren't neccesary), the problem is getting them
> matched using OR instead of AND.
>
> Thanks,
>
> Jon
>
> On Tue, 2009-02-17 at 21:11 +0300, Ruslan Zakirov wrote:
>> 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
>> >
>> >
>>
>>
>>
> --
> 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
>



-- 
Best regards, Ruslan.