Question 344008 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel Query Builder: get all has many related row in key value format in side main row

Product table

"id",
"sort_order",
"name",
"visible",
"description",

Product Property table

"product_id",
"key_name",
"key_value"

The response I need from the query builder

[{
"id": 1,
"sort_order": 1,
"name": "Samosas",
"visible": 1,
"description": null,
"property": {
    "{key_name}": "{key_value}",
    "{key_name}": "{key_value}",
    "{key_name}": "{key_value}",
}
}]

or atleast i can achive it like elequent with relation

[{
"id": 1,
"sort_order": 1,
"name": "Samosas",
"visible": 1,
"description": null,
"property": [
    {
        "product_id": 1,
        "key_name": "categories",
        "key_value": "Starter"
    },
    {
        "product_id": 1,
        "key_name": "print_order",
        "key_value": "1"
    }

]}]

i tried in elequent it can be done but for query builder how to achive that form sql side?

i tried by joining them:

DB::('products')->join('product_properties', 'product.id', '=', 'product_properties.product_id')->get()

Thanks

Tags:

2

Answers


  1. 0

    You can achieve this by first fetching the main rows, then iterating over them to fetch and format the related rows.

    $results = DB::table('products')->get();

foreach ($results as $result) {
    $properties = DB::table('product_properties')
        ->where('product_id', $result->id)
        ->pluck('key_value', 'key_name');

    $result->property = $properties;
}

return $results;

    Or you can eager load:

    $products = AppModelsProduct::with('properties')->get();

$products->transform(function ($product) {
    $properties = $product->properties->pluck('key_value', 'key_name');
    $product->property = $properties;
    unset($product->properties);
    return $product;
});

return $products;
    Login or Signup to reply.
  2. 0

    Why your joining query is weird?, sometime it’s "products", sometime it’s "product"

    DB::('products')->join('product_properties', 'product.id', '=', 'product_properties.product_id')->get()
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search