TicketSQL

From Request Tracker Wiki
Revision as of 12:43, 15 March 2011 by 108.9.108.26 (talk) (→‎Date Syntax: added not equal to)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

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

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

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

(Owner = '__CurrentUser__')

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.

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. 1898-03-13)

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

$tickets->FromSQL($tsql);