SQL Statement dynamische Felder auswerten

[Misfitz]

Hallo zusammen,

ich habe ein kleines Problem. Ich hatte auf meinem alten OTRS 3.0.11 dieses SQL-Statement am laufen:

Code: Select all

SELECT DISTINCT t.tn, t.title, t.freetext1, t.freetext2, t.freetext3, t.freetext4, t.freetext5, t.freetext6, t.freetext7, t.freetext8, t.freetext9, t.freetext10, t.freetext11, t.freetext12, t.create_time, t.change_time, q.name, ts.name
FROM otrs.queue q, otrs.ticket t, otrs.ticket_state ts
WHERE q.id=t.queue_id AND t.ticket_state_id=ts.id AND t.create_time BETWEEN '2014-01-01 00:01:00' AND '2014-01-08 23:59:00%'
AND q.id NOT IN (1,5,19)
ORDER BY t.create_time;

Nun habe ich auf die Version 3.3.3 gewechselt und dort gibt es nun die Freetext-Felder nicht mehr in der Tabelle Ticket, diese wurden ja durch Dynamische Felder ersetzt. Ich habe leider ganz ganz wenig Erfahrung mit MySQL und daher die Frage an Euch: Wie bekomme ich nun diese Dynamischen Felder in mein SQL-Statement?

Was ich schon gesehen habe gibt es dort "BLOB-Dateien" in den dynamic_field und dynamic_field_value Tabellen.

Ich weiß aber wirklich nicht, wie ich mit einem SQL die Tickets mit den ganzen Spalten zusammenbauen kann.

[Misfitz]

Hallo nochmals,

wie kann ich die Tabellen dynamic_field und dynamic_field_value mit der Tabelle Ticket verknüpfen, damit mir dann die

Aus Tabelle Ticket:
t.tn, t.title, t.create_time, t.change_time

Aus Tabelle Queue:
q.name

Aus Tabelle ticket_state:
ts.name

Aus Tabelle dynamic_field d oder(und) dynamic_field_value dv:
d.Dynamic_Field_Field1, dv.Dynamic_field_value_Field1, d.Dynamic_Field_Field2, dv.Dynamic_field_value_Field2 usw.

[chfr]

Ich habe erst vor kurzem meine Auswertungen anpassen müssen, hier mal ein Beispiel:

Code: Select all

SELECT 'Ticketnummer', 'Erstellungszeit', 'PRIO', 'Titel', 'Besitzer', 'Verantwortlicher', 'Queue', 'Typ',
'letzte Aktion am', 'Status', 'Service', 'Standort', 'User'
UNION
SELECT ticket.tn, ticket.create_time, ticket_priority.name, ticket.title, us1.login, us2.login, queue.name, ticket_type.name,
ticket.change_time, ticket_state.name, service.name, ticket.customer_id, ticket.customer_user_id
FROM ticket
INNER JOIN queue on queue.id = ticket.queue_id
LEFT JOIN service on service.id = ticket.service_id
LEFT JOIN ticket_type on ticket_type.id = ticket.type_id
LEFT JOIN ticket_state on ticket.ticket_state_id = ticket_state.id
LEFT JOIN users us1 on us1.id = ticket.user_id
LEFT JOIN users us2 ON us2.id = ticket.responsible_user_id
LEFT JOIN Ticket_priority ON Ticket_priority.id = ticket.ticket_priority_id
WHERE queue.id IN (1,4,5,15,23,37)
AND Ticket_state.id IN (1,4,6,7,8,12,14,15,16,17,18,19,20)

[Misfitz]

Hallo zusammen,

ich habe nun einen Weg gefunden, die Dynmaischen Felder mit MYSQL auszulesen. Da ich aber kein SQL bzw. OTRS Profi bin, könnte man es auch eventuell auf eine andere Art lösen. Diese Lösung von mir funktioniert aber soweit für meine Bedürfnisse:

Code: Select all

