[Rt-devel] Search for tickets in queue with cf storing date, based on date.

[Chad Granum]

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 http://wiki.bestpractical.com/view/SearchOnDates  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

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 252 bytes
Desc: OpenPGP digital signature
Url : http://lists.bestpractical.com/pipermail/rt-devel/attachments/20090130/17fb1a9e/attachment.pgp