CODEWITHSUNDEEP
phpmysqllaraveleloquentlaravel-5
BenNov
BenNov

Reputation: 1111

PHP - Laravel - Getting average directly from Eloquent and not having to loop through the results

I'm building a survey platform and I need to get the average answer rate of the survey. What I'm currently doing is retrieving all the questions and then dividing times viewed and times answered. Is there a more efficient / less resource consuming method by calculating average on the DB and not looping through thousands of results?

Here is my working code right now that takes forever:

    $total_showed = 0;
    $total_answered = 0;

    $total_queries = Query::where('client_app_id','=', $app_id)->get();

    foreach ($total_queries as $app_query) {
         $total_showed = $total_showed + $app_query->showed;
         $total_answered = $total_answered + $app_query->answered;
    }
    if ($total_showed > 0) {
        $total_arate = round(($total_answered / $total_showed) * 100, 1);
    } else {
        $total_arate = 0;
    }

Upvotes: 0

Views: 590

Answers (3)

Farkie
Farkie

Reputation: 3337

Sure you can go into Raw SQL:

instead of:

$total_queries = Query::where('client_app_id','=', $app_id)->get();

use something like:

$total_queries = Query::select(DB::raw('SUM(showed) as counter, SUM(answered) as answered'))
              ->where('client_app_id','=', $app_id)->get();

Upvotes: 1

narayansharma91
narayansharma91

Reputation: 2353

try this aggregate function avg(); like this 

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

Upvotes: 0

Sherif
Sherif

Reputation: 1546

try $total_showed = $total_queries->sum('showed') $total_answered = $total_queries->sum('answered')

since $total_queries is a collection you can use it's sum method see https://laravel.com/docs/5.3/collections#method-sum this would be mre efficient I think

Upvotes: 1

Related Questions