Get Json array from multiple table

72 Views Asked by At

I have two tables in my database.

device_category('id_category', 'name_category');
device('id_device', 'name_device', 'category_device');

I have a DeviceController with a get() method:

public function get()
{
    $devices = Device::select('*')
                 ->join('device_category','device_category.id_category','=','device.category_device')
                 ->get();

    return response()->json($devices, 200);
}

And the JSON result is:

[
    {
        "id_category": 1,
        "name_category": "Phone",
        "id_device": 1,
        "name_device": "iPhone 10"
    },
    {
        "id_category": 1,
        "name_category": "Phone",
        "id_device": 2,
        "name_device": "iPhone XS"
    },
    {
        "id_category": 2,
        "name_category": "Computer",
        "id_device": 3,
        "name_device": "Lenovo xx"
    }
]

Then I group the result:

$devices = $devices->groupBy('name_category');
return response()->json($devices, 200);

And got this JSON:

{
    "Phone" : [
        {
            "id_category": "1",
            "id_device": 1,
            "name_device": "iPhone 10"
        },
        {
            "id_category": "1",
            "id_device": 2,
            "name_device": "iPhone XS"
        }
    ],
    "Computer" : [
        {
            "id_category": "2",
            "id_device": 3,
            "name_device": "Lenovo XX"
        }
    ]
}

I would like to have an array of categories with an array of device for each categorie, how can I do it to get this result ? Can I do it in one query ? or do I need to get categories and then set devices ?

[
    {
        "id_category": 1,
        "name_category": "Phone",
        "device" : [
            {
                "id_device": 1,
                "id_category": 1,
                "name_device": "iPhone 10"
            },
            {
                "id_device": 2,
                "name_device": "iPhone XS"
            }
        ]
    },
    {
        "id_category": 2,
        "name_category": "Computer",
        "device" : [
            {
                "id_device": 3,
                "name_device": "Lenovo XX"
            }
        ]
    }
]
0

There are 0 best solutions below