Which SQL for listing tickets with all their dynamic fields

Moderator: crythias

Post Reply
vincentb
Znuny newbie
Posts: 12
Joined: 30 Jan 2021, 07:51
Znuny Version: 6 community
Real Name: Vincent B.

Which SQL for listing tickets with all their dynamic fields

Post by vincentb »

Hello,

Which SQL request would allow me to list tickets with all the dynamic field values?
(something similar to what we obtain from OTRS search results when selecting CSV as the output, or when using the "statistics" feature)

- I know how to "SELECT * FROM tickets t", how to make JOINs to display queue names, status names and so on.
- I know how to make a JOIN to fetch some dynamic field value, e.g. to fetch "Country" df value:
...JOIN dynamic_field_value dfv ON t.id = dfv.object_id JOIN dynamic_field df ON (dfv.field_id = df.id AND df.object_type = 'Ticket') WHERE df.name = 'Country'...
- I know how to get programmatically all dynamic fields of one given ticket.

But I do NOT know how to fetch all the DFs at once using pure SQL, i.e. perhaps just with some smart JOINs (without using some external language to performi loop through every ticket).
Can you help?

(I use MySQL)

Thanks
shawnbeasley
Znuny Employee
Posts: 132
Joined: 13 Sep 2021, 09:38
Znuny Version: Znuny 6.3.x
Real Name: Shawn Beasley
Company: Znuny

Re: Which SQL for listing tickets with all their dynamic fields

Post by shawnbeasley »

I don't see the use-case.

If you know how to get all DFs for one ticket, why do you want to loop through all tickets using SQL?.

I'm sure it's doable, but then it's no longer an OTRS qusetion, because you already understand the structure.

You would have to do select for vaules.

select * from dynamic_field_value where object_id in (select id from ticket);

Not even close, but add your JOINS and you should be one step closer.
Post Reply