Getting average scores from objects in a collection

@twoarmtom

You can try with this-

$average = Model::avg('Question One');

"Model" will replace with your model name and "Question One" will be replaced with the actual column name.

And if it is already a collection

$average1 = $collection->avg('Question One');
$average2 = $collection->avg('Question Two');

Unfortunately, none of these work. To clarify, the collection is obtained by using ‘pluck’ to get the values of a column. But the values are saved as an object.

This is how the value of ‘question_answers’ is stored:

{
"Question One": "4",
"Question Two": "3",
"Question Three": "4",
"Questions Four": "2",
"Question Five": "1"
}

So the example above is the result of ‘pluck’ .

$collection = Model::where(‘user_id’, $id)->pluck(‘question_answers’);

// results are

[
{
"Question One": "4",
"Question Two": "3",
"Question Three": "4",
"Questions Four": "2",
"Question Five": "1"
},
{
"Question One": "4",
"Question Two": "3",
"Question Three": "4",
"Questions Four": "2",
"Question Five": "1"
},
{
"Question One": "4",
"Question Two": "3",
"Question Three": "4",
"Questions Four": "2",
"Question Five": "1"
}
]

Pluck returns an array by default. Maybe you can try something like this:

collect($collection)->avg('Question One')

Didn’t work :-(

Can you give bit more context. Did you get an error? Or is the result wrong?

Also please share the code that currently does not work.

The result says 0 even though the average score is 3. Even if I use sum() I get 0. The lowest score is 3 (out of 4) in the database, so it makes no sense. If I substitute any random string in the avg("string") I get the same result of 0, so something is not right.

That is strange. I Just did the following in tinker and it works perfectly

$data = collect([
[
    "Question One" => "4",
"Question Two" => "3",
"Question Three" => "4",
"Questions Four" => "2",
"Question Five" => "1"
],
[
    "Question One" => "4",
"Question Two" => "3",
"Question Three" => "4",
"Questions Four" => "2",
"Question Five" => "1"
],
[
    "Question One" => "4",
"Question Two" => "2",
"Question Three" => "4",
"Questions Four" => "2",
"Question Five" => "1"
]
]);

then

$data->avg('Question Two'); //outputs 2.66666666666

But you are wrapping the questions in array [ ]. They are an objects when plucked so the collection is an array of objects [{},{},{}] Also, it is “Question One”:”4” separated by : not =>

As this is php they are an array of arrays, not objects :)

I took your exact example and just replaced : with => and {} with [] (to get php syntax instead of json)

Sorry, I may not be using the right terms, so it works if you do this for each item in Tinker?

{
"Question One": "4",
"Question Two": "3",
"Question Three": "4",
"Questions Four": "2",
"Question Five": "1"
}

Instead of this:

[
"Question One" => "4",
"Question Two" => "2",
"Question Three" => "4",
"Questions Four" => "2",
"Question Five" => "1"
]

But my pluck does not return it that way, should I do a replace function to change the values?

There is no way I can get php to read {} (except using json_decode). You might be confusing javascript and php syntax :)

When you check its direct output in the browser it gets displayed as JSON as that is what makes sense (as its normally used by javascript)

Are you sure this gets multiple instances of questions blocks and not just one? Could you dd() the result and paste it here expanded?

$collection = Model::where(‘user_id’, $id)->pluck(‘question_answers’);
dd($collection->toArray());

This is what that gets:

array:5 [▼
  10 => array:5 [▼
    "Question One" => "3"
    "Question Two" => "2"
    "Question Three" => "2"
    "Question Four" => "2"
    "Question Five" => "2"
  ]
  11 => array:5 [▼
     "Question One" => "3"
    "Question Two" => "4"
    "Question Three" => "3"
    "Question Four" => "3"
    "Question Five" => "4"
  ]
  12 => array:5 [▼
     "Question One" => "3"
    "Question Two" => "3"
    "Question Three" => "2"
    "Question Four" => "4"
    "Question Five" => "4"
  ]
  13 => array:5 [▼
     "Question One" => "3"
    "Question Two" => "4"
    "Question Three" => "4"
    "Question Four" => "4"
    "Question Five" => "4"
  ]
  14 => array:5 [▼
     "Question One" => "4"
    "Question Two" => "4"
    "Question Three" => "4"
    "Question Four" => "4"
    "Question Five" => "4"
  ]
]

