Return unified JSON of many to many relationship in Laravel

I can’t wrap my head around this. I have three tables (abbreviated):

*images*
id | label | …

*keywords*
id | label | …

*image_keyword*
image_id | keyword_id

And I have their many to many relations defined in the models:

*Image.php*
public function keywords()
{
    return $this->belongsToMany(Keyword::Class);
}

*Keyword.php*
public function images()
{
    return $this->belongsToMany(Image::Class);
}

At one point I implement a lazy fuzzy search via this code:

$terms = explode(' ', $request->input('q'));
$query = DB::connection('pia')->table('images');

foreach($terms as $k => $term) {
    $query->where(DB::raw('lower(images.title)'), 'like', '%' . strtolower($term) . '%');
}

return response()->json($query->get());

That works fine for the moment. Now I would love to add the keywords to the image objects that I’m passing back to the frontend, but I don’t manage to produce a working solution. I’m not an expert concerning database design and code.

I would love to have the keywords just passed with the image object, like this:

{
    'id': 123,
    'label': 'Dog',
    'file': '/data/images/dog.jpg',
    'keywords': [
        {
            'id': 312,
            'label': 'Animal'
        },
        {
            'id': 313,
            'label': 'Pet'
    ],
}

I tried joins but couldn’t produce any working results.

Answer

I think you have two solutions for this

1- you can try joins like so

$query = DB::connection('pia')->table('images');
$query->select('images.*','keywords.id,label')      
        ->join('image_keyword','images.id','image_keyword.image_id')
        ->leftJoin('keywords','image_keyword.keyword_id','keywords.id');
foreach($terms as $k => $term) {
    $query->where(DB::raw('lower(images.title)'), 'like', '%' . strtolower($term) . '%');
};
return response()->json($query->groupBy(images.id)->get());
        

2- the easiest solution is using laravel eloquent model like so

$query = Image::with('keywords:id,label');
foreach($terms as $k => $term) {
    $query->where(DB::raw('lower(images.title)'), 'like', '%' . strtolower($term) . '%');
}

return response()->json($query->get());