How to get array in array with laravel and mysql database?

I am using Laravel (PHP) and MySQL for my backend. I am creating methods for setting and getting information from the database. Those information are being send as a json to the frontend.

I can send table information like:

[
    {
        "id": 4,
        "name": "Max"
    },
    {
        "id": 5,
        "name": "Eric"
    }
]

For this I am using laravel methods like: DB::table('user')->select('user.id', 'user.name')->get();

However my friend who is doing the frontend want the following json code:

[
    {
        "id": 4,
        "name": "Max"
        "specific_user_price":{
                                "S":5.00,
                                "M":6.00,
                                "XL":8.00
                              }
    },
    {
        "id": 5,
        "name": "Eric"
        "specific_user_price":{
                                "S":5.50,
                                "M":10.00,
                                "XL":15.00
                              }
    }
]

“specific_user_price is a table and “S”, “M”, “XL” are columns which have different values depending on the user. I do not know how I can create specific_user_price as an array in a query. I can use group_concat but he needs the json like displayed above.

My idea was to create additional columns in user “size S price”, “size M price” and “size XL price”. However my friend want those values as an own array group, because some users only habe access to one size, so he would get null values.

Any ideas which method in PHP or Laravel I can use for that? Or is there a MySQL method for creating such thing in a query?

Answer

Firstly use Models, way easier to work with out of the box. Define your User model like this, with a relationship for the price.

use IlluminateDatabaseEloquentModel;

class User extends Model
{
    public function specificUserPrice() {
        return $this->hasOne(UserPrice::class);
    }
}

You also need to have the UserPrice model defined.

use IlluminateDatabaseEloquentModel;

class SpecificUserPrice extends Model
{
}

Laravel automatically transforms models, you can get away with the following code in the controller.

public function index()
{
    return User::with('specificUserPrice')->get();
}