It starts at 10 because I filter out null rows.

That should work perfectly then

$collection = Model::where(‘user_id’, $id)->pluck(‘question_answers’);
$q1 = $collection->avg('Question One');
$q2 = $collection->avg('Question Two');
...and so on

Result says 0. I can even put a random string in the avg() and it doesn't throw an error, just says 0.

Please show your code. :)

Thank you, here is the full code I'm using to test getting the average.

$student = User::find($id);

// Retrieve the question string
$professionalism_Q1 = EvaluationForm::where('category', 'professionalism')->value('question_1');

// Find all completed evaluations
$evaluations = Evaluation::where('user_id', $student->id)->where('completed', true)->get();

// pluck professionalism scores and filter null values
$collection = $evaluations->pluck('professionalism_scores')->filter();

// testing to see the results of $collection query
return $collection; 

// gives this result
{
"10": {
"Attitude/Presentation: Generally positive attitude/focus on learning.  Appropriate language, behavior, attire.": "2",
"Initiative/Participation: Seeks out learning opportunities. Utilizes down-time effectively.": "2",
"Reliability/Preparedness: Punctual and prepared for shift, including completion of daily goals.": "2",
"Professional Standards: Adheres to scope of practice. Acts in accordance with RT program/ hospital professional conduct.": "2",
"Patient confidentiality: As per CRTO Standards of Practice and hospital policy.": "2"
},
"11": {
"Attitude/Presentation: Generally positive attitude/focus on learning.  Appropriate language, behavior, attire.": "3",
"Initiative/Participation: Seeks out learning opportunities. Utilizes down-time effectively.": "3",
"Reliability/Preparedness: Punctual and prepared for shift, including completion of daily goals.": "3",
"Professional Standards: Adheres to scope of practice. Acts in accordance with RT program/ hospital professional conduct.": "3",
"Patient confidentiality: As per CRTO Standards of Practice and hospital policy.": "3"
},
"12": {
"Attitude/Presentation: Generally positive attitude/focus on learning.  Appropriate language, behavior, attire.": "3",
"Initiative/Participation: Seeks out learning opportunities. Utilizes down-time effectively.": "3",
"Reliability/Preparedness: Punctual and prepared for shift, including completion of daily goals.": "3",
"Professional Standards: Adheres to scope of practice. Acts in accordance with RT program/ hospital professional conduct.": "3",
"Patient confidentiality: As per CRTO Standards of Practice and hospital policy.": "3"
},
"13": {
"Attitude/Presentation: Generally positive attitude/focus on learning.  Appropriate language, behavior, attire.": "3",
"Initiative/Participation: Seeks out learning opportunities. Utilizes down-time effectively.": "3",
"Reliability/Preparedness: Punctual and prepared for shift, including completion of daily goals.": "3",
"Professional Standards: Adheres to scope of practice. Acts in accordance with RT program/ hospital professional conduct.": "3",
"Patient confidentiality: As per CRTO Standards of Practice and hospital policy.": "3"
},
"14": {
"Attitude/Presentation: Generally positive attitude/focus on learning.  Appropriate language, behavior, attire.": "2",
"Initiative/Participation: Seeks out learning opportunities. Utilizes down-time effectively.": "2",
"Reliability/Preparedness: Punctual and prepared for shift, including completion of daily goals.": "2",
"Professional Standards: Adheres to scope of practice. Acts in accordance with RT program/ hospital professional conduct.": "2",
"Patient confidentiality: As per CRTO Standards of Practice and hospital policy.": "2"
},
"15": {
"Attitude/Presentation: Generally positive attitude/focus on learning.  Appropriate language, behavior, attire.": "3",
"Initiative/Participation: Seeks out learning opportunities. Utilizes down-time effectively.": "3",
"Reliability/Preparedness: Punctual and prepared for shift, including completion of daily goals.": "2",
"Professional Standards: Adheres to scope of practice. Acts in accordance with RT program/ hospital professional conduct.": "3",
"Patient confidentiality: As per CRTO Standards of Practice and hospital policy.": "3"
},
"16": {
"Attitude/Presentation: Generally positive attitude/focus on learning.  Appropriate language, behavior, attire.": "3",
"Initiative/Participation: Seeks out learning opportunities. Utilizes down-time effectively.": "3",
"Reliability/Preparedness: Punctual and prepared for shift, including completion of daily goals.": "3",
"Professional Standards: Adheres to scope of practice. Acts in accordance with RT program/ hospital professional conduct.": "3",
"Patient confidentiality: As per CRTO Standards of Practice and hospital policy.": "3"
}
}

