How to get tickets with nested SQL select statement

RT Users
1

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@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@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

smime.p7s (4.16 KB)

2

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@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@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@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

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@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.

3

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.From: Ruslan Zakirov [mailto:ruslan.zakirov@gmail.com]
Sent: March 12, 2009 5:37 PM
To: Simon Lane
Cc: rt-users@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.

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@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@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

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@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.

smime.p7s (4.16 KB)

4

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@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@gmail.com]
Sent: March 12, 2009 5:37 PM
To: Simon Lane
Cc: rt-users@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@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@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@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

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@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.

5

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.From: Ruslan Zakirov [mailto:ruslan.zakirov@gmail.com]
Sent: March 13, 2009 1:07 PM
To: Simon Lane
Cc: rt-users@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.

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@gmail.com]
Sent: March 12, 2009 5:37 PM
To: Simon Lane
Cc: rt-users@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.

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@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@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

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@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.

smime.p7s (4.16 KB)

6

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);From: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Simon Lane
Sent: March 17, 2009 8:59 AM
To: Ruslan Zakirov
Cc: rt-users@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.

From: Ruslan Zakirov [mailto:ruslan.zakirov@gmail.com]
Sent: March 13, 2009 1:07 PM
To: Simon Lane
Cc: rt-users@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.

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@gmail.com]
Sent: March 12, 2009 5:37 PM
To: Simon Lane
Cc: rt-users@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.

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@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@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

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@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.

smime.p7s (4.16 KB)