[rt-users] unable to form query in TicketSQL; can RT do SQL?

[Robert Kleemann]

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?

thanks
Robert.

Robert Kleemann wrote:
> 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
>  where
>  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?
>
> thanks!
> Robert.
> _______________________________________________
> 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