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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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());


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x