TicketSQL - Revision history

Phanousk: /* Perl API */

2018-08-21T13:56:03Z

Perl API

← Older revision		Revision as of 09:56, 21 August 2018
Line 69:		Line 69:

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

Barton: Formatting cleanup

2017-08-18T01:44:09Z

Formatting cleanup

← Older revision		Revision as of 21:44, 17 August 2017
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 43:		Line 43:
	* <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.		* <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.		Consult the [https://metacpan.org/pod/Time::ParseDate#DATE-FORMATS-RECOGNIZED Time::ParseDate documentation] for all valid date/time formats.

	== Examples ==		== Examples ==

	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 > '7 days ago' AND Queue = 'General'		<tt>Created > '7 days ago' AND Queue = 'General'</tt>

	Status = 'stalled' AND Due <= 'today'		<tt>Status = 'stalled' AND Due <= '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 ==

Admin: 9 revisions imported

2016-04-06T20:39:28Z

9 revisions imported

← Older revision		Revision as of 16:39, 6 April 2016
(No difference)

203.12.195.243: link to Time::ParseDate documentation for all valid date/time formats

2014-06-09T08:03:56Z

link to Time::ParseDate documentation for all valid date/time formats

← Older revision		Revision as of 04:03, 9 June 2014
Line 42:		Line 42:
	* <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.		* <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 ==

124.148.207.98: Document relative dates in RT-QL

2013-07-05T09:18:47Z

Document relative dates in RT-QL

New page

[[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: [[UseRtCrontool|$RTHOME/bin/rt-crontool]] or [[UseRtTool|$RTHOME/bin/rt]], 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.

== 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 <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:

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

There are at least three valid date formats:

* <tt>today</tt> uses today's date as the date value
* <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>'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 ==

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

78.105.203.40: /* Special placeholders */

2011-10-22T16:26:20Z

Special placeholders

New page

[[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: [[UseRtCrontool|$RTHOME/bin/rt-crontool]] or [[UseRtTool|$RTHOME/bin/rt]], 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.

== 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 <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:

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

There are at least three valid date formats:

* <tt>today</tt> uses today's date as the date value
* <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}})

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

108.9.108.26: /* Date Syntax */ added not equal to

2011-03-15T16:43:57Z

Date Syntax: added not equal to

New page