[FIXED] how to retrieve limited number of related model and sort collection by related model in laravel?

Issue

I have 3 model Shop model, Product model, Picture model

I want to retrieve a collection of shops with last 3 Product model with their pictures and sort my collection based on newest product. I tried leftjoint and joint in laravel 6 to be able to sort the result but i get all shops`product (i only need last 3 product for each shop),

when I use joint I cant retrieve product pictures I also have tried “with” method in laravel , I couldnt sort the result based on product.creatred_at and also i get all related product in this method too.(as i mentioned i need the last 3 product)

class Shop extends Model
{
    public function products()
    {
        return $this->hasMany('App\Product');
    }
}

class Product extends Model
{

    public function shop()
    {
        return $this->belongsTo('App\Shop');
    }

    public function pictures()
    {
        return $this->morphMany('App\hPicture', 'pictureable');
    }

}


Shop::select('shops.*', 'products.id', 'products.shop_id', 'products.name as pname', 'products.user_id', 'products.code', 'products.price')
            ->with(['pictures', 'products.pictures'])
            ->leftjoin('products', function ($leftJoin) {
                $leftJoin->on('shops.id', '=', 'products.shop_id');
            });
            $dataList = $dataList->orderBy($field, $order);
            $dataList = $dataList->paginate(5)->appends(['sortField' => $field, 'sortOrder' => $order]);

the table layout for product and shop model is:

 Schema::create('shops', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->string('slug');
        $table->string('phone')->nullable();
        $table->string('address')->nullable();  
        $table->timestamps();
        $table->string('description')->nullable();
        $table->uuid('uuid');
    });
 Schema::create('products', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('shop_id')->unsigned();
            $table->foreign('shop_id')->references('id')->on('shops');
            $table->string('name');
            $table->string('code');
            $table->string('slug');
            $table->integer('price');
            $table->uuid('uuid');
            $table->timestamps();
        });

Solution

There are only 2 ways of resolving this:

1. Either you pull in all products, and trim them in the end(advisable only if not too many products per shop):

$shops = Shop::with(['products' => function($subQuery) { 
    $subQuery
        ->with('pictures') //Running in scope of product, also load each product's pictures
        ->orderBy('created_at', 'desc');
}])
->get();
foreach ($shops as $shop) {
   $shop->setRelation('products', $shop->products->take(3));
}

NOTE:

You will load every single product that is linked to the shops you load. You could get memory issues with this.

2. Take only what you need, but introduce a n+1 query issue(advisable only with small quantities of $shops:

$shops = Shop::get();
foreach ($shops as $shop) {
    $shop->load([
        'products' => function($query) {
            $query
                ->orderBy('created_at', 'desc')
                ->limit(3)
                ->get();
    }]);
}

NOTE:

N+1 query problem: You are performing a new query for each shop, so if you have a million shops, it will be a million extra queries.

EDIT: (answering comment question)

Q: How can i sort $shops based on their latest product created_at field?

$sortedShops = $shops->sortBy(function ($shop, $key) {
    return $shop->products->first()->created_at;
})->values()->all();

sortBy is called on the collection(not uery). It allows you to go over each element(in this case shops) and use each object. Please do note that this function will fail if you have no products linked to the shop.

The ->values()->all() at the end makes sure that when you convert your shops to json, you will create an array, and not an object in js.

Source: https://laravel.com/docs/7.x/collections#method-sortby

EDIT: (deleted original answer as it did not work)

• Previous answer does not work, because limit(3) will limit the total amound of products loaded, in stead of 3 products per shop(my bad).

Answered By - Techno

Answer Checked By - Dawn Plyler (PHPFixing Volunteer)