mysql query results on 1 row with GROUP_CONCAT?

Question

I'm not an experienced programmer so please go easy with me! And I'm not sure if what I want is possible. I may need to provide some more info.

We have a simple ticket system built in a Joomla website and are changing to another platform. I need to extract data into a spreadsheet from the existing site (for history/reference). The mysql db has the ticket data across 6 tables.

I can output all of this data, but I end up with multiple rows in my spreadsheet as the query iterates through the data. 2 field are giving me hassle - messages and custom fields - each of these live in their own table with an index to the ticket to which they belong. Messages are like a conversation in the ticket, so there may be 1 or lots. Custom fields vary by type of ticket - each has a label and field for its data - so if there are 2 messages and 3 custom fields I end up with 6 rows, if there are 1 and 3 I end up with 3 etc. From 311 existing tickets I have a spreadsheet with 3270 results - which is unusable! I want to end up with all data for each ticket on 1 row.

I found the GROUP_CONCAT function yesterday. This has reduced the output to 24 rows - different to 3270 but not right and still unusable!! What is happening is that messages from many tickets are being concatenated into a single cell in the output, where I only want tickets from each ticket concatenated. I also want to concatenate the custom fields for the ticket but haven't looked at that part yet.

This is my sql...

SELECT ROW_NUMBER() OVER (
    ORDER BY sgj4z_rsticketspro_tickets.id
    ) 'Row number',
    sgj4z_rsticketspro_tickets.id AS 'ID',
    DATE_FORMAT (sgj4z_rsticketspro_tickets.date,"%d/%m/%Y") AS 'Date raised',
    sgj4z_users.name AS 'Raised by',
    sgj4z_rsticketspro_tickets.code AS 'Code',
    sgj4z_rsticketspro_tickets.subject AS 'Subject',
    sgj4z_rsticketspro_statuses.name AS 'Status',
    sgj4z_rsticketspro_priorities.name AS 'Priority',
    GROUP_CONCAT(sgj4z_rsticketspro_ticket_messages.message SEPARATOR '  |  ') AS 'Messages',
    sgj4z_rsticketspro_custom_fields.label AS 'Custom field name',
    sgj4z_rsticketspro_custom_fields_values.value AS 'Value',
    DATE_FORMAT (sgj4z_rsticketspro_tickets.closed,"%d/%m/%Y") AS 'Date closed'
FROM
    (
    sgj4z_rsticketspro_tickets
    INNER JOIN sgj4z_users ON sgj4z_rsticketspro_tickets.customer_id = sgj4z_users.id
    INNER JOIN sgj4z_rsticketspro_statuses ON sgj4z_rsticketspro_tickets.status_id = sgj4z_rsticketspro_statuses.id
    INNER JOIN sgj4z_rsticketspro_priorities ON sgj4z_rsticketspro_tickets.priority_id = sgj4z_rsticketspro_priorities.id
    INNER JOIN sgj4z_rsticketspro_custom_fields_values ON sgj4z_rsticketspro_tickets.id = sgj4z_rsticketspro_custom_fields_values.ticket_id
    INNER JOIN sgj4z_rsticketspro_ticket_messages ON sgj4z_rsticketspro_tickets.id = sgj4z_rsticketspro_ticket_messages.ticket_id
    INNER JOIN sgj4z_rsticketspro_custom_fields ON sgj4z_rsticketspro_custom_fields_values.custom_field_id = sgj4z_rsticketspro_custom_fields.id
    )
WHERE NOT 
    (
    sgj4z_rsticketspro_ticket_messages.user_id = "-1"
    )
GROUP BY sgj4z_rsticketspro_custom_fields.label;

Can I have GROUP_CONCAT concatenate the messages for each ticket separately from the other tickets - how?

Thanks, Nige

Answer 1

Instead of

INNER JOIN sgj4z_rsticketspro_ticket_messages ON sgj4z_rsticketspro_tickets.id =
        sgj4z_rsticketspro_ticket_messages.ticket_id`

you can use

INNER JOIN (
    select 
        ticket_id
        , group_concat(message  SEPARATOR ' | ') as message
    from sgj4z_rsticketspro_ticket_messages
    group by ticket_id
) as ticketwise_messages ON 
sgj4z_rsticketspro_tickets.id =
        ticketwise_messages.ticket_id

and replace

GROUP_CONCAT(sgj4z_rsticketspro_ticket_messages.message SEPARATOR ' | ') AS 'Messages',

with

ticketwise_messages.message as 'Messages'

Hope this helps. Do the same with custom fields table as well.