How to get a custom collection based on a data that is most repeated (from top to bottom))-CodePudding

I have a table like this:

Basically this table is named favourite_products and contains product ids that are added as favourite for users.

Now I wanted to get a collection of most added product from this table.

So in this case, a product with an id of 10 would be on top of the collection.

But I don't know how to get this collection ordering by from most repeated product id (prd_id)...

So if you know, please help me out.

Here is the Model:

class FavouriteProduct extends Model
{
    protected $table = 'favourite_products';
    protected $fillable = ['usr_id','prd_id'];
    
    public function user()
    {
        return $this->belongsTo(User::class, 'usr_id');
    }

    public function product()
    {
        return $this->belongsTo(Product::class, 'prd_id');
    }
}

CodePudding user response：

try use this

public function example()
{
    $data=FavouriteProduct::orderBy('prd_id', 'ASC')->get();
    dd($data);
}

CodePudding user response：

I think the following code solve your problem:

$most_liked_products = DB::table('favourite_products')
    ->select(DB::raw('count(prd_id) as total'), id)
    ->groupBy('total')
    ->orderByDesc('total')
    ->get();

Please try it and give your feedback