order by not working on query where exists pivot table laravel 8

I have three table like this :

  • tbl_product
    • product_id
  • users
    • id
  • user_product
    • id
    • product_id
    • user_id

It is a ManyToMany relationship.

Models Product:

Product_user(){
    return $this->belongsToMany(User::class, 'user_product', 'product_id', 'user_id');
}

Models User:

User_product(){
    return $this->belongsToMany(Product::class, 'user_product', 'user_id', 'product_id');
}

Query select top like product by user:

$toplike = Product::with('Product_user')
    ->whereExists(function ($query){
        $query->select('product_id', DB::raw('count(product_id) as total'))
            ->from('user_product')
            ->whereColumn('user_product.product_id', 'tbl_product.product_id')
            ->groupBy('product_id')
            ->orderBy('total', 'desc')
            ->take(10);
        })
    ->get();

I get list product, but order by total not working, List product sorting by product_id asc.

Please, help me.

Answer

That’ll filter the relation instead of just the existence check:

$toplike = Product::with(['Product_user' => function ($query){
    $query->select('product_id', DB::raw('count(product_id) as total'))
        ->from('user_product')
        ->whereColumn('user_product.product_id', 'tbl_product.product_id')
        ->groupBy('product_id')
        ->orderBy('total', 'desc')
        ->take(10);
    })])
->whereExists(function ($query){
    $query->select('product_id', DB::raw('count(product_id) as total'))
        ->from('user_product')
        ->whereColumn('user_product.product_id', 'tbl_product.product_id');
    })
->get();