I’m trying to write a query the the FromSQL method. I need to create an object
contains tickets in our CustomerCare queue and that have either been worked on
or resolved in the last week (which will be based on the date the script is run,
not the last week from now).
This is what I have so far
$tix->FromSQL(‘Queue = “CustomerCare” AND Status = “resolved” OR Status = “open”’);
I’m thinking I need to add something like “AND (Created > $startDate AND
LastUpdated < $endDate) OR Resoved > $startDate”. Basically, I don’t really
care when it was created. I just need the transactions that were added between
a start date and an end date. That makes me think the above snippets are both
wrong.
I have been trying to use the Query Builder to get this accomplished but it
doesn’t seem to support both AND and OR requests in the same query. Changing
the aggregator changes it for the entire query, not just one line. Selecting
the Aggregator prior to adding a new criteria does the same thing.
Can anyone help me out with this?
Mathew
Different aggregator on the same level are ambiguous and you have to
use parentheses to group things by aggregator.On 3/22/07, Mathew Snyder theillien@yahoo.com wrote:
I’m trying to write a query the the FromSQL method. I need to create an object
contains tickets in our CustomerCare queue and that have either been worked on
or resolved in the last week (which will be based on the date the script is run,
not the last week from now).
This is what I have so far
$tix->FromSQL(‘Queue = “CustomerCare” AND Status = “resolved” OR Status = “open”’);
I’m thinking I need to add something like “AND (Created > $startDate AND
LastUpdated < $endDate) OR Resoved > $startDate”. Basically, I don’t really
care when it was created. I just need the transactions that were added between
a start date and an end date. That makes me think the above snippets are both
wrong.
I have been trying to use the Query Builder to get this accomplished but it
doesn’t seem to support both AND and OR requests in the same query. Changing
the aggregator changes it for the entire query, not just one line. Selecting
the Aggregator prior to adding a new criteria does the same thing.
Can anyone help me out with this?
Mathew
The rt-users Archives
Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com
Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
Best regards, Ruslan.
Roy,
The query you provided won’t work. I get zippy from it. What I’ve tried doing
is to create a variable with the query in it:
my $query = “‘Queue = “CustomerCare” AND (Status = “open” OR Status =
“resolved”) AND LastUpdate = $startDate’”;
and then using $query as the argument for FromSQL. This appears to cause the
script to go through every single ticket in the database looking for anything
resolved. The only way I’ve been able to get reliable results is if I limit the
search only to tickets that are open and Created after the start date.
Anything else you can point me toward?
Mathew
Roy El-Hames wrote: