How to join two tables and get JSON data of all rows

Question

I have a 2 tables units table and unit_images table

units

id | unit_types | price
------------------------
1  | Apartment  | 100
2  | House      | 200

unit_images

id | unit_id  | image_name
------------------------
1  | 1        | image1.jpg
2  | 1        | image2.jpg
3  | 2        | image3.jpg
4  | 2        | image4.jpg

How can I achieve this result when I display on the div?

<div>
    <p>Apartment</p>
    <p>100</p>
    <p>image1.jpg</p>
    <p>image2.jpg</p>
</div>

<div>
    <p>House</p>
    <p>200</p>
    <p>image3.jpg</p>
    <p>image4.jpg</p>
</div>

This is what i've tried

index.blade.php

        var apiUrl = 'view';
        $.ajax({
            type: "GET",
            url: apiUrl,
            success: function(response) {   
             
                let unit_types_result = response.unit_types.map((result) => {
                    let images_result = response.image.map((result1) => {
                        return `
                            <p>${result1.image_name}"</p>`
                    });
                    return `
                            <div>
                                    <p>${result.unit_types}</p>
                                    <p>${result.price}</p>
                                    <p>${images_result}</p>
                            </div>`
                });
                unit_types_div.append(unit_types_result);
            }
        });

ApiController.php

    public function view()
    {
        $unit_types = DB::table('units')
            ->join('unit_images', 'units.id', '=', 'unit_images.unit_id')
            ->distinct()
            ->select('units.unit_types')
            ->get();
        $img = DB::table('units')
            ->join('unit_images', 'units.id', '=', 'unit_images.unit_id')
            ->get();
        return response()->json([
            'status' => true,
            'image' => $img,
            'unit_types' => $unit_types,
        ]);
    }

On this source code I can display the Apartment and House on separate div but the problem is the 4 images display on each div.

Answer 1

First thing first, you need to create model for each of your table.
And inside each of model class you declare the relationship.

// App\Models\Unit
class Unit extends Model
{
   public function images() 
   {
      return $this->hasMany(UnitImage::class);
   }
}

// App\Models\UnitImage
class UnitImage extends Model
{
   public function unit()
   {
      return $this->belongsTo(Unit::class);
   }
}

And inside your controller now to call the query, use eloquent.

$unit_types = Unit::with('images')->get();
return response()->json([
   'status' => true,
   'unit_types' => $unit_types,
]);

And this is the result of $unit_types as Json

[
  {
    "id": 1,
    "unit_types": "Apartment",
    "price": 100,
    "images": [
      {
        "id": 1,
        "unit_id": 1,
        "image_name": "image1.jpg"
      },
      {
        "id": 2,
        "unit_id": 1,
        "image_name": "image2.jpg"
      }
    ]
  },
  {
    "id": 2,
    "unit_types": "House",
    "price": 200,
    "images": [
      {
        "id": 3,
        "unit_id": 2,
        "image_name": "image3.jpg"
      },
      {
        "id": 4,
        "unit_id": 2,
        "image_name": "image4.jpg"
      }
    ]
  }
]

And now you can populate your html using js ajax alot easier.

I hope this can help you out.