Question about sql limits

RT Users
1

Good day.

In RT code many constructions like $self->TicketsObj->Limit* - limit sql query in some params.
I try use construction
$self->TicketsObj->LimitPriority(VALUE => “FinalPriority”, OPERATOR => “<”);

and finally sql code look like this:

…AND ( ‘Priority’ < ‘FinalPriority’ )

But I can’t understand how get sql code like this

AND ( ‘Priority’ < FinalPriority )

Can someone help?

2

Something like this? $self->TicketsObj->LimitPriority(VALUE => $self->TicketsObj->FinalPriority, OPERATOR => "<");

3

may be, but I see error:
Can’t locate object method “FinalPriority” via package “RT::Tickets”

4

Where are you trying to do this? You want to use the TicketObj since you should only be checking the final priority for one ticket

5

I’m trying next:

/local/lib/RT/Search/ActivePriorityTicketsInQueue.pm
package RT::Search::ActivePriorityTicketsInQueue;

use strict;
use warnings;
use base qw(RT::Search);

sub Describe {
my $self = shift;
return ($self->loc(“No description for [_1]”, ref $self));
}

sub Prepare {
my $self = shift;

$self->TicketsObj->LimitPriority(VALUE => $self->TicketsObj->FinalPriority, OPERATOR => “<”);
$self->TicketsObj->LimitToActiveStatus;

return(1);
}

RT::Base->_ImportOverlays();

1;

and exec that in

rt-crontool --search RT::Search::ActivePriorityTicketsInQueue --action RT::Action::EscalatePriority

6

I think it’d be better to make a new action /local/lib/RT/EscalatePriorityBasedOnFinalPriority.pm where you copy the content of EscalatePriority but add a check to see if the action should run based on the Final priority of the ticket

7

I would like to limit the search in the database and not filter the result in RT.
In ActivePriorityTicketsInQueue I can use

$self->TicketsObj->LimitFinalPriority(VALUE => 1, OPERATOR => “>”);

and found tickets that used priority. But this search will also include tickets with Priority==FinalPriority and you will need to drop them into action.

SQL

select id from Tickets where (Status = ‘open’ OR Status = ‘new’ OR Status = ‘stalled’) and ‘Priority’ < FinalPriority;

do what I want. How to generate this sql in RT code?

8

You can try using the RT::Search::FromSQL module but I don’t know if you can use two variable values like that but its worth a try

9

I tried this module:

$self->TicketsObj->FromSQL(“( Status = ‘open’ OR Status = ‘new’ OR Status = ‘stalled’ ) AND ( Priority < FinalPriority )”);

and got the error:

Couldn’t parse query: Wrong query, expecting a VALUE in ‘( Status = ‘open’ OR Status = ‘new’ OR Status = ‘stalled’ ) AND ( Priority < >FinalPriority<–here )’ at /
opt/rt4/bin/…/lib/RT/Tickets.pm line 3036, line 522. (/opt/rt4/bin/…/lib/RT/Tickets.pm:3213)

10

I see there appears to be a branch on Github:
https://github.com/bestpractical/rt/commits/4.4/columns-as-values-in-ticket-search

11

Yes, because there is a typo in the code. It should be:

$self->TicketsObj->LimitPriority(VALUE => $self->TicketObj->FinalPriority, OPERATOR => "<");
12

Nop

Can’t locate object method “TicketObj” via package “RT::Search::ActivePriorityTicketsInQueue”