CODEWITHSUNDEEP
laraveleloquent
Mr Joe
Mr Joe

Reputation: 131

Eloquent to count with different status

It's possible to make one query to get total, sold & unsold in laravel eloquent?

$total_apple  = Item::whereName('Apple')->count();
$sold_apple   = Item::whereName('Apple')->whereStatus(2)->count();
$unsold_apple = Item::whereName('Apple')->whereStatus(1)->count();

Upvotes: 2

Views: 1414

Answers (3)

Thomas Kim
Thomas Kim

Reputation: 15951

There is no need run multiple queries or even fetch the entire results and use collection methods to loop through. Just use raw queries.

$apples = Item::whereName('Apple')
              ->selectRaw('COUNT(*) as total_apples,
                           SUM(status=2) as sold_apples,
                           SUM(status=1) as unsold_apples')
              ->first();

echo $apples->total_apples;     // Outputs total apples
echo $apples->unsold_apples;    // Outputs the unsold apples
echo $apples->sold_apples;      // Outputs the sold apples

Since you are only doing simple counts though, you can use the query builder as well.

Upvotes: 3

Milan Maharjan
Milan Maharjan

Reputation: 4246

Yes you can totally do that. You can use filter method on collection object returned by your Eloquent query.

$apples = Item::whereName('Apple')->get();

$soldApples = $apples->filter(function ($apple){
     return $apple->status == 2;
});

$unsoldApples = $apples->filter(function ($apple){
     return $apple->status == 1;
});

$soldApples and $unsoldApples contains the object of the items. You can then just use count($soldApples) and count($unsoldApples) to get their count.

filter method is against the collection object so there is no sql overhead.

Upvotes: 3

Mina Abadir
Mina Abadir

Reputation: 2981

I would get all the items in one collection, then run the where statement on that collection. This should trigger a single Query.

$apples       = Item::whereName('Apple')->get();  // This goes against SQL
$total_apple  = $apples->count();          //This runs on the Collection object not SQL
$sold_apple   = $apples->whereStatus(2)->count();
$unsold_apple = $apples->whereStatus(1)->count();

Upvotes: 1

Related Questions