Concatenate rows in one table as one column in different table MYSQL

Question

I think fundamentally, I am not understanding how to accomplish joining 2 tables together using a related table, I need help.

1. I have one table called 'formoptionslist' it has 3 columns (id, name, active)
2. A relate table called 'formoptionslistformoptionsrelates' it has 3 columns(id, formoptionslist_id, formoptions_id)
3. and finally a 'formoptions' table that has 3 columns (id, name, value)

Table values are loosely represented as the following:

formoptionslist

id |   name   | active
---------------------
1  | statuses | 1

formoptionslistformoptionsrelates

id | formoptionslist_id | formoptions_id
------------------------------------
1  | 1                  | 1    
2  | 1                  | 2   
3  | 1                  | 3

formoptions

id | name                   | value
------------------------------------
1  | Successfully Purchased | success    
2  | Shipping Error         | shipping_error   
3  | Failed Payment         | failed_payment

What I am trying to build is the following in one query.

[{name: statues, 
     options:[
         {name: 'Successfully Purchased', value: 'success'},
         {name: 'Shipping Error, value: 'shipping_error'},
         {name: 'Failed Payment', value: 'failed_payment'}
     ]
}]

I using php and PDO to query mysql

What I have been able to do is generate something like:

[{"options":"1,2,3,"}]

using the following test query ( I'm just trying to understand the SQL at this point, no need to comment about how I am not using placeholders, etc.

$this->db->select(
    'GROUP_CONCAT(DISTINCT formoptions_id) as options'.self::from.self::formoptionslistformoptionsrelates
        .self::leftJoin.self::formoptions
        .self::on
        .self::formoptionslistformoptionsrelates.'.formoptions_id'
        .self::equals.self::formoptionslistformoptionsrelates.'.formoptions_id'
    .self::where.'formoptionslist_id = 1'
    .' GROUP BY formoptionslist_id'
);

The above prints out to the following:

SELECT GROUP_CONCAT(DISTINCT formoptions_id) as options 
FROM formoptionslistformoptionsrelates 
LEFT JOIN _formoptions ON formoptionslistformoptionsrelates.formoptions_id = formoptionslistformoptionsrelates.formoptions_id 
WHERE formoptionslist_id = 1 
GROUP BY formoptionslist_id

I know that fundamentally, what I am trying to accomplish is possible. But I am not sure how to get there. Any help is greatly appreciated.

Answer 1

Use JSON_OBJECT to create the objects, and JSON_ARRAYAGG() to combine the value within a group into an array.

Then join all 3 tables.

SELECT JSON_OBJECT(
        'name', l.name,
        'options', JSON_ARRAYAGG(
            JSON_OBJECT(
                'name', o.name,
                'value', o.value
            )
        )
    ) AS result
FROM formoptionslist AS l
LEFT JOIN formoptionslistformoptionsrelates AS ol ON l.id = ol.formoptionslist_id
LEFT JOIN formoptions AS o ON o.id = formoptions_id
WHERE l.id = 1
GROUP BY l.id

DEMO