Count rows from query laravel

Question

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');
}

does count($returned) not work? are you able to somehow add count(*) as total into the query and then $return['total'] will have your total? — Joshua
– Joshua, Commented Oct 5, 2018 at 0:00
I believe I could definately do the count(*) as total. Then would I have to write a new query for each of the statuses — Josh
– Josh, Commented Oct 5, 2018 at 0:56
so try $count = count($result); on the result. I'd be surprised if it's not returned in an array or countable object. — Joshua
– Joshua, Commented Oct 5, 2018 at 0:58

Josh · Accepted Answer · 2018-10-06 03:16:11Z

1

$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();

answered Oct 6, 2018 at 3:16

Josh

392 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joshua · Accepted Answer · 2018-10-05 01:22:41Z

0

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();

answered Oct 5, 2018 at 1:22

Joshua

1,1264 gold badges17 silver badges32 bronze badges

2 Comments

Josh Over a year ago

Still returns a count for just the query leaving me to write 16 queries to achieve what I was looking for. I am looking to count how many with a value of 1 in a column then a count for each status. Then the same for value 2 and so on. So the table holds medications. I want to count how many of each med carried is each status so I want to know that this medication we have 10 total and 2 are in boxes 6 are in the safe 4 are destroyed.

Joshua Over a year ago

You may be able to try a subquery but that could still be rather long and not sure how that works with laravel models.

Collectives™ on Stack Overflow

Count rows from query laravel

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related