How to convert TicketSql syntax to raw sql queries?

RT Users
1

I have a custom sql which will run when the spreadsheet button is clicked. And upon getting the $Collecion->Query I want to parse/convert that query into raw sql query. For example when I get this query from $collection->Query:
'( Status != 'Close ’ AND Queue = ‘Back Office ’ ) AND ( Queue = “29” ) AND ( Status = “open” ) AND Creator = “zahir.sarwari” AND ( ‘CustomField.{MSISDN}’ LIKE “93704954070” ) AND ( ‘CustomField.{Creator Department}’ LIKE “Call Center” ) AND ( ‘CustomField.{Category}’ = “Voice Bundle” ) AND ( ‘CustomField.{Subcategory}’ = “Daily 50MIN @ 15AFN” ) AND ( ‘CustomField.{Province}’ = “Faryab” OR ‘CustomField.{Province}’ = “Ghazni” OR ‘CustomField.{Province}’ = “Helmand” ) AND ( ‘CustomField.{Language}’ = “Dari” )’

I want to convert it to sql where clause and use it here:
my $sql_tickets = qq{
SELECT
t.id AS ticket_id,
cf_msisdn.Content AS MSISDN,
creator.Name AS Opened_By,
t.Status AS Status,
CASE
WHEN t.Status = ‘Close’
AND cf_credept.Content = ‘Call Center’ THEN ‘Call Center’
ELSE cf_credept.Content
END AS Creator_Department,
queue.Name AS Department,
t.LastUpdated AS Updated,
updater.Name AS Last_Modified_By,
cf_cat.Content AS Category,
cf_sub.Content AS Subcategory,
cf_lang.Content AS Language,
cf_pt.Content AS Problem_Type,
cf_prov.Content AS Province
FROM temp_export_ids e
JOIN Tickets t ON t.id = e.ticket_id
LEFT JOIN Users creator ON t.Creator = creator.id
LEFT JOIN Users updater ON t.LastUpdatedBy = updater.id
LEFT JOIN Queues queue ON t.Queue = queue.id

  LEFT JOIN ObjectCustomFieldValues cf_msisdn
    ON cf_msisdn.ObjectId   = t.id
   AND cf_msisdn.ObjectType = 'RT::Ticket'
   AND cf_msisdn.CustomField = ?

  LEFT JOIN ObjectCustomFieldValues cf_cat
    ON cf_cat.ObjectId   = t.id
   AND cf_cat.ObjectType = 'RT::Ticket'
   AND cf_cat.CustomField = ?

  LEFT JOIN ObjectCustomFieldValues cf_sub
    ON cf_sub.ObjectId   = t.id
   AND cf_sub.ObjectType = 'RT::Ticket'
   AND cf_sub.CustomField = ?

  LEFT JOIN ObjectCustomFieldValues cf_lang
    ON cf_lang.ObjectId   = t.id
   AND cf_lang.ObjectType = 'RT::Ticket'
   AND cf_lang.CustomField = ?

  LEFT JOIN ObjectCustomFieldValues cf_pt
    ON cf_pt.ObjectId   = t.id
   AND cf_pt.ObjectType = 'RT::Ticket'
   AND cf_pt.CustomField = ?

  LEFT JOIN ObjectCustomFieldValues cf_prov
    ON cf_prov.ObjectId   = t.id
   AND cf_prov.ObjectType = 'RT::Ticket'
   AND cf_prov.CustomField = ?

  LEFT JOIN ObjectCustomFieldValues cf_credept
    ON cf_credept.ObjectId   = t.id
   AND cf_credept.ObjectType = 'RT::Ticket'
   AND cf_credept.CustomField = ?

  ORDER BY e.ticket_id
};

Is that possible ?

2

You could turn on the statement log I think and grab the query that is generated when you do the UI ticket sql lookup

3

To hopefully helpfully expand a bit on @knation suggestion, you can use the _Handle() method in DBIx::SearchBuilder (which is what TicketSQL queries in the RT Perl API are based on) to get a DBIx::SearchBuilder::Handle object. Do this from whatever collection object you’re using (so RT::Tickets for example).

On this handle you can then call the LogSQLStatements(1) to turn on logging, set up your TicketSQL to make the query (using the FromSQL call on an RT::Tickets object for example), and then call SQLStatementLog() to get the list of real SQL queries made (and their invocation and execution times). Call LogSQLStatements(0) to turn the logging off.

The SQL statement log is an array of array references, with each inner array giving the invocation time, the SQL statement, the placeholder replacement values for the query (as another array), the execution time and a textual representation of the call stack (so you can see what methods were called to create this query).

Here’s a quick-n-dirty script to get you started:

use strict;
use lib '/opt/rt5/lib';
use RT -init;
use RT::Tickets;
use Data::Dumper;
$| = 1;

my $currentUser = RT::CurrentUser->new(RT::SystemUser);
my $Tickets = new RT::Tickets($currentUser);

my $queryHandle = $Tickets->_Handle();
$queryHandle->LogSQLStatements(1);
$Tickets->FromSQL("Status = 'Open' and Queue = 'Back Office'");
my @statementLog = $queryHandle->SQLStatementLog();
$queryHandle->LogSQLStatements(0);

print Dumper(@statementLog);
4

Yes, I’m grabbing this query but don’t know how to turn it into sql WHERE clause and use it in my custom sql query.

5

Thanks for the reply
It’s working just fine