Yii Framework Forum: with() method across multiple databases - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

with() method across multiple databases Is this possible? Rate Topic: -----

#1 User is offline   Visevo 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 1
  • Joined: 03-October 14

Posted 03 October 2014 - 01:35 PM

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?
0

#2 User is offline   le_top 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 448
  • Joined: 08-June 10
  • Location:France (Ile-de-France/Val d'Oise)

Posted 05 October 2014 - 07:06 AM

Thoughts:
  • 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.

0

#3 User is offline   Gerhard Liebenberg 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 380
  • Joined: 07-January 12
  • Location:Stillbay - Western Cape - South Africa

Posted 05 October 2014 - 05:24 PM

It is possible yes.

Check out: wiki
0

#4 User is offline   le_top 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 448
  • Joined: 08-June 10
  • Location:France (Ile-de-France/Val d'Oise)

Posted 05 October 2014 - 05:41 PM

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.

That said, it might still be possible if one puts his mind to it.
0

#5 User is offline   realtebo 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 831
  • Joined: 03-October 12
  • Location:Ferrara

Posted 22 January 2015 - 03:53 AM

ehm,... could you, please, post a link, an idea, a suggestion, on how to realize cross-db query?
Il computer non ragiona, ma ha sempre ragione.
Ricordalo quando fai il debug
0

#6 User is offline   le_top 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 448
  • Joined: 08-June 10
  • Location:France (Ile-de-France/Val d'Oise)

Posted 22 January 2015 - 04:20 AM

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.
0

#7 User is offline   Gerhard Liebenberg 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 380
  • Joined: 07-January 12
  • Location:Stillbay - Western Cape - South Africa

Posted 03 March 2015 - 03:46 PM

Hi guys, sorry for late reply.

I got it working using AR and a behavior:
link
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users