[rt-users] How to get tickets with nested SQL select statement

[Simon Lane]

I must be doing something wrong.  My initial query manually returns:
        ID
----------
      4346
      4359
      4486
      4534
      4535

      4600
      4346
      4359
      4486
      4534


When I run the code as follows, I don't seem to get anything:
   
my $sth = $RT::Handle->SimpleQuery( "select t.id from
rt.objectcustomfieldvalues ocf,rt.tickets t,rt.users u,rt.transactions
tr,rt.queues q where t.id=ocf.objectid(+) and (ocf.customfield in (1,11,13)
or ocf.customfield is null) and u.id=tr.creator and q.id=t.queue and
tr.objecttype like '%Ticket' and tr.objectid=t.id and trunc(tr.created)
between to_DAte('01-JUN-2008','dd-mon-yyyy') and
to_date('02-JUN-2008','dd-mon-yyyy') and (ocf.disabled = 0  or ocf.disabled
is null) and (ocf.content) in ('Severity 3','Severity 2','Severity
1','Baseline') and upper(u.name)=upper('root') group by rollup
(trunc(tr.created),(t.id,t.subject))" );
$sth->execute();
my $ticket_id;
$sth->bind_columns( undef, \$ticket_id );
my @Tickets;
my $sql_query;
my $ticket_count = 0;
while ( $sth->fetch())
        {
        $Query = "id = $ticket_id";
        $sql_query = $Query;
        $Tickets[$ticket_count] = RT::Tickets->new($session{'CurrentUser'});
        $Tickets[$ticket_count]->FromSQL($Query);
        $ticket_count++;
        }

Sorry, I realize this might be a bit basic, but I am new and learning this.
Thanks very much.

-----Original Message-----
From: Ruslan Zakirov [mailto:ruslan.zakirov at gmail.com] 
Sent: March 12, 2009 5:37 PM
To: Simon Lane
Cc: rt-users at lists.bestpractical.com
Subject: Re: [rt-users] How to get tickets with nested SQL select statement

If you're working on something that's only for you then it's easier to
use $RT::Handle ( in 3.8 it should be written as RT->DatabaseHandle )
that returns connection to the DB. This object has SimpleQuery method
to execute any SQL. SimpleQuery returns sth (statement handle)
described in `perldoc DBI`. So everything in complex looks like this:

my $sth = $RT::Handle->SimpleQuery( "any SQL you like" );
while ( my $row = $sth->fetchrow_hashref ) {
.... here goes custom processing of the results ....
}

As you can see you don't get objects but pure data structures without
methods, however if you have id of a ticket it's easy to turn it into
ticket object and get access to all methods it has.

On Thu, Mar 12, 2009 at 10:55 PM, Simon Lane <slane at cedvalinfo.com> wrote:
> I have an installation of RT (3.6.4) on Oracle and am trying to write a
> report (a modified MyDay report) that displays only those tickets that
have
> been updated by the current user within a given period.  I can get a list
of
> all tickets that have been modified  by setting:
>
>
>
> $Query = “LastUpdated => $olddate AND LastUpdate <= $newdate”;
>
>
>
> (where $olddate and $newdate are variables) and then calling
>
>
>
> my $Tickets = RT::Tickets->new($session{'CurrentUser'});
>
> $Tickets->FromSQL($Query);
>
>
>
> The trouble I have is that the above returns all of the tickets that have
> been updated within the given period.  In order to get around this, I have
a
> fairly complex query that will provide me with a list of ticket IDs that
> have been modified by a given user within the specified period:
>
>
>
> select t.id from   rt.objectcustomfieldvalues ocf,rt.tickets t,rt.users
> u,rt.transactions tr,rt.queues q
>
> where t.id=ocf.objectid(+)
>
> and (ocf.customfield in (1,11,13) or ocf.customfield is null) and
> u.id=tr.creator and q.id=t.queue and tr.objecttype like '%Ticket'
>
> and tr.objectid=t.id
>
> and trunc(tr.created) between to_DAte('01-JUN-2008','dd-mon-yyyy') and
> to_date('02-JUN-2008','dd-mon-yyyy')
>
> and (ocf.disabled = 0  or ocf.disabled is null) and (ocf.content) in
> ('Severity 3','Severity 2','Severity 1','Baseline') and
> upper(u.name)=upper('slane at cedvalinfo.com')
>
> group by rollup (trunc(tr.created),(t.id,t.subject));
>
>
>
> I would have thought that if I just added “id IN “ to the front of this so
> that, for example:
>
>
>
> $Query = “id IN (select t.id from rt.objectcustomfieldvalues
ocf,rt.tickets
> t,rt.users u,rt.transactions tr,rt.queues q where t.id=ocf.objectid(+) and
> (ocf.customfield in (1,11,13) or ocf.customfield is null) and
> u.id=tr.creator and q.id=t.queue and tr.objecttype like '%Ticket' and
> tr.objectid=t.id and trunc(tr.created) between
> to_DAte('01-JUN-2008','dd-mon-yyyy') and
> to_date('02-JUN-2008','dd-mon-yyyy') and (ocf.disabled = 0  or
ocf.disabled
> is null) and (ocf.content) in ('Severity 3','Severity 2','Severity
> 1','Baseline') and upper(u.name)=upper('slane at cedvalinfo.com') group by
> rollup (trunc(tr.created),(t.id,t.subject)))";
>
> my $Tickets = RT::Tickets->new($session{'CurrentUser'});
>
> $Tickets->FromSQL($Query);
>
>
>
> Unfortunately, this does not return anything (that I can see).
>
> Running this manually with “Select * from tickets where 

..” seems to
work,
> however and I get all the tickets.
>
> What is it that I am missing with RT:Tickets->FromSQL ?
>
>
>
> Any help would be greatly appreciated.
>
> Thanks.
>
>
>
> --
>
> Simon Lane
>
>
>
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
>
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com
>



-- 
Best regards, Ruslan.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/x-pkcs7-signature
Size: 4257 bytes
Desc: not available
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20090313/0acddc93/attachment.bin>