[SOLVED] Adding Dynamic Fields to Database Views

[interploy]

Hello,

I've been tasked with creating a postgreSQL database view for an odbc connection to pull some basic ticket data. I've been able to setup most of the data, but two fields I need to include are dynamic fields. I found the the dynamic_field and dynamic_field_value tables, but I can't figure out the relationship path between these and the ticket table.

One of the dynamic fields is a dropdown menu and the other is a checkbox. My guess is the object_id in the dynamic_field_value table refers to the tickets, but I'm not sure and my test queries haven't been successful so far. Sorry if this is trivial, I'm only moderately skilled with SQL (this is the most complex query I've put together). Any help you could give on how the tables are connected would be awesome.

Thanks,
-interploy



My view up to this point:

CREATE OR REPLACE VIEW helpdesk_view AS
SELECT t.id,
v.name AS validity,
q.name AS queue_name,
t.title,
t.ticket_answered,
y.name AS type,
u.login AS agent,
p.name AS ticket_priority,
s.name AS ticket_state,
t.customer_id AS customer,
t.create_time,
c.login AS change_by
FROM ticket t
INNER JOIN valid v ON t.valid_id = v.id
INNER JOIN queue q ON t.queue_id = q.id
LEFT OUTER JOIN ticket_type y ON t.type_id = y.id
INNER JOIN users u ON t.user_id = u.id
INNER JOIN users c ON t.change_by = c.id
LEFT OUTER JOIN ticket_priority p ON t.ticket_priority_id = p.id
LEFT OUTER JOIN ticket_state s ON t.ticket_state_id = s.id
ORDER BY id;

[interploy]

No thoughts on this at all? Is it even feasible...?

I'm in the process of upgrading to v3.2.3, though from my tests insofar it doesn't look like there's a direct relationship between the ticket table and the dynamic_fields table.

If there is no direct connection, what might the repercussions be if I created a relationship between the ticket and dynamic_field tables?

And if that's not possible, is there another way to setup the data to have custom fields related to the ticket table? If I added columns to the ticket table, what modules would need to be updated for the system to see the new columns?

Thanks in advance,
-interploy

[crythias]

I posted the relationship ... somewhere and forgot to bookmark it.
viewtopic.php?f=53&t=16009&p=77686#p77686

[interploy]

I posted the relationship ... somewhere and forgot to bookmark it.
viewtopic.php?f=53&t=16009&p=77686#p77686

This is just what I was trying to figure out. Thank you so much!

[interploy]

For anyone interested, this is the new view I ended up creating:

Code: Select all

CREATE OR REPLACE VIEW helpdesk_view AS
SELECT t.id,
       v.name AS validity,
       q.name AS queue_name,
       t.tn AS ticket_number,
       t.title,
       y.name AS type,
       u.login AS agent,
       p.name AS ticket_priority,
       s.name AS ticket_state,
       d1.value_int  AS atypon_bug,
       d2.value_text AS trending_category,
       t.customer_id AS customer,
       t.create_time,
       t.change_time,
       c.login AS change_by
FROM ticket t
INNER JOIN valid v ON t.valid_id = v.id
INNER JOIN queue q ON t.queue_id = q.id
LEFT OUTER JOIN ticket_type y ON t.type_id = y.id
INNER JOIN users u ON t.user_id = u.id
INNER JOIN users c ON t.change_by = c.id
LEFT OUTER JOIN ticket_priority p ON t.ticket_priority_id = p.id
LEFT OUTER JOIN ticket_state s ON t.ticket_state_id = s.id
LEFT JOIN (
	SELECT v.object_id,
	       v.value_int
	FROM dynamic_field_value v
	LEFT JOIN dynamic_field f ON v.field_id = f.id
	WHERE f.name = 'NAME_VALUE_1'
) d1 ON d1.object_id = t.id
LEFT JOIN (
	SELECT v.object_id, 
	       v.value_text
	FROM dynamic_field_value v
	LEFT JOIN dynamic_field f ON v.field_id = f.id
	WHERE f.name = 'NAME_VALUE_2'
) d2 ON d2.object_id = t.id
ORDER BY t.id;

[crythias]

Left join select? okay... :) Since there's only one value assigned per dynamic field (usually?) you probably could just join directly.

[interploy]

Left join select? okay... Since there's only one value assigned per dynamic field (usually?) you probably could just join directly.

I used separate selects so I would only get one row back for each ticket instead of two. I admit there's probably a less verbose way to do it, but this works for what I need.

[Navidlo]

Thank you guys.
It is very helpful.

Navidlo