Join tables with multiple rows in Laravel-CodePudding

This is what my products table looks like,

id | name | price
-----------------
1  | soap | 20.00

This is what my product_images table looks like,

id | product_id | image_url
---------------------------
1  |     1      |   http://someurl
2  |     1      |   http://someurl
3  |     1      |   http://someurl

As you can see each product has many images. I want to get this data but the product_images should be an array like this,

What I'm expecting,

   [
    {"id": 1,"name": "soap","price": 20.00, 
     [
      {"id": 1,"image_url": "http://someurl"},
      {"id": 2,"image_url": "http://someurl"},
      {"id": 3,"image_url": "http://someurl"}
     ]
    }
   ]

But instead, I'm getting one record at one time with duplicates of the product. This is the way I tried that,

 return DB::table('products')
   ->join('product_images', 'product_images.product_id', 'products.id')
   ->get();

CodePudding user response：

Use laravel Eloquent: Relationships

First, create two models Product and ProductImage using php artisan command .Then add relationship one to many to product images

 php artisan make:model Product

and

php artisan make:model ProductImage

the above command will create two models in following path

app/Models/

so in model

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    use HasFactory;
    
    public function productImages(){
        
        return $this->hasMany(ProductImage::class);
    }
}

So your query will look like this

 $products=\App\Models\Product::with('productImages')->get();