CODEWITHSUNDEEP
laravel
Cath
Cath

Reputation: 43

Write multiple laravel query at once

im new to laravel, I just want to know if there's any way to efficiently rewrite this code?

$answer1 = SurveyAnswer::where('answer_1','=','1')->get()->count();
$answer2 = SurveyAnswer::where('answer_1','=','2')->get()->count();
$answer3 = SurveyAnswer::where('answer_1','=','3')->get()->count();
$answer4 = SurveyAnswer::where('answer_1','=','4')->get()->count();
$answer5 = SurveyAnswer::where('answer_1','=','5')->get()->count();

Upvotes: 1

Views: 75

Answers (3)

Mohamed Akram
Mohamed Akram

Reputation: 2117

You can easily do it with an agregate function with a case expression, since mysql doesnot support native pivoting functions Following is a sample, and try to rewrite according to your requirement and runt it against database directly, if it works, then you can use it with laravel raw sql.

 select id,
 sum(case when value = 1 then 1 else 0 end) ANSWER1_COUNT,
 sum(case when value = 2 then 1 else 0 end) ANSWER2_COUNT
from survey
group by answer

Upvotes: 0

Alexey Mezenin
Alexey Mezenin

Reputation: 163748

Get the data first:

$answers = SurveyAnswer::whereIn('answer_1', [1, 2, 3, 4, 5])->get();

Then count answers using loaded collection:

$answer1 = $answers->where('answer_1', 1)->count();
$answer2 = $answers->where('answer_1', 2)->count();
...

This code will generate just one DB query instead of five.

Upvotes: 1

user320487
user320487

Reputation:

Try this:

$matches = [1,2,3,4,5];

$answer = SurveyAnswer::whereId('answer_1', $matches)->groupBy('answer_1')->get();

Upvotes: 0

Related Questions