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

[Ruslan Zakirov]

SimpleQuery does binding for you, ->SimpleQuery("... WHERE x = ? ...",
$binding, $another_one_binding);

So sth you get is ready for fetching. If sth is not true value then
it's an error.

On Fri, Mar 13, 2009 at 7:52 PM, Simon Lane <slane at cedvalinfo.com> wrote:
> 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.
>



-- 
Best regards, Ruslan.