Subclause Aggregation with SearchBuilder

RT Developers
1

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@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

signature.asc (197 Bytes)

2

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@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@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
Email Disclaimer | Claranet UK

List info: The rt-devel Archives

Best regards, Ruslan.

3

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,

JonOn 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@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@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
Email Disclaimer | Claranet UK

List info: The rt-devel Archives

Jon Wood
SYSTEMS DEVELOPER

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

+44 (0)20 7685 8643
jonathan.wood@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

signature.asc (197 Bytes)

4

different subclauses are always joined with ANDOn Wed, Feb 18, 2009 at 1:30 PM, Jon Wood jonathan.wood@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@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@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
Email Disclaimer | Claranet UK

List info: The rt-devel Archives

–
Jon Wood
SYSTEMS DEVELOPER

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

+44 (0)20 7685 8643
jonathan.wood@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
Email Disclaimer | Claranet UK

Best regards, Ruslan.