I have this code :
$total = ClubOfferCpv::find()->groupBy( ['LEFT(cpv_id, 2)'] )
->select( ['LEFT(cpv_id, 2) AS cpv_id', 'COUNT(cpv_id) AS count'])
->all();
foreach ($total as $data)
{
$id = $data->cpv_id."000000";
$cpvName = Cpv::find()->select('name')->where(['id' => $id])->one();
echo $cpvName->name. " " . $data->count;
echo "<br>";
}
I have 2 related tables: cpv and club_offer_cpv
cpv has many club_offer_cpv
I need to pull data from club_offer_cpv and merge it with some data from cpv.
In club_offer_cpv I have field cpv_id ( foreign key for table cpv). I need to count all cpv_id from club_offer_cpv and group them by parent id. What I mean by parent id is that there is some hierarchy for ids.
01000000 - this is parent and it is stored in cpv table.
01100000
01200000 } - these are children of 01000000 and they are stored in club_offer_cpv table.
01300000
I am counting these children and I am grouping them by first two digits, because these two digits will always represent their parent.
But also I need to get parent names from cpv table so I can display them like this:
01000000 - Something first
02000000 - Something second ( "Something" second is name of this parent id )
If you look at my code above, you will see that it is not ideal, inside foreach loop I am executing one more query to get name of cpv.id based on cpv_id, so I am ending up with around 50 executed queries.
Can I make this better somehow with eager loading ? What do I need to do ?
Thanks