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
Which SQL for listing tickets with all their dynamic fields
Moderator: crythias
-
- 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
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.
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.