Difference between revisions of "TicketSQL"

From Request Tracker Wiki
Jump to navigation Jump to search
(→‎Date Syntax: added not equal to)
 
(One intermediate revision by the same user not shown)
Line 2: Line 2:


* in RT's web interface on the Query Builder page (click Advanced)
* in RT's web interface on the Query Builder page (click Advanced)
* on the command-line: [$RTHOME/bin/rt-crontool [[UseRtCrontool]]] or [$RTHOME/bin/rt [[UseRtTool]]], the general-purpose command-line tool
* on the command-line: [[UseRtCrontool|$RTHOME/bin/rt-crontool]] or [[UseRtTool|$RTHOME/bin/rt]], the general-purpose command-line tool
* in RT's Perl API
* in RT's Perl API


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


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


  (Owner = '__CurrentUser__')
  (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 Syntax ==


Date statements take the following form [[Field]] [[Operator]] [[Date]] Field is some Ticket Field of type date. Operator is the comparison Operator. Date is a date value.
Date statements take the following form <code><Field> <Operator> <Date></code>. Field is some Ticket Field of type date. Operator is the comparison Operator. Date is a date value.


Valid operators include:
Valid operators include:


* &lt; (less than)
* <tt>&lt;</tt> (less than)
* &lt;= (less than or equal to)
* <tt>&lt;=</tt> (less than or equal to)
* = (equals)
* <tt>=</tt> (equals)
* != (not equal to)
* <tt>!=</tt> (not equal to)
* &gt; (greater than)
* <tt>&gt;</tt> (greater than)
* &gt;= (greater than or equal to)
* <tt>&gt;=</tt> (greater than or equal to)


There are at least three valid date formats:
There are at least three valid date formats:


* 'today' uses today's date as the date value
* <tt>today</tt> uses today's date as the date value
* 'x days ago' 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)
* <nowiki>'yyyy-mm-dd'' absolute day in the format year-month-date (e.g. 1898-03-13) </nowiki>
* <tt>YYYY-MM-DD</tt> absolute day in the format year-month-date (e.g. {{CURRENTYEAR}}-{{CURRENTMONTH}}-{{CURRENTDAY2}})


== Examples ==
== Examples ==
Line 60: Line 70:
== Perl API ==
== Perl API ==


$tickets-&gt;[[FromSQL]]($tsql);
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";
}

Revision as of 12:26, 22 October 2011

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)

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";
}