TicketCustomFieldValues = ObjectCustomFieldValues?

I just came across another snag in my migration (3.4.2 -> 3.4.5) migration

slap my wrist for not using RT’s API…
however we already have a bunch of reports calling on the mysql database
table TicketCustomFieldValues and I don’t want overhaul all those pages
just yet.

Can I simply substitute the table name ObjectCustomFieldValues for
TicketCustomFieldValues in our non-api aware code or is there something
more complicated that I need to be aware of?

Browsing around the database it looks fairly similar…

Thanks,
Mike

some code snippets below:
$sql = “SELECT TicketCustomFieldValues.CustomField,
TicketCustomFieldValues.Content FROM TicketCustomFieldValues”;
$conditions = " WHERE (TicketCustomFieldValues.CustomField = 9) AND
(TicketCustomFieldValues.Content IS NOT NULL)";
$group_by = ’ GROUP BY TicketCustomFieldValues.Content ';
$order_by = ’ ORDER BY TicketCustomFieldValues.Content ';

$sql = “SELECT COUNT(Tickets.Id) AS Created FROM Tickets,
TicketCustomFieldValues”;
$conditions = " WHERE (Tickets.Id = TicketCustomFieldValues.Ticket) AND
(Tickets.Id = Tickets.EffectiveId) AND
(TicketCustomFieldValues.CustomField = 9) AND
(TicketCustomFieldValues.Content = ‘{$vars[‘department’]}’) AND
(Tickets.Id IS NOT NULL) AND (" . $this->exclude_rt_queues(‘id’) . ") AND ";
$group_by = “”;

$sql = “SELECT SUM(CASE WHEN Tickets.Status = ‘resolved’ THEN 1 ELSE 0
END) AS Resolved FROM Tickets, TicketCustomFieldValues”;
$conditions = " WHERE (Tickets.Id = TicketCustomFieldValues.Ticket) AND
(Tickets.Id = Tickets.EffectiveId) AND
(TicketCustomFieldValues.CustomField = 9) AND
(TicketCustomFieldValues.Content = ‘{$vars[‘department’]}’) AND
(Tickets.Id IS NOT NULL) AND (" . $this->exclude_rt_queues(‘id’) . ") AND ";
$group_by = “”;

Mike Patterson
Systems Manager
UC Berkeley Extension

I think I figured it out.

TicketCustomFieldValues -> ObjectCustomFieldValues

and

TicketCustomFieldValues.Ticket -> ObjectCustomFieldValues.ObjectId

This solves all the issues I’m experiencing.

Thanks,
Mike

Mike Patterson
Systems Manager
UC Berkeley Extension

At Thursday 5/11/2006 07:09 PM, Mike Patterson wrote:

I just came across another snag in my migration (3.4.2 -> 3.4.5) migration

slap my wrist for not using RT’s API…
however we already have a bunch of reports calling on the mysql
database table TicketCustomFieldValues and I don’t want overhaul all
those pages just yet.

Can I simply substitute the table name ObjectCustomFieldValues for
TicketCustomFieldValues in our non-api aware code or is there
something more complicated that I need to be aware of?

Browsing around the database it looks fairly similar…

Thanks,
Mike

some code snippets below:

$sql = “SELECT TicketCustomFieldValues.CustomField,
TicketCustomFieldValues.Content FROM TicketCustomFieldValues”;
$conditions = " WHERE (TicketCustomFieldValues.CustomField = 9) AND
(TicketCustomFieldValues.Content IS NOT NULL)";
$group_by = ’ GROUP BY TicketCustomFieldValues.Content ';
$order_by = ’ ORDER BY TicketCustomFieldValues.Content ';

You’ll need to add

AND ObjectCustomFieldValues.ObjectType = ‘RT::Ticket’

to your queries to make sure you only get ticket CF values.

Steve