CakePHP 2.9 join query returning wrong result

Question

I'm using the following (working) MySQL query to get a count of all the collection_items for a collection_keyword

SELECT ck.id
     , ck.working_title
     , count(*) as matches 
  FROM collection_keywords ck
  JOIN collection_keywords_collection_items ckci 
    ON ckci.collection_keyword_id = ck.id
  JOIN nl_collection_items nci 
    ON nci.id = ckci.collection_item_id
 GROUP 
    BY ck.id

SQL Fiddle: http://sqlfiddle.com/#!9/7b4b0a/1

Which returns the following information

+----+---------------+---------+
| id | working_title | matches |
+----+---------------+---------+
|  1 | First Title   |       5 |
|  2 | Second Title  |      10 |
|  3 | Third Title   |      15 |
+----+---------------+---------+

I'm trying to replicate this query using CakePHP 2.9., using the join method but

the result is returning empty

This is my CakePHP code

$collectionKeywords = $this->CollectionKeyword->find('all', array(
    'contain' => array(),
    'fields' => array('CollectionKeyword.id, CollectionKeyword.working_title', 'count(*) AS matches'),
    'joins' => array(
        array(
            'table' => 'collection_keywords_collection_items',
            'type' => 'INNER',
            'conditions' => array(
                'collection_keywords_collection_items.collection_keyword_id' => 'CollectionKeyword.id'
            )
        )
    ),
    'group' => 'CollectionKeyword.id',
));

The relations are defined as follows

class CollectionKeyword extends PluginCollectionKeyword
{
    public $hasAndBelongsToMany = array(
        'CollectionItem' => array(
            'className' => 'CollectionItem',
            'joinTable' => 'collection_keywords_collection_items',
            'foreignKey' => 'collection_keyword_id',
            'associationForeignKey' => 'collection_item_id',
        )
    );
}

And the CollectionItem relation

class CollectionItem extends PluginCollectionItem
{

    public $hasAndBelongsToMany = array(
        'CollectionKeyword' => array(
            'className' => 'CollectionKeyword',
            'joinTable' => 'collection_keywords_collection_items',
            'foreignKey' => 'collection_item_id',
            'associationForeignKey' => 'collection_keyword_id',
        ),

    );
}

(Actually, probably wasn't required in this instance, but +1 anyway) — Strawberry, Aug 20 '19 at 07:29

score 1 · Accepted Answer · answered Aug 20 '19 at 07:53

A few issues I found in your code:

The array key for Join should be joins. You have used join.
The condition for 2nd Inner Join is missing.

Here is the code that worked.

 $collectionKeywords = $this->CollectionKeyword->find('all', array(
    'contain' => array(),
    'fields' => array('CollectionKeyword.id, CollectionKeyword.working_title', 'count(*) AS matches'),
    'recursive' => -1,
    'joins' => array(
        array(
            'table' => 'collection_keywords_collection_items',
            'type' => 'INNER',
            'conditions' => array(
                "collection_keywords_collection_items.collection_keyword_id = CollectionKeyword`.id"
            )
        ),
        array(
            'table' => 'collection_items',
            'type' => 'INNER',
            'conditions' => array(
                "collection_items.id = collection_keywords_collection_items.collection_item_id"
            )
        ),        
    ),
    'group' => 'CollectionKeyword.id',
    ));

It produces the below query

SELECT CollectionKeyword.id, CollectionKeyword.working_title, count(*) AS matches FROM `collection_keywords` AS `CollectionKeyword` INNER JOIN `collection_keywords_collection_items` ON (`collection_keywords_collection_items`.`collection_keyword_id` = `CollectionKeyword`.`id`) INNER JOIN `collection_items` ON (`collection_items`.`id` = `collection_keywords_collection_items`.`collection_item_id`) WHERE 1 = 1 GROUP BY `CollectionKeyword`.`id`

Depending upon the Cakephp Version that you are using, you can verify the Last Query which ran by using this code.

Thanks for your answer! It also works without the second join, since I don't need any data from that table, I see now that I was using array notation for the join conditions, which is false. — Miguel Stevens, Aug 20 '19 at 07:57

CakePHP 2.9 join query returning wrong result

SQL Fiddle: http://sqlfiddle.com/#!9/7b4b0a/1

the result is returning empty

1 Answers1