Difference between revisions of "TicketSQL"

From Request Tracker Wiki
Jump to navigation Jump to search
 
(Document relative dates in RT-QL)
(2 intermediate revisions by the same user not shown)
Line 15: Line 15:
You can use __CurrentUser__ anywhere you'd use a user id. This is really useful for building saved searches for people.
You can use __CurrentUser__ anywhere you'd use a user id. This is really useful for building saved searches for people.


e.g. Owner = '__CurrentUser__' AND Status != 'resolved'
e.g. Owner = '__CurrentUser__' AND Status != 'resolved'


Bookmarked tickets can be searched too:
Bookmarked tickets can be searched too:
Line 29: Line 29:
* <tt>&lt;</tt> (less than)
* <tt>&lt;</tt> (less than)
* <tt>&lt;=</tt> (less than or equal to)
* <tt>&lt;=</tt> (less than or equal to)
* <tt>=</tt> (equals)
*
<tt>
=</tt> (equals)
* <tt>!=</tt> (not equal to)
* <tt>!=</tt> (not equal to)
* <tt>&gt;</tt> (greater than)
* <tt>&gt;</tt> (greater than)
Line 39: Line 41:
* <tt>x days ago</tt> where x is some integer value (e.g. 8 days ago)
* <tt>x days ago</tt> where x is some integer value (e.g. 8 days ago)
* <tt>YYYY-MM-DD</tt> absolute day in the format year-month-date (e.g. {{CURRENTYEAR}}-{{CURRENTMONTH}}-{{CURRENTDAY2}})
* <tt>YYYY-MM-DD</tt> absolute day in the format year-month-date (e.g. {{CURRENTYEAR}}-{{CURRENTMONTH}}-{{CURRENTDAY2}})
* <tt>'n seconds'</tt>, <tt>'n minutes'</tt>, <tt>'n hours'</tt>, <tt>'n days'</tt>, <tt>'n weeks'</tt> are dates in the future relative to the current date, ie current_date + n minutes. May be negative, eg '-2 days' is two days in the past.


== Examples ==
== Examples ==
Line 45: Line 48:


  (Status = 'new' OR Status = 'open') AND Queue = 'General' AND owner = 'joe'
  (Status = 'new' OR Status = 'open') AND Queue = 'General' AND owner = 'joe'


Searching by date:
Searching by date:
Line 52: Line 54:
   
   
  Status = 'stalled' AND Due &lt;= 'today'
  Status = 'stalled' AND Due &lt;= 'today'


Accessing custom fields:
Accessing custom fields:


  Status = 'resolved' AND CF.YourCustomField = 'somevalue'
  Status = 'resolved' AND CF.YourCustomField = 'somevalue'


Tickets that have no members (children):
Tickets that have no members (children):


  HasMember = 'NULL'
  HasMember = 'NULL'


Tickets that depend on at least one other ticket:
Tickets that depend on at least one other ticket:

Revision as of 05:18, 5 July 2013

TicketSQL is RT's loose variant of SQL that you can use for composing custom queries by hand:

For the time being, the best way to learn TicketSQL is to compose queries using Query Builder and then click Advanced to see the generated TicketSQL code.

Special placeholders

If you would like to search relative to the current user, you can click Advanced and enter something like

(Owner = '__CurrentUser__')

You can use __CurrentUser__ anywhere you'd use a user id. This is really useful for building saved searches for people.

e.g. Owner = '__CurrentUser__' AND Status != 'resolved'

Bookmarked tickets can be searched too:

id = '__Bookmarked__'

Date Syntax

Date statements take the following form . Field is some Ticket Field of type date. Operator is the comparison Operator. Date is a date value.

Valid operators include:

  • < (less than)
  • <= (less than or equal to)

= (equals)

  • != (not equal to)
  • > (greater than)
  • >= (greater than or equal to)

There are at least three valid date formats:

  • today uses today's date as the date value
  • x days ago where x is some integer value (e.g. 8 days ago)
  • YYYY-MM-DD absolute day in the format year-month-date (e.g. 2022-08-19)
  • 'n seconds', 'n minutes', 'n hours', 'n days', 'n weeks' are dates in the future relative to the current date, ie current_date + n minutes. May be negative, eg '-2 days' is two days in the past.

Examples

Tickets in the General queue that are new or open and owned by joe:

(Status = 'new' OR Status = 'open') AND Queue = 'General' AND owner = 'joe'

Searching by date:

Created > '7 days ago' AND Queue = 'General'

Status = 'stalled' AND Due <= 'today'

Accessing custom fields:

Status = 'resolved' AND CF.YourCustomField = 'somevalue'

Tickets that have no members (children):

HasMember = 'NULL'

Tickets that depend on at least one other ticket:

DependsOn != 'NULL'

Perl API

my $tickets = RT::Tickets->new(RT->SystemUser);
$tickets->FromSQL($tsql);
while (my $t = $tickets->Next) {
    # do stuff with each ticket $t here
    print $t->Subject, "\n";
}