with() method across multiple databases

Yii 1.1 General Discussion
1

Consider the following SQL query:




$sql = "

SELECT t1.id, t1.task, t1.description t2.staffid, t3.fullname

FROM table_1 AS t1

JOIN table_2 AS t2 ON t1.id = t2.id

JOIN `external_db`.table AS t3 ON t2.staffid = t3.staffid

";

and I run


$cmd = Yii::app()->db->createCommand($sql)->setFetchMode(PDO::FETCH_OBJ);

Is it possible to accomplish this via Yii’s with() method? I cannot seem to get it working using multiple databases, even though I have extended CActiveRecord to handle multiple database connections, I still get the error:


Database1.t3 not found in Database1

Any thoughts?

2

Thoughts:

[list=1][]I suppose that you tested the proposed SQL query interactively and that it works;[]I suggest that you get the actual SQL query that Yii has built to check that it is correct and to make manual adjustments to make it work in order to know how to adjust your Yii setup.[]It looks like the query is looking for t3 in Database1 - does it exist there?[]Possibly create a third (empty) database so that all databases that you use are "external" databases with might work around your issue.[/list]

3

It is possible yes.

Check out: wiki

4

Hi Gerhard

The Wiki you refer to says:

  • Tables in one database cannot directly reference tables in another database, and this means that relations don’t cross DB boundaries.

[font="Arial, sans-serif"][color="#222222"][size=4]That said, it might still be possible if one puts his mind to it.[/size][/color][/font]

5

ehm,… could you, please, post a link, an idea, a suggestion, on how to realize cross-db query?

6

Because you should do something like this to fully specify the database with each table:


[font=Consolas, Menlo, Monaco,]SELECT[/font][font=Consolas, Menlo, Monaco,] [/font][font=Consolas, Menlo, Monaco,]*[/font][color=#000000][size=3]  [/size][/color][color=#00008B][size=3]FROM[/size][/color][color=#000000][size=3] this_database[/size][/color][color=#000000][size=3].[/size][/color][color=#000000][size=3]table_1 t1 [/size][/color][font=Consolas, Menlo, Monaco,]  [/font][font=Consolas, Menlo, Monaco,]JOIN[/font][font=Consolas, Menlo, Monaco,] that_database[/font][font=Consolas, Menlo, Monaco,].[/font][font=Consolas, Menlo, Monaco,]table_2 t2 [/font][font=Consolas, Menlo, Monaco,]ON[/font][font=Consolas, Menlo, Monaco,] t2[/font][font=Consolas, Menlo, Monaco,].[/font][font=Consolas, Menlo, Monaco,]column[/font][font=Consolas, Menlo, Monaco,] [/font][font=Consolas, Menlo, Monaco,]=[/font][font=Consolas, Menlo, Monaco,] t1[/font][font=Consolas, Menlo, Monaco,].[/font][font=Consolas, Menlo, Monaco,]column[/font]

I think that if you add the database name to ‘tableName()’ that you might already advance a bit. However you may run into trouble regarding quoting, etc through yii.

7

Hi guys, sorry for late reply.

I got it working using AR and a behavior:

link