CLI Scripting Question

Good morning,

I am currently writing a few scripts for internal usage to faciliate some Scrum like pattern within RT. One script does create timeLeft Sums on all tickets of a given sprint (which is a parent ticket). While all and all this works great, I have a little question regarding RT::ticket:

The main loop of the script should look for all tickets starting with the text “Sprint”, doing about this:

my $tickets = RT::Tickets->new(RT->SystemUser);
$tickets->LimitQueue( VALUE => $queueName );
$tickets->LimitStatus( VALUE => ‘new’ );
$tickets->LimitStatus( VALUE => ‘open’ );
$tickets->LimitSubject( VALUE => “Sprint %”, OPERATOR => ‘LIKE’ );
$tickets->OrderBy( FIELD => ‘Id’, ORDER => ‘ASC’ );

Unfortunalety, the LIKE operation seems to enclose the search pattern into wildcards as well, thus finding all tickets which contain the string “Sprint” anywhere in the ticket, not just in the subject.

The operator “STARTSWITH” is not accepted, although specifying STARTSWITH in an extended query in RT does yield the expected result.

Any hints how I can solve this?

Best regards,
Torben Nehmer
Torben Nehmer
Diplom Informatiker (FH)
Business System Developer
CANCOM Deutschland GmbH
Messerschmittstr. 20
89343 Scheppach
Germany
Tel.: +49 8225 - 996-1118
Fax: +49 8225 - 996-41118
torben.nehmer@cancom.demailto:torben.nehmer@cancom.de
www.cancom.dehttp://www.cancom.de/
CANCOM Deutschland GmbH
Sitz der Gesellschaft: Jettingen-Scheppach
HRB 10653 Memmingen
Geschäftsführer: Martin Mayr, Tobias Hörmann
Diese E-Mail und alle mitgesendeten Dateien sind vertraulich und ausschließlich für den Gebrauch durch den Empfänger bestimmt!
This e-mail and any files transmitted with it are confidential intended solely for the use of the addressee!

Could you try this condition:

$tickets->LimitSubject( VALUE => “*^*Sprint %”, OPERATOR => ‘LIKE’ );

– Bart

Op 29 november 2011 09:12 schreef Nehmer Torben
torben.nehmer@cancom.dehet volgende:

Could you try this condition:

$tickets->LimitSubject( VALUE => “^Sprint %”, OPERATOR => ‘LIKE’ );

It wouldn’t work. Most times it’s easier to use
$tickets->FromSQL(“Queue = ‘x’ AND (Status …)”) instead of Limit*
calls. LIKE adding % around arguments is historical thing. Use
MATCHES.

– Bart

het volgende:

Good morning,

I am currently writing a few scripts for internal usage to faciliate some
Scrum like pattern within RT. One script does create timeLeft Sums on all
tickets of a given sprint (which is a parent ticket). While all and all this
works great, I have a little question regarding RT::ticket:

The main loop of the script should look for all tickets starting with the
text “Sprint”, doing about this:

my $tickets = RT::Tickets->new(RT->SystemUser);

$tickets->LimitQueue( VALUE => $queueName );

$tickets->LimitStatus( VALUE => ‘new’ );

$tickets->LimitStatus( VALUE => ‘open’ );

$tickets->LimitSubject( VALUE => “Sprint %”, OPERATOR => ‘LIKE’ );

$tickets->OrderBy( FIELD => ‘Id’, ORDER => ‘ASC’ );

Unfortunalety, the LIKE operation seems to enclose the search pattern into
wildcards as well, thus finding all tickets which contain the string
“Sprint” anywhere in the ticket, not just in the subject.

The operator “STARTSWITH” is not accepted, although specifying STARTSWITH
in an extended query in RT does yield the expected result.

Any hints how I can solve this?

Best regards,
Torben Nehmer


Torben Nehmer
Diplom Informatiker (FH)
Business System Developer

CANCOM Deutschland GmbH
Messerschmittstr. 20
89343 Scheppach
Germany

Tel.: +49 8225 - 996-1118
Fax: +49 8225 - 996-41118
torben.nehmer@cancom.de
www.cancom.de

CANCOM Deutschland GmbH
Sitz der Gesellschaft: Jettingen-Scheppach
HRB 10653 Memmingen
Geschäftsführer: Martin Mayr, Tobias Hörmann

Diese E-Mail und alle mitgesendeten Dateien sind vertraulich und
ausschließlich für den Gebrauch durch den Empfänger bestimmt!
This e-mail and any files transmitted with it are confidential intended
solely for the use of the addressee!


RT Training Sessions (http://bestpractical.com/services/training.html)

  • Boston — TBA

RT Training Sessions (http://bestpractical.com/services/training.html)

  • Boston — TBA

Best regards, Ruslan.

Ruslan Zakirov wrote:> On Tue, Nov 29, 2011 at 12:41 PM, Bart bart@pleh.info wrote:

Could you try this condition:

$tickets->LimitSubject( VALUE => “^Sprint %”, OPERATOR => ‘LIKE’ );

It wouldn’t work. Most times it’s easier to use
$tickets->FromSQL(“Queue = ‘x’ AND (Status …)”) instead of Limit*
calls. LIKE adding % around arguments is historical thing. Use
MATCHES.

Our use of RT has lead us to patch SearchBuilder to NOT include the % in
front and back of the search term but to leave it to the enduser the ask
for wildcard searches. Big bonus: actual use of the indexes.

Joop

Hi,

It wouldn’t work. Most times it’s easier to use
$tickets->FromSQL(“Queue = ‘x’ AND (Status …)”) instead of Limit*
calls. LIKE adding % around arguments is historical thing. Use
MATCHES.

Using OPERATOR => ‘MATCHES’ produces an error:

[Wed Nov 30 08:15:28 2011] [error]: RestrictionsToClauses: Invalid operator MATCHES for Subject (STRING) at /usr/share/request-tracker4/lib/RT/Tickets.pm line 3363. (/usr/share/request-tracker4/lib/RT/Tickets.pm:3416)

MATCHES in FromSQL does effectively break the entire query, LIKE in FromSQL has the same bahvoir as LimitSubject.

Besides, I find it much more error prone to build SQL queries by hand.

So I am still where I started.

It can’t be that hard to do a query the subject for a string on the beginning without patching the Perl SearchBuilder modules itself, can it?

Best regards,
Torben

Hi,

It wouldn’t work. Most times it’s easier to use
$tickets->FromSQL(“Queue = ‘x’ AND (Status …)”) instead of Limit*
calls. LIKE adding % around arguments is historical thing. Use
MATCHES.

Using OPERATOR => ‘MATCHES’ produces an error:

[Wed Nov 30 08:15:28 2011] [error]: RestrictionsToClauses: Invalid operator MATCHES for Subject (STRING) at /usr/share/request-tracker4/lib/RT/Tickets.pm line 3363. (/usr/share/request-tracker4/lib/RT/Tickets.pm:3416)

MATCHES in FromSQL does effectively break the entire query, LIKE in FromSQL has the same bahvoir as LimitSubject.

Besides, I find it much more error prone to build SQL queries by hand.

So I am still where I started.

It can’t be that hard to do a query the subject for a string on the beginning without patching the Perl SearchBuilder modules itself, can it?

You want OPERATOR => ‘STARTSWITH’, documented in DBIx::SearchBuilder
on the Limit method.

-kevin