tags:

views:

234

answers:

0

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?