Difference between revisions of "TicketSQL"

From Request Tracker Wiki
Jump to navigation Jump to search
 
 
(6 intermediate revisions by 4 users not shown)
Line 11: Line 11:
If you would like to search relative to the current user, you can click Advanced and enter something like
If you would like to search relative to the current user, you can click Advanced and enter something like


(Owner = '__CurrentUser__')
<tt> (Owner = '__CurrentUser__')</tt>


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. <tt>Owner = '__CurrentUser__' AND Status != 'resolved'</tt>


Bookmarked tickets can be searched too:
Bookmarked tickets can be searched too:


id = '__Bookmarked__'
<tt> id = '__Bookmarked__'</tt>


== Date Syntax ==
== Date Syntax ==
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.
Consult the [https://metacpan.org/pod/Time::ParseDate#DATE-FORMATS-RECOGNIZED Time::ParseDate documentation] for all valid date/time formats.


== Examples ==
== Examples ==
Line 44: Line 48:
Tickets in the General queue that are new or open and owned by joe:
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'
<tt>(Status = 'new' OR Status = 'open') AND Queue = 'General' AND owner = 'joe'</tt>


Searching by date:
Searching by date:


Created &gt; '7 days ago' AND Queue = 'General'
<tt>Created &gt; '7 days ago' AND Queue = 'General'</tt>
Status = 'stalled' AND Due &lt;= 'today'
   
   
<tt>Status = 'stalled' AND Due &lt;= 'today'</tt>


Accessing custom fields:
Accessing custom fields:


Status = 'resolved' AND CF.YourCustomField = 'somevalue'
<tt> Status = 'resolved' AND CF.YourCustomField = 'somevalue'</tt>


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


HasMember = 'NULL'
<tt> HasMember = 'NULL'</tt>


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


DependsOn != 'NULL'
<tt> DependsOn != 'NULL'</tt>


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

Latest revision as of 09:56, 21 August 2018

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. 2024-03-28)
  • '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.

Consult the Time::ParseDate documentation for all valid date/time formats.

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