[rt-users] help optimizing Tickets->Limit queries

[Boldra]

I'm sure this code is terrible; if for no other reason than it 
instantiates three RT::Tickets objects.  The purpose is to display a 
coloured gif on an related website based on the status of any tickets in 
RT with a matching requestor, all according to the following key:

white -> no tickets for that requestor
red -> some new/open tickets for that requestor found
green -> only resolved tickets for that requestor
yellow -> tickets for that requestor that are not new/open/resolved 
(matching stalled/rejected tickets found)

|sub get_colour {
  my ($email,$current_user) = @_;
  my ($colour,$remaining_tickets_count);
  my $tickets = new RT::Tickets($current_user);
  $tickets->LimitWatcher(TYPE => 'Requestor', VALUE => $email);
  my $total_tickets_count = $tickets->Count();
  if($total_tickets_count > 0) {
    #$RT::Logger->debug("$total_tickets_count tickets for $email");
    $tickets = new RT::Tickets($current_user);
    $tickets->LimitWatcher(TYPE => 'Requestor', VALUE => $email);
    $tickets->LimitStatus(VALUE => 'open');
    $tickets->LimitStatus(VALUE => 'new');
    $remaining_tickets_count =  $tickets->Count();
    #$RT::Logger->debug("$remaining_tickets_count open/new tickets for 
$email.");
    if($remaining_tickets_count > 0) {
      #There are open/new tickets for this requestor
      $colour = 'red';
    } else {
         $tickets = new RT::Tickets($current_user);
      $tickets->LimitWatcher(TYPE => 'Requestor', VALUE => $email);
      $tickets->LimitStatus(VALUE => 'resolved');
      $remaining_tickets_count = $tickets->Count();
      #$RT::Logger->debug("$remaining_tickets_count resolved tickets for 
$email.");
      if($remaining_tickets_count == $total_tickets_count) {
              #All the tickets are resolved
        $colour = 'green';
      } else {
          #There must be unresolved tickets here
          $colour = 'yellow';
      }
    }
  } else {
    #there were no tickets at all.
    $colour = 'white';
  }
}|

A query similar to the following would at least be efficient sql (except 
that I want the requestor, not the owner)
SELECT Status,COUNT(*) FROM Tickets where Tickets.Owner = '12' GROUP BY 
Status;

Any help very much appreciated!

Paul Boldra
ticketsystem.de
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20060728/8b17e5b0/attachment.htm>