Cakephp beforeFind() - How do I add a JOIN condition AFTER the belongsTo association is added?
- by michael
I'm in Model-beforeFind($queryData), trying to add a JOIN condition to the queryData on a model which has belongsTo associations. Unfortunately, the new JOIN references a table in the belongsTo association, so it must appear AFTER the belongsTo in the query.
Here is my Tagged-belongsTo association:
app\plugins\tags\models\tagged.php (line 192)
Array
(
[Tag] => Array
(
[className] => Tag
[foreignKey] => tag_id
[conditions] =>
[fields] =>
[order] =>
[counterCache] =>
)
[Group] => Array
(
[className] => Group
[foreignKey] => foreign_key
[conditions] => Array
(
[Tagged.model] => Group
)
[fields] =>
[order] =>
[counterCache] =>
)
)
Here is the JOIN added in Tagged-beforeFind(), notice that the belongsTo joins have not yet been added:
app\plugins\tags\models\tagged.php (line 194)
Array
(
[conditions] => Array
(
[Tag.keyname] => europe
)
[fields] => Array
(
[0] => DISTINCT Group.*
[1] => GroupPermission.*
)
[joins] => Array
(
[0] => Array
(
[table] => permissions
[alias] => GroupPermission
[foreignKey] =>
[type] => INNER
[conditions] => Array
(
[GroupPermission.model] => Group
[0] => GroupPermission.foreignId = Group.id
[or] => Array
( ... )
)
)
)
[limit] =>
[offset] =>
[order] => Array
(
[0] =>
)
[page] => 1
[group] =>
[callbacks] => 1
[by] => europe
[model] => Group
)
When I check the output, it fails with "1054: Unknown column 'Group.id' in 'on clause'" because the Permissions join appeared BEFORE the Groups join.
SELECT DISTINCT `Group`.*, `GroupPermission`.*
FROM `tagged` AS `Tagged`
INNER JOIN permissions AS `GroupPermission` ON (`GroupPermission`.`model` = 'Group' AND `GroupPermission`.`foreignId` = `Group`.`id` AND (...))
LEFT JOIN `tags` AS `Tag` ON (`Tagged`.`tag_id` = `Tag`.`id`)
LEFT JOIN `groups` AS `Group` ON (`Tagged`.`foreign_key` = `Group`.`id` AND `Tagged`.`model` = 'Group')
WHERE `Tag`.`keyname` = 'europe'
But this SQL (with Permissions joined moved to the end) works fine:
SELECT DISTINCT `Group`.*, `GroupPermission`.*
FROM `tagged` AS `Tagged`
LEFT JOIN `tags` AS `Tag` ON (`Tagged`.`tag_id` = `Tag`.`id`)
LEFT JOIN `groups` AS `Group` ON (`Tagged`.`foreign_key` = `Group`.`id` AND `Tagged`.`model` = 'Group')
INNER JOIN permissions AS `GroupPermission` ON (`GroupPermission`.`model` = 'Group' AND `GroupPermission`.`foreignId` = `Group`.`id` AND (...))
WHERE `Tag`.`keyname` = 'europe'
How do I add my join in beforeFind() after the belongsTo join?
