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 <[email protected]> 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:[email protected]] > Sent: March 12, 2009 5:37 PM > To: Simon Lane > Cc: [email protected] > 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 <[email protected]> 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('[email protected]') >> >> 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('[email protected]') 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: [email protected] >> >> >> 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. _______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [email protected] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
