Retrieve Associated models with group by

Need Help
1

I have the following query:
$oRProductTypes->select([‘ProductTypes.id’,‘ProductTypes.description’, ‘totalquantity’=>$oRProductTypes->func()->sum(‘quantity’),‘totalquantitye’=>$oRProductTypes->func()->sum(‘quantitye’)])
->contain([
‘Products’=>[‘ProductTypes’],
])
->group([‘ProductTypes.id’]);
that generate the above array:
[
(int) 0 => [
‘totalquantity’ => (float) 577,
‘totalquantitye’ => (float) 514
],
(int) 1 => [
‘totalquantity’ => (float) 587,
‘totalquantitye’ => (float) 384
],
(int) 2 => [
‘totalquantity’ => (float) 781,
‘totalquantitye’ => (float) 429
]
]
I dont understand why ProductTypes.id and ProductTypes.description are not shown on the result. the query generated works on mysql and the columns are shown but with the message that table dont contain a unique column. When in reality ProductTypes.id is unique because the group by clause.
If someone can help…

2

For joins and group by I usually just write normal query.

Example of connection manager:

    public static function getDogs()
    {
        $dbh = ConnectionManager::get('default');
        $sql = "SELECT * FROM dc_dogs WHERE `dogid` < :dogid";
        $sth = $dbh->prepare($sql);
        $params = [':dogid' => 5];
        $sth->execute($params);
        return $sth->fetchAll(\PDO::FETCH_OBJ);
    }

You could easily take a more complex query and use:

$sql = "SELECT dc_powners.ownerid, dc_powners.oname, ";
$sql .= "COUNT(dc_pets.petid) AS countOfPets ";
$sql .= "FROM dc_powners LEFT JOIN dc_pets ON ";
$sql .= "dc_powners.ownerid = dc_pets.ownerid ";
$sql .= "GROUP BY dc_powners.ownerid ";
$sql .= "ORDER BY dc_powners.oname";

//   bind, etc here

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1

Me, after years of java I just got used of normal queries.

Remember the ORM converts to normal SQL at run time.