[rt-users] How to get tickets with nested SQL select statement
Simon Lane
slane at cedvalinfo.com
Fri Mar 20 12:23:59 EDT 2009
If anyone is interested, I got this to work, but not by using the
SimpleQuery method. For those who need to do other direct queries......this
really basic example just builds up a list of tickets and then uses
RT:Tickets to actually do stuff with them (not shown).
my $dbh = $RT::Handle->dbh;
my $query_text = "SELECT ID FROM TICKETS WHERE ID >= 32394 AND ID <= 32396";
my $cursor = $dbh->prepare($query_text);
$cursor->bind_columns( undef, \$ticket_id);
$cursor->execute();
while ($cursor->fetch())
{
if ($ticket_count == 0)
{
$Query = "id = $ticket_id";
}
else
{
$Query .= " OR id = $ticket_id";
}
$ticket_count++;
}
$cursor->finish;
my $sql_query = $Query;
my $Tickets = RT::Tickets->new($session{'CurrentUser'});
$Tickets->FromSQL($Query);
-----Original Message-----
From: rt-users-bounces at lists.bestpractical.com
[mailto:rt-users-bounces at lists.bestpractical.com] On Behalf Of Simon Lane
Sent: March 17, 2009 8:59 AM
To: Ruslan Zakirov
Cc: rt-users at lists.bestpractical.com
Subject: Re: [rt-users] How to get tickets with nested SQL select statement
I hope everyone will excuse my ignorance, but I still can't get this to
work. I do not seem to get any rows back or, more likely, I am not getting
the ticket ID correctly. I reduced the query to a really simple one
directly on the tickets table to test:
my $sth = $RT::Handle->SimpleQuery( "select id from Tickets where id >=
32394 AND id <= 32396" );
$sth->execute();
my $ticket_id;
my @Tickets;
my $ticket_count = 0;
while ( $ticket_id = $sth->fetch())
{
$Query = "id = $ticket_id";
$Tickets[$ticket_count] = RT::Tickets->new($session{'CurrentUser'});
$Tickets[$ticket_count]->FromSQL($Query);
$ticket_count++;
}
I have looked at a lot of info on SimpleQuery but can only find reference to
the methods query and qs.....
Again, I really appreciate the help.
-----Original Message-----
From: Ruslan Zakirov [mailto:ruslan.zakirov at gmail.com]
Sent: March 13, 2009 1:07 PM
To: Simon Lane
Cc: rt-users at lists.bestpractical.com
Subject: Re: [rt-users] How to get tickets with nested SQL select statement
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.
-------------- 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/20090320/63036cd4/attachment.bin>
More information about the rt-users
mailing list