# Retrieve Associated models with group by

**URL:** <https://discourse.cakephp.org/t/retrieve-associated-models-with-group-by/6524>\
**Category:** Need Help\
**Created:** [August 21, 2019, 1:20pm UTC](https://discourse.cakephp.org/t/retrieve-associated-models-with-group-by/6524 "2019-08-21T13:20:01Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![mokiwaa](https://avatars.discourse-cdn.com/v4/letter/m/71c47a/32.png) [@mokiwaa](https://discourse.cakephp.org/u/mokiwaa)\
**Post date:** [August 21, 2019, 1:20pm UTC](https://discourse.cakephp.org/t/retrieve-associated-models-with-group-by/6524/1 "2019-08-21T13:20:01Z")

</div>

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…

---

<div class="post-metadata">

**Author:** ![jimgwhit](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/jimgwhit/32/378_2.png) [@jimgwhit](https://discourse.cakephp.org/u/jimgwhit)\
**Post date:** [September 2, 2019, 2:59am UTC](https://discourse.cakephp.org/t/retrieve-associated-models-with-group-by/6524/2 "2019-09-02T02:59:12Z")

</div>

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

#### Example of connection manager:

```auto
    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:

```auto
$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:

```ruby
ownerid, oname, countOfPets

```

Like:

```ruby
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.