Select distinct
    t.tn AS 'Ticketnummer',
    t.title AS 'Tickettitel',
    t.create_time AS 'Erstelldatum',
    ts.name AS 'Ticketstatus',
    q.name AS 'Queue',
	(select coalesce(field_display, value_text) from dynamic_field join dynamic_field_value on dynamic_field.id = dynamic_field_value.field_id left outer join dynamic_fields_config on dynamic_fields_config.field_name = dynamic_field.name and dynamic_fields_config.field_value = value_text where object_id = t.id and name = 'DES DYNAMISCHEN FELD' ) as 'ALIAS DER ANGEZEIGT WERDEN SOLL',
	(select group_concat(coalesce(field_display, value_text) separator '; ') from dynamic_field join dynamic_field_value on dynamic_field.id = dynamic_field_value.field_id left outer join dynamic_fields_config on dynamic_fields_config.field_name = dynamic_field.name and dynamic_fields_config.field_value = value_text where object_id = t.id and name = 'DES DYNAMISCHEN FELD' ) as 'ALIAS DER ANGEZEIGT WERDEN SOLL',
    (select coalesce(field_display, value_int) from dynamic_field join dynamic_field_value on dynamic_field.id = dynamic_field_value.field_id left outer join dynamic_fields_config on dynamic_fields_config.field_name = dynamic_field.name and dynamic_fields_config.field_value = value_int[color=#FF0000][quote]HIER STEHT DAS VALUE_INT FÜR EINE CHECKBOX[/quote][/color] where object_id = t.id and name = 'DES DYNAMISCHEN FELD' ) as 'ALIAS DER ANGEZEIGT WERDEN SOLL',
	(select coalesce(field_display, value_text) from dynamic_field join dynamic_field_value on dynamic_field.id = dynamic_field_value.field_id left outer join dynamic_fields_config on dynamic_fields_config.field_name = dynamic_field.name and dynamic_fields_config.field_value = value_text where object_id = t.id and name = 'DES DYNAMISCHEN FELD' ) as 'ALIAS DER ANGEZEIGT WERDEN SOLL', 
	(select group_concat(coalesce(field_display, value_text) separator '; ') from dynamic_field join dynamic_field_value on dynamic_field.id = dynamic_field_value.field_id left outer join dynamic_fields_config on dynamic_fields_config.field_name = dynamic_field.name and dynamic_fields_config.field_value = value_text where object_id = t.id and name = 'DES DYNAMISCHEN FELD' ) as 'ALIAS DER ANGEZEIGT WERDEN SOLL',
	(select group_concat(coalesce(field_display, value_text) separator '; ') from dynamic_field join dynamic_field_value on dynamic_field.id = dynamic_field_value.field_id left outer join dynamic_fields_config on dynamic_fields_config.field_name = dynamic_field.name and dynamic_fields_config.field_value = value_text where object_id = t.id and name = 'DES DYNAMISCHEN FELD' ) as 'ALIAS DER ANGEZEIGT WERDEN SOLL'

from
    ticket t
		LEFT OUTER JOIN
	ticket_state ts ON t.ticket_state_id = ts.id
        LEFT OUTER JOIN
    dynamic_field d ON d.id = t.tn
        LEFT OUTER JOIN
    queue q ON q.id = t.queue_id
        AND t.create_time BETWEEN '2014-01-01 00:01:00' AND '2014-01-21 23:59:00%'

ORDER BY t.tn DESC

Das schwierige hierfür war aber, dass ich eine Hilfstabelle "dynamic_fields_config" erstellen musste um aus den Schlüsseln die Werte zu machen. Diese Tabelle besteht aus den Columns "id", "field_name", "field_value" und "field_display".
Die Hilfstabelle muss man dann mit den Werten der dynamischen Feldern befüllen. Hierzu diente mir dieses SQL Statement:

Code: Select all

insert into dynamic_fields_config(id, field_name, field_value, field_display) VALUES (fortlaufende ID Nummer, 'Name des Dynamischen Feld', 'Schlüssel des Dynamischen Feld', 'Wert des Dynamischen Feld')

Die Hilfstabelle muss man auch regelmässig bei Änderungen in den dynamischen Feldern mitanpassen (vll ein kleiner Nachteil).

Kleine Erklärung noch zum Code:

GROUP_CONCAT FÜR MULTISELECTFELDER:

Code: Select all

(select group_concat(coalesce(field_display, value_text) separator '; ') from dynamic_field join dynamic_field_value on dynamic_field.id = dynamic_field_value.field_id left outer join dynamic_fields_config on dynamic_fields_config.field_name = dynamic_field.name and dynamic_fields_config.field_value = value_text where object_id = t.id and name = 'DES DYNAMISCHEN FELD' ) as 'ALIAS DER ANGEZEIGT WERDEN SOLL',

Auswahlwerte werden in einer Zeile ausgegeben und mit ";" getrennt.

VALUE_INT für Checkboxen:

Code: Select all

(select coalesce(field_display, value_int) from dynamic_field join dynamic_field_value on dynamic_field.id = dynamic_field_value.field_id left outer join dynamic_fields_config on dynamic_fields_config.field_name = dynamic_field.name and dynamic_fields_config.field_value = value_int where object_id = t.id and name = 'DES DYNAMISCHEN FELD' ) as 'ALIAS DER ANGEZEIGT WERDEN SOLL',

Ich habe natürlich auch die Checkboxenm mit "Ja" für "1" und "Nein" für "0" mit in die Hilfstabelle mitaufgenommen. Damit nun ja oder nein angezeigt werden, muss man die "value_text" in eine "value_int" austauschen.

Vielleicht kann es ja jemand von Euch brauchen, der genauso wenig Ahnung von OTRS und MYSQL hat wie ich. (Hat mich eine Menge Gummibärchen gekostet )