When I try to get the average for the first question:

// average the score for the first question
$q1 = $collection->avg($professionalism_Q1);

// check the results of the average
return $q1;

// Gives

0

If I put the string in also it gives 0.

$student = User::find($id);

// Retrieve the question string
$professionalism_Q1 = EvaluationForm::where('category', 'professionalism')->value('question_1');

// Find all completed evaluations
$evaluations = Evaluation::where('user_id', $student->id)->where('completed', true)->get();

// pluck professionalism scores and filter null values
$collection = $evaluations->pluck('professionalism_scores')->filter();

// average the score for the first question
$q1 = $collection->avg("Attitude/Presentation: Generally positive attitude/focus on learning.  Appropriate language, behavior, attire.");

// check the results of the average
return $q1;

// Gives

0

Just to clear things up, I think what @twoarmtom is saying is that he is storing json objects in the database. Is that correct @twoarmtom?

If that's right the collection average methods aren't going to work for you.

You will probably have to loop through the collection and json_decode each item. In your loop you can build up count and total and use these to calculate average the good old fashioned way.

@goldtaste nice catch. Didn't think of that

If that is the case, he can probably just cast it as array

On the model

protected $casts = ['professionalism_scores' => 'array'];

@goldtaste Yes, that is correct. They are stored from a form like this:

<input required type="radio" name="{{ $form->category }}_scores[{{ $question }}]" value="4">
<label>4</label>

//  stores the value like this, as an object

{"Question":"4"}

@sinnbeck I already have it set up like that.

protected $casts = [
        'professionalism_scores' => 'array',
        'communication_scores' => 'array', 
        'analysis_problem_scores' => 'array', 
        'management_knowledge_scores' => 'array'
    ];

So I assume that won't help.

Hmm strange then. As it should be cast it would have assumed it would work. I will give it a try myself when I get the chance (unless someone beats me to it)

"You will probably have to loop through the collection and json_decode each item. In your loop you can build up count and total and use these to calculate average the good old fashioned way."

@goldtaste that is a bit above my skill level, but I understand exactly what you are saying. I used to have this saved separately in their own column rather than as an object and didn't foresee this issue down the road (the old columns are still in the database). Saving as an object helped me clean up code for the view showing the completed form, but made this option much more complicated.

Just a crazy idea

$collection = $evaluations->pluck('professionalism_scores')->filter();

$collection = collect($collection->toArray());
// average the score for the first question
$q1 = $collection->avg("Attitude/Presentation: Generally positive attitude/focus on learning.  Appropriate language, behavior, attire.");

@sinnbeck

Same result: 0

The following may work, but may not be the best solution;

$collection = Model::where(‘user_id’, $id)->pluck(‘question_answers’);

$sum = 0;

foreach($collection as $questionAnswer){
    $questionAnswer = json_decode($questionAnswer);
    $sum += $questionAnswer->{'Question One'};
}

$average = $sum / $collection->count();

echo $average;

My best bet is that it cannot find the keys. Perhaps as the encoding is different or something meaning it is not a perfect match

Try this to test that theory out

dd($collection->first() ->get("Attitude/Presentation: Generally positive attitude/focus on learning.  Appropriate language, behavior, attire."));

Does this return anything? Does it make a difference if you use single quotes ' instead of "

@sinnbeck It gets this error:

"class": "Symfony\Component\Debug\Exception\FatalThrowableError", "message": "Call to a member function get() on array",