[Rt-devel] Subclause Aggregation with SearchBuilder

[Jon Wood]

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
-------------- 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/20090218/05c5b348/attachment.pgp