Anybody help me to resolve my problem. After upgrade from 3.2.10 to 3.3.5, my SQL command does not work. What was changed in DB? It was working good in 3.2.10.
My SQL is;
Code: Select all
SELECT (SELECT GROUP_CONCAT(dfv.value_text)
FROM dynamic_field_value dfv
WHERE dfv.id = t.id
) dynamic_field_values , t.id ticket_id, t.tn ticket_number, t.title ticket_title, t.queue_id, q.name queue_name, t.ticket_lock_id, tl.name ticket_lock_name, t.type_id, tt.name type_name,
t.service_id, s.name service_name, t.sla_id, sl.name sla_name, t.user_id, u.login user_name, t.responsible_user_id, u_resp.login responsible_user_name,
t.ticket_priority_id, tp.name ticket_priority_name, t.ticket_state_id, ts.name ticket_state_name, tst.id ticket_state_type_id, tst.name ticket_state_type_name, t.customer_id, t.customer_user_id, t.timeout, t.until_time, t.escalation_time, t.escalation_update_time,t.escalation_response_time,
t.escalation_solution_time, t.valid_id, v.name valid_name, t.archive_flag, t.create_time_unix create_time, t.create_by, ucr.login create_by_name,
t.change_time, t.change_by, uch.login change_by_name,
(
SELECT UNIX_TIMESTAMP(ai.create_time)
FROM article ai
INNER JOIN article_type ait ON ait.id = ai.article_type_id
WHERE ai.ticket_id = t.id
and ai.article_type_id IN (1,10)
ORDER BY ai.id
LIMIT 1
) first_response_time,
(
SELECT UNIX_TIMESTAMP(thi.create_time)
FROM ticket_history thi
INNER JOIN ticket_state tsi ON tsi.id = thi.state_id
INNER JOIN ticket_state_type tsti ON tsi.type_id = tsti.id
WHERE thi.ticket_id = t.id
AND thi.history_type_id = 27
AND tsti.id = 3
ORDER BY thi.id DESC
LIMIT 1
) close_time
FROM ticket t
INNER JOIN queue q on t.queue_id = q.id
INNER JOIN ticket_lock_type tl on tl.id = t.ticket_lock_id
INNER JOIN ticket_type tt ON tt.id = t.type_id
LEFT OUTER JOIN service s ON s.id = t.service_id
LEFT OUTER JOIN sla sl ON sl.id = t.sla_id
INNER JOIN users u ON u.id = t.user_id
INNER JOIN users u_resp ON u_resp.id = t.user_id
INNER JOIN ticket_priority tp ON tp.id = t.ticket_priority_id
INNER JOIN ticket_state ts ON ts.id = t.ticket_state_id
INNER JOIN ticket_state_type tst ON tst.id = ts.type_id
INNER JOIN valid v ON v.id = t.valid_id
LEFT OUTER JOIN users uch ON uch.id = t.change_by
LEFT OUTER JOIN users ucr ON ucr.id = t.create_by
ORDER BY t.id DESC