hello’s!
i’m unable to solve a statistics query problem. specifically, the
‘deleted’ count is always zero, presumably because of this query:
SELECT count(main.id) FROM Tickets main WHERE ((main.EffectiveId =
main.id)) AND ((main.Status != ‘deleted’)) AND ((main.Type = ‘ticket’))
AND ( ( (main.Status = ‘deleted’) ) AND ( (main.Queue = ‘1’) ) AND (
(main.LastUpdated <= ‘2006-01-17 16:00’)AND(main.LastUpdated >=
‘2006-01-16 16:00’) ) );
i think the problem is:
((main.Status != ‘deleted’)) … AND ( ( (main.Status = ‘deleted’) )
when i run the query manually without the !=deleted, the count returns
correct.
the only instance i’ve found in the rt3 codebase is
./lib/RT/Tickets_Overlay.pm, but removing the ‘AND ((main.Status !=
‘deleted’))’ doesn’t change the query that’s being run (viewing in mysql
log).
i’m running rt 3.4.4 and RTx-Statistics v1.8.
has someone solved this problem and i’ve glossed over it in the lists?
thanks!
joe
hello’s!
i’m unable to solve a statistics query problem. specifically, the
‘deleted’ count is always zero, presumably because of this query:
SELECT count(main.id) FROM Tickets main WHERE ((main.EffectiveId =
main.id)) AND ((main.Status != ‘deleted’)) AND ((main.Type = ‘ticket’))
AND ( ( (main.Status = ‘deleted’) ) AND ( (main.Queue = ‘1’) ) AND (
(main.LastUpdated <= ‘2006-01-17 16:00’)AND(main.LastUpdated >=
‘2006-01-16 16:00’) ) );
i think the problem is:
((main.Status != ‘deleted’)) … AND ( ( (main.Status = ‘deleted’) )
when i run the query manually without the !=deleted, the count returns
correct.
the only instance i’ve found in the rt3 codebase is
./lib/RT/Tickets_Overlay.pm, but removing the ‘AND ((main.Status !=
‘deleted’))’ doesn’t change the query that’s being run (viewing in mysql
log).
i’m running rt 3.4.4 and RTx-Statistics v1.8.
Hello, Kelly, Joseph and co.
RT doesn’t allow searching for deleted tickets unless you use special option
$tickets_obj->{‘allow_deleted_search’} = 1
This functionality is available in 3.4.2 and greater, for earlier
versions you should patch RT.
See also usage in RTx::Shredder::Plugin::Tickets; and patch is also
shipped with shredder distribution.
has someone solved this problem and i’ve glossed over it in the lists?
thanks!
joe
The rt-users Archives
Be sure to check out the RT Wiki at http://wiki.bestpractical.com
Download a free sample chapter of RT Essentials from O’Reilly Media at http://rtbook.bestpractical.com
WE’RE COMING TO YOUR TOWN SOON - RT Training in Amsterdam, Boston and
San Francisco - Find out more at http://bestpractical.com/services/training.html
Best regards, Ruslan.
[Kelly F. Hickel] Also, I’ve looked at this and can’t sync that query up
with the source. Joseph, can you tell us which report in RTx:Statistics
you’re using to generate that query?
this is just the ‘Tickets per Day’ target under RTx-Statistics. i did
try changing the allow_deleted_search to no avail.
in the file “./lib/RT/Tickets_Overlay.pm” @line 1390:
RT adds Status != ‘deleted’ until object has
allow_deleted_search internal property set.
$tickets->{‘allow_deleted_search’} = 1;
$tickets->LimitStatus( VALUE => ‘deleted’ );
i do want deleted tickets in the results, but changed to 0 and also
changed $tickets->LimitStatus( VALUE => ‘deleted’ ); to
$tickets->LimitStatus( VALUE => ‘’ );.
whether i restart apache or not, the query is not changed and results
are all zero for deleted tickets.
thanks for looking into this!
joe
-----Original Message-----
From: joseph kacmarcik [mailto:joe@earth.care2.com]
Sent: Tuesday, January 17, 2006 11:42 AM
To: Kelly F. Hickel
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] statistics query
[Kelly F. Hickel] Also, I’ve looked at this and can’t sync that
query up
with the source. Joseph, can you tell us which report in
RTx:Statistics
you’re using to generate that query?
this is just the ‘Tickets per Day’ target under RTx-Statistics. i did
try changing the allow_deleted_search to no avail.
in the file “./lib/RT/Tickets_Overlay.pm” @line 1390:
RT adds Status != ‘deleted’ until object has
allow_deleted_search internal property set.
$tickets->{‘allow_deleted_search’} = 1;
$tickets->LimitStatus( VALUE => ‘deleted’ );
i do want deleted tickets in the results, but changed to 0 and also
changed $tickets->LimitStatus( VALUE => ‘deleted’ ); to
$tickets->LimitStatus( VALUE => ‘’ );.
whether i restart apache or not, the query is not changed and results
are all zero for deleted tickets.
thanks for looking into this!
joe
Looking at the code in CallsQueueDay/index.html, I don’t see any way to
overcome this in the RTx::Statistics package, it must be something
happening under the covers. The excerpted code that generates this
query looks like this:
% my $tix = new RT::Tickets($session{‘CurrentUser’});
% $tix->LimitQueue (VALUE => $Queue);
% $tix->LimitStatus(VALUE => ‘deleted’);
% $tix->LimitLastUpdated(VALUE => $dates[$d]->ISO, OPERATOR
=> “>=”);
% if ($dates[$d+1]) {
% $tix->LimitLastUpdated(VALUE => $dates[$d+1]->ISO,
OPERATOR => “<=”);
% }
–Kelly
Looking at the code in CallsQueueDay/index.html, I don’t see any way to
overcome this in the RTx::Statistics package, it must be something
happening under the covers. The excerpted code that generates this
query looks like this:
% my $tix = new RT::Tickets($session{‘CurrentUser’});
% $tix->LimitQueue (VALUE => $Queue);
% $tix->LimitStatus(VALUE => ‘deleted’);
% $tix->LimitLastUpdated(VALUE => $dates[$d]->ISO, OPERATOR
=> “>=”);
% if ($dates[$d+1]) {
% $tix->LimitLastUpdated(VALUE => $dates[$d+1]->ISO,
OPERATOR => “<=”);
% }
i just found something else that’s alleviated ‘my’ problem. in
rt-3.4.4/lib/RT/Tickets_Overlay_SQL.pm @line 511:
We don’t want deleted tickets unless ‘allow_deleted_search’ is set
unless( $self->{‘allow_deleted_search’} ) {
$self->SUPER::Limit(FIELD => ‘Status’,
OPERATOR => ‘!=’,
VALUE => ‘deleted’);
}
it’s not a solution for everyone, but i’ve added:
$self->{‘allow_deleted_search’} = 1;
which gives me the results i want. i had tried changing
CallsQueueDay/index.html, but didn’t get anywhere. i guess it’s just not
picking up this variable.(?)
i’ll check out rt 3.4.5 shortly to see if there’s any difference in this
behavior.
thanks again!
joe