Joins not used for complex HABTM search CakePhp 2

Question

I have Contents, which can have Tags belonging to different TagGroups. I have a quite complex search condition which is as follows: A Content matches if it is tagged with at least one tag from the search as long as it belongs to the same tag group.

Example: TagGroup 1 are colours, TagGroup2 are shapes. So if a Content is tagged with "blue", "turquoise" and "rectangular" it will be found, when I search for "blue" and "rectangular" However this example is only to show that the logic behind this is quite complex.

Content -> ContentsTag <- Tag -> TagGroup

I want to develop a search with paging of the results I had it working, but between refactoring and framework updates it is broken.

At some point I loose the information for the joins and so my SQL is crashing because it is missing tables.

array(
    'limit' => (int) 10,
    'order' => array(
        'Content.objnbr' => 'asc'
    ),
    'joins' => array(
        (int) 0 => array(
            'table' => 'sang_contents_tags',
            'alias' => 'CT1', //join for the first TagGroup
            'type' => 'INNER',
            'conditions' => array(
                (int) 0 => 'CT1.content_id = Content.Id'
            )
        ),
        (int) 1 => array(
            'table' => 'sang_contents_tags',
            'alias' => 'CT2', //join for the second TagGroup
            'type' => 'INNER',
            'conditions' => array(
                (int) 0 => 'CT2.content_id = Content.Id'
            )
        )
    ),
    'conditions' => array(
        'AND' => array(
            (int) 0 => array(
                'OR' => array(
                    (int) 0 => array(
                        'CT1.tag_id' => '189' // chosen Tag 1 from the first TagGroup
                    )
                )
            ),
            (int) 1 => array(
                'OR' => array(
                    (int) 0 => array(
                        'CT2.tag_id' => '7' // chosen Tag 2 from the second TagGroup
                    )
                )
            )
        )
    ),
    'contain' => array(
        (int) 0 => 'Description',
        'ContentsTag' => array(
            'Tag' => array(
                (int) 0 => 'Taggroup'
            )
        )
    )
)

results in the following SQL:

SELECT `Content`.`id`, `Content`.`objnbr`, `Content`.`name`, `Content`.`imagecounter`, `Content`.`videolength`, `Content`.`money_maker`, `Content`.`comment` 
FROM `my_db`.`contents` AS `Content` 
    WHERE ((`CT1`.`tag_id` = '189') AND (`CT2`.`tag_id` = '7')) 
ORDER BY `Content`.`id` DESC 
LIMIT 20

So clearly the Tags CT1 and CT2 are not joined and my sql is crashing.

Could it be that the contain is blocking the joins? If I unset the contain I still get the same result / error.

Any ideas?

Edit: To clarify, what I want to achieve: The result should be a SQL statement like this:

SELECT `Content`.`id`, `Content`.`objnbr`, `Content`.`name`, `Content`.`imagecounter`, `Content`.`videolength`, `Content`.`money_maker`, `Content`.`comment` 
FROM
    `my_db`.`contents` AS `Content`
        INNER JOIN
    contents_tags AS CT1 ON CT1.content_id = Content.Id
        INNER JOIN
    contents_tags AS CT2 ON CT2.content_id = Content.Id
WHERE
    ((`CT1`.`tag_id` = '189')
        AND (`CT2`.`tag_id` = '7'))
ORDER BY `Content`.`id` DESC
LIMIT 10

It looks like the trouble is caused by the pagination. If I do a "simple" find I get Contents based on the Tags:

$result = $this->Content->find('all', $this->paginate['Content']);

generated query by find:

SELECT 
    `Content`.`id`,
    `Content`.`objnbr`,
    `Content`.`name`,
    `Content`.`imagecounter`,
    `Content`.`videolength`,
    `Content`.`money_maker`,
    `Content`.`comment`
FROM
    `my_db`.`contents` AS `Content`
        INNER JOIN
    `my_db`.`contents_tags` AS `CT0` ON (`CT0`.`content_id` = `Content`.`Id`)
        INNER JOIN
    `my_db`.`contents_tags` AS `CT2` ON (`CT2`.`content_id` = `Content`.`Id`)
WHERE
    ((`CT0`.`tag_id` = '56')
        AND (`CT2`.`tag_id` = '7'))
ORDER BY `Content`.`objnbr` ASC

$result = $this->Paginator->paginate($this->modelClass, $this->param); with $this->modelClass = 'Content' and $this->param = array( 'AND' => array( (int) 0 => array( 'OR' => array( (int) 0 => array( 'CT1.tag_id' => '189' ) ) ), (int) 1 => array( 'OR' => array( (int) 0 => array( 'CT2.tag_id' => '7' ) ) ) ) ) — Calamity Jane, Aug 21 '17 at 11:46
So looks like somewhere before this call my information gets lost..... That is at least a good start for further debugging! — Calamity Jane, Aug 21 '17 at 11:47
Manual, since I am using the same connection between Tag and TagGroups twice. Tag belongs to TagGroup — Calamity Jane, Aug 21 '17 at 12:06
if (!empty($querytags)) { foreach ($querytags['conditions'] as $key => $condition) { $this->param[$key] = $condition; } foreach ($querytags['join'] as $tagjoins) { $this->join[] = $tagjoins; } } $querytags being the Tags selected in the search form — Calamity Jane, Aug 21 '17 at 12:08
Of course I could cheat and not use pagination at all. But that would run the risk of getting some huge result sets depending on the search conditions. — Calamity Jane, Aug 21 '17 at 12:13
sorry have not understand your find query and what u actually want to do — Ajay Krishna Dutta, Aug 21 '17 at 12:14
so you want to convert this mysql query into cakephp find query?? right? — Ajay Krishna Dutta, Aug 22 '17 at 06:50
No. I want to know what to do to have this query working with pagination. So that the Paginator is calling the correct sql in the background. — Calamity Jane, Aug 22 '17 at 08:43

score 0 · Answer 1 · answered Aug 23 '17 at 08:44

I did a research in the bowels of the pagination class and my conclusion is, that it simply is not able to work with the current Paginator, because I cannot pass on my special joins I need for this complex query.

A custom find type also will not help, because my query is too dynamic for that.

Should anybody prove me wrong I will be a happy coder.

Joins not used for complex HABTM search CakePhp 2

1 Answers1