I am working on a notification that is supposed to be generated for
tickets based on a date entered into a custom field. As such I need to
search for tickets where the date in the field is in a specific range. I
cannot use the date fields already in RT tickets as we have other plans
for those.
I created this SQL as an example of what I am working for. It is a
postgres database so table/column capitalization is unimportant.
SELECT t.id FROM
objectcustomfieldvalues AS v
JOIN tickets AS t ON (v.objectid = t.id)
JOIN queues AS q ON (t.queue = q.id)
JOIN customfields AS c ON ( c.id = v.customfield )
WHERE
objecttype = ‘RT::Ticket’
AND q.name = ‘SP-Delivery’
AND date(v.content) > (now() - interval ‘1 day’)
AND c.name = ‘Expected Decision Date’;
I was looking at SearchOnDates - Request Tracker Wiki which
does not seem to pertain in this case. As well I have been studying
SBIx::Searchbuilder and the SQL libraries in RT. It would appear
DBIx::SearchBuilder and the other code does not have any system for
doing a limit on a value cast as a specific type.
I was going to go ahead and write a plugin that connects to the DB
manually, finds the tickets, and returns a Tickets object with them. But
I wanted to check in here first to see if there was already a solution,
or something simple I could use.
Thank you,
Chad
signature.asc (252 Bytes)