CODEWITHSUNDEEP
cakephpcakephp-4.x
mk97
mk97

Reputation: 274

CakePHP How to Get Associated Data and COUNT/GROUP BY

I'm having some trouble with Cakephp Query builder and try to get results I'm looking for. I have three tables 'Users', 'Items', 'Owns', where Owns belongs to both 'Users' and 'Items'.... A user can own many items (and many of the same items).

So I'm wanting to return the "owned" items by a user along with the count... However whenever I add count into my query I lose the associated Items data.

example - this returns the user data along with associated owns data and associated items data as shown below the query

$owns = $this->Users->get($id, [
  'contain' => [
    'Owns' => function($q) { return $q->find('all')->group(['Owns.item_id']); },
    'Owns.Items'
  ],
]);

SQL Generated from this is

SELECT 
  Owns.id AS Owns__id, 
  Owns.item_id AS Owns__item_id, 
  Owns.user_id AS Owns__user_id, 
  Items.id AS Items__id, 
  Items.name AS Items__name, 
  Items.description AS Items__description, 
  Items.created AS Items__created, 
  Items.modified AS Items__modified 
FROM 
  owns Owns 
  INNER JOIN items Items ON Items.id = (Owns.item_id) 
WHERE 
  Owns.user_id in (1) 
GROUP BY 
  Owns.item_id

Data results look like this:

'owns' => [
    'id' => (int) 1,
    'username' => 'myusername',
    'owns' => [
        (int) 0 => object(App\Model\Entity\Own) id:0 {
        'id' => (int) 2
        'item_id' => (int) 1
        'user_id' => (int) 1
            'item' => object(App\Model\Entity\Item) id:1 {
                'id' => (int) 1
                'name' => 'Item Name'
                'description' => 'Item Description'

However in the 'owns' query I want to add in the count (i.e. the number owned). I can get the count by using this query but then I lose the associated item object from my results. I've tried this many different ways but always seems that if I want to use SQL count I can't get the associated data.

$owns2 = $this->Users->get($id, [
  'contain' => [ 
    'Owns' => function($q) { return $q->select(['count' => $q->func()->count('Owns.id'),'Owns.id', 'Owns.user_id', 'Owns.item_id'])->group(['Owns.item_id']); },
    'Owns.Items'
  ],
]);

SQL Generated from this is

SELECT 
  (COUNT(Owns.id)) AS count, 
  Owns.id AS Owns__id, 
  Owns.user_id AS Owns__user_id, 
  Owns.item_id AS Owns__item_id 
FROM 
  owns Owns 
  INNER JOIN items Items ON Items.id = (Owns.item_id) 
WHERE 
  Owns.user_id in (1) 
GROUP BY 
  Owns.item_id

Data results look like this:

'owns2' => [
    'id' => (int) 1,
    'username' => 'myusername',
    'owns' => [
        (int) 0 => object(App\Model\Entity\Own) id:36 {
        'count' => (int) 4
        'id' => (int) 2
        'user_id' => (int) 1
        'item_id' => (int) 1

Any insight into how I can get "count" into the first $user query or the associated items into the $user2 would be appreciated.

Upvotes: 2

Views: 854

Answers (1)

mk97
mk97

Reputation: 274

To keep the association table's fields that are returned in the $owns = find('all') query along with the SQL count function I needed to add the associated fields into the select statement

$owns2 = $this->Users->get($id, [
  'contain' => [ 
    'Owns' => function($q) { return $q->select(['count' => $q->func()->count('Owns.id'),'Owns.id', 'Owns.item_id','Owns.user_id', 'Items.id', 'Items.collection_id','Items.name', 'Items.description'])->group(['Owns.item_id']); },
    'Owns.Items'
  ],

]);

Upvotes: 1

Related Questions