'Querying only one row from a one to many relationship laravel

I have a products table and a product_pictures table, where a single product can have many pictures (one to many relationship). I am trying to display all of the product with all of its details (such as name, quantity, etc.) and only one of its product picture. This is what i have tried so far.

$products = Product::->join('product_pictures', 'products.product_id', '=', 'product_pictures.product_id')
        ->select('product_name', 'quantity', 'products.product_id', 'product_picture_id')
        ->groupBy('products.product_id')
        ->get();

I have also tried to use distinct to no luck. Any suggestions?

phpmysqllaraveleloquentlaravel-query-builder


Solution 1:[1]

You can use the with function. It will include a model's relation. So if you have a Product model, you can query Product's relationship .e.g Product -> ProductPicture.

Make sure you have the relationship set up in your Models. It would roughly be like this:

Product model:

public function pictures()
{
    return $this->hasMany('App\ProductPicture', 'product_id', 'id');
}

ProductPicture model:

public function product()
{
    return $this->belongsTo('App\Product', 'id', 'product_id');
}

The query with with function will look like:

$products = Product::with(['pictures' => function($q) {
    $q->take(1);
])->get();

It will get all products, and attach a picture (its relationsip) to that product.

Update: It turns out it will only get 1 picture from a product, and leave the other products' pictures empty. So the take function nested in a with will only work as expected if you're fetching a single parent using find.

What you can try is to define a new function in the Product model like so, it will only attach a single picture to the product:

public function singlePicture()
{
    return $this->hasMany('App\ProductPicture', 'product_id', 'id')->limit(1);
}

Call it in the query:

$products = Product::with(['singlePicture'])->get();

Solution 2:[2]

You can use GROUP_CONCAT() function of mysql You can also get list of all picture using single query like

DB::raw("GROUP_CONCAT(`product_picture_id` SEPARATOR ',') as product_picture_ids")

$products = Product::join('product_pictures', 'products.product_id', '=', 'product_pictures.product_id')
        ->select('product_name', 'quantity', 'products.product_id', DB::raw(" GROUP_CONCAT(`product_picture_id` SEPARATOR ',') as product_picture_ids"))
        ->groupBy('products.product_id')
        ->get();

Here you want to get only single field of product_picture_id, if you want multiple fields in single query, like children in json, You can refer https://romik-mk.medium.com/laravel-one-to-many-relation-in-single-query-5587697c259

Solution 3:[3]

$products = Product::join('product_pictures as pp', 'products.product_id', '=', 'pp.product_id')
    ->select('products.product_name', 'products.quantity', 'products.product_id', 'pp.product_picture_url')
    ->groupBy('products.id')
    ->get();

You need to groupBy products.id instead of product_id. It'll return only products which has product image with first product image. If you want products that haven't single product image you can use leftJoin

Hope this will work!!

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Romik Makavana
Solution 3

Related Questions