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

[Ruslan Zakirov]

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.