SUM Query with specific relational other table

Need Help
1

I have a query like this

$categoryInstruments = $this->CategoryInstruments->find('all')->contain(
        [
            'SubCategories' => function($q) use($supervision_id){
                return $q->contain(['Instruments' => function($qq) use($supervision_id){
                    return $qq->contain(['Rubrics' => function($s){
                        return $s->select([
                            'Rubrics.instrument_id',
                            'total_score' => 'SUM(Rubrics.score)'
                        ]);
                    }])->where(['Instruments.supervision_id' => $supervision_id]);
                }]);
                }
        ]
);

Please help me to correct my code above, what I want is the total_score can SUM the Rubrics score where Instrument tabel that Inside CategoryInstruments…
it’s work but SUM function is calculate all rubrics score not in the specific in each data in table CategoryInstruments.

2

so you want sum group by category ? … and what fields, objects do you expect and need at the end ? … going inside out may be a better way … so doing subquery for sum and left join it with category. … but lets see … you have more tables in your query than in your question … can you try to rephrase your question please ?

3

I’m sorry that I’m not good in english, you were correct actually
the field what I want to get is SUM of score in Rubrics table which group by Instruments table that contain by Category

4

and this is my revision of my query

$categoryInstruments = $this->CategoryInstruments->find('all')->contain(
        [
            'SubCategories' => function($q) use($supervision_id){
                return $q->contain(['Instruments' => function($qq) use($supervision_id){
                    return $qq->contain(['Rubrics' => function($s){
                        return $s->select([
                            'Rubrics.instrument_id',
                            'Rubrics.score'
                        ])->group(['Rubrics.instrument_id']);
                    }])->where(['Instruments.supervision_id' => $supervision_id]);
                }]);
                }
        ]
);

or how I can SUM the Rubrics.score using foreach?
oke

thankyou

5

No worries at all, well it still depends on your use case and what data you are fetching from $categoryInstruments your view_internal.ctp (and also which cake version you use - 3.x ?).
But I try to give you some idea on alternatives ( cake 3.8+) what I (me also not an expert) would see:

Usecase 1: get the sum of Rubrics.score for each category and subcategory assuming you have good model and associations

$categoryInstruments = $this->CategoryInstruments->find(‘all’)
->contain([‘SubCategories’ , ‘SubCategories.Rubrics’, ‘Subcategories.Rubrics.Instruments’]);
$categoryInstruments->select([
‘your_category_field’,
‘your_subcategory_field’,
‘TotalScore’ => $categoryInstruments->func()->sum(‘Rubrics.Score’),
])
->where([‘Instruments.supervision_id’ => $supervision_id])
-> group([
‘your_category_field’,
‘your_subcategory_field’,
]);

use case 2: your query (probably may not work)
$categoryInstruments = $this->CategoryInstruments->find(‘all’)->contain(
[
‘SubCategories’ => function($q) use($supervision_id){
return $q->contain([‘Instruments’ => function($qq) use($supervision_id){
return $qq->contain([‘Rubrics’ => function($s){
return $s->select([
‘Rubrics_Totalscore’ => ‘sum(Rubriks.score)’,
])
}])->where([‘Instruments.supervision_id’ => $supervision_id]);
}]);
}
]
);

can you give an example, what your output (fields, values) should look like ? and probably the associations and relations between your four tables would be interesting.
The grouping on instrument_id looks for me a bit to detailed, so the statement itself will work may be, but you group on the most granular level and also without any sum function now.

so,

  • at which level you want to sum and which table is delivering the grouping field ?
  • which fields do you need / use in your view_internal.ctp ?
6

Yes, I use cakePHP version 3.8
actually I still using this query

$categoryInstruments = $this->CategoryInstruments->find('all')->contain(
        [
            'SubCategories' => function($q) use($supervision_id){
                return $q->contain(['Instruments' => function($qq) use($supervision_id){
                    return $qq->contain(['Rubrics' => function($s){
                        return $s->select([
                            'Rubrics.instrument_id',
                            'Rubrics.score'
                        ])->group(['Rubrics.instrument_id']);
                    }])->where(['Instruments.supervision_id' => $supervision_id]);
                }]);
                }
        ]
);

And then in my view_internal.ctp I get the total score using foreach statement

$total_score = 0;
foreach ($categoryInstrument->sub_categories as $subCategory) {
  foreach ($subCategory->instruments as $instrument) {
    foreach ($instrument->rubrics as $rubric) {
      $total_score = $total_score + $rubric->score;
    }
  }
}

$na = 0;
$na = 100 * ($total_score / $categoryInstrument->max_score);
// echo $total_score;
echo substr($na, 0, 4);

after doing some effort finally I solve it with this way, but I know it was not good
and I will try your use-case 1

thank you so much for your help and your explanation is good for me to understand more about CakePHP