CODEWITHSUNDEEP
phplaravel-5eloquent
Josh
Josh

Reputation: 39

Count rows from query laravel

Web View

I am trying to count the total for each medication listed and break it down by the total of each medication and then total by location. All of this data is in one table. The controller I am using to get each medication is below. I am trying to determine if I have to write a query for each tally I am looking for, or do I need to do a php count function for each, or does laravel have something to make this less time consuming?

enter code here public function index()
{
    $medications = ControlledSubstances::with('Medications', 'Locations')->paginate('10');

    $rx = Medications::where('controlled', '1')->get()
                ->keyBy('id')
                ->map(function ($rx){
                    return"{$rx->trade_name}  -  {$rx->brand_name}";
                });

    $cs = Medications::get();

    $nb = NarcoticBoxes::get()
                ->keyBy('id')
                ->map(function ($nb){
                    return"{$nb->box_number}";
                });

    $status = VialStatus::get()
                ->keyBy('id')
                ->map(function ($status){
                    return"{$status->label}";
                });

    return view('logistics.controlled', compact('medications', 'rx', 'nb', 'status', 'cs'))->with('success', 'New Controlled Substance Added');
}

Upvotes: 1

Views: 9663

Answers (2)

Josh
Josh

Reputation: 39

$mcount = ControlledSubstances::
            select('medication', DB::raw('count(*) as count'), DB::raw('count(IF(status = 3,1,NULL)) safe'), DB::raw('count(IF(status = 4,1,NULL)) box'), DB::raw('count(IF(status = 8,1,NULL)) destroyed') ) 
            ->groupBy('medication')
            ->get();

Upvotes: 1

Joshua
Joshua

Reputation: 1126

Quoting from the Laravel 5 Docs:

$price = DB::table('orders')
    ->where('finalized', 1)
    ->avg('price');

Then change avg('price') to count() will give you the count.

Alternatively:

$users = DB::table('users')
    ->select(DB::raw('count(*) as user_count, status'))
    ->where('status', '<>', 1)
    ->groupBy('status')
    ->get();

Upvotes: 0

Related Questions