Unable to form query in TicketSQL; can RT do SQL?

I’m trying to do the query “show me any tickets that I’ve touched in any
way in the past three weeks.” I’ve tried to use the query ui and
TicketSQL but couldn’t find a query that worked.

I have access to the postgres DB that runs our RT system so I was able
to form the following SQL query that works:

select distinct foo.id, tickets.subject from (
select transactions.objectid, transactions.created, tickets.id
from transactions, tickets
transactions.creator=(select users.id from users where name=‘kleemann’)
and objecttype='RT::Ticket’
and transactions.created > (now()- interval ‘3 weeks’)
and transactions.objectid = tickets.id
) as foo, tickets
where foo.id=tickets.id

I’d like to have the query results available in RT so I could have a
list of ticket links. Is there a way to make RT use a query like this?
Is there an easier way to do this?


I don’t mean to spam but I thought I’d post this one more time. Does no
one do complicated queries like this? Am I doing something wrong?


Robert Kleemann wrote: