CakePHP：使用不同的数据库关联两个模型？ [英] CakePHP: associating two models using different databases?

问题描述

我有两个模型，植物和Emp，有一个Has和属于许多关系。我已经配置它们关联和查询获取每个的数据是正确的，但问题是Plant和Emp在不同的数据库。 Emp在数据库1上，工厂在数据库2上。因此，它们不会正确查询连接表;连接表只在数据库1上。

I have two models, Plant and Emp, that have a Has And Belongs To Many relationship. I've configured them to be associated and the query to get the data for each is correct, but the problem is Plant and Emp are on different databases. Emp is on Database 1, Plant is on Database 2. Because of this they don't query the join table properly; the join table is only on Database 1.

当工厂模型尝试访问连接表时，它查询数据库2，它没有这些数据。

When the Plant model tries to access the join table it's querying Database 2, which does not have this data.

这是植物协会的协会。

var $hasAndBelongsToMany = array(
    'Plant' =>
        array(
            'className'              => 'Plant',
            'joinTable'              => 'emp_plant',
            'foreignKey'             => 'employee_id',
            'associationForeignKey'  => 'LocationID',
            'unique'                 => true,
            'conditions'             => '',
)
);

更新：我试图设置一个finderQuery查询连接表，但我不知道如何给出这样的原始SQL查询，并允许它动态使用模型的实例的id，而不是预定义的值。

Update:I tried to set a "finderQuery" attribute to let me query the join table, but I don't know how to give a raw SQL query like that and allow it to dynamically use the id for the instance of the Model instead of a predefined value.

我可以设置像

SELECT * FROM [Plant] AS [Plant] JOIN [DB].[DBO].[empplant] AS 
[EmpPlant] ON ([EmpPlant].[employee_id] = **4** 
AND [EmpPlant].[ID] = [Plant].[LocationID]) 

这将给我正确的数据 employee，但我不知道如何使这个finderQuery动态查询。

Which will give me the correct data for one employee, but I don't know how to make this finderQuery a dynamic query. There has to be a way for this to work.

推荐答案

我需要使用自定义 finderQuery ，并使用特殊的 {$ __ cakeID __ $} 标识符来代替匹配的模型ID。这是上面示例的固定版本，在 $ hasAndBelongsToMany 数组的关系条目中设置为查找器查询。

I needed to use a custom finderQuery and use the special {$__cakeID__$} identifier in place of the model ID being matched. This is a fixed version of the sample above, set as the finder query in the relationship entry for the $hasAndBelongsToMany array.

'finderQuery'=>'SELECT * FROM [Plant] AS [Plant] JOIN [DB].[DBO].[empplant] AS 
[EmpPlant] ON ([EmpPlant].[employee_id] = {$__cakeID__$} 
AND [EmpPlant].[ID] = [Plant].[LocationID])' 

这可行，但如果任何人都知道如何解决这种情况，而不需要自定义查询器查询（我在通过使用关联避免）请张贴一个答案，我会标记正确的。

This works but if anyone knows how to fix this situation without a custom finder query (what I was trying to avoid by using associations) please post an answer and I will mark that correct instead.

这篇关于CakePHP：使用不同的数据库关联两个模型？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！