Yii Framework Forum: Multiple Databases Dao Sql In Yii 2.0 - Yii Framework Forum

Jump to content

  • (2 Pages)
  • +
  • 1
  • 2
  • You cannot start a new topic
  • You cannot reply to this topic

Multiple Databases Dao Sql In Yii 2.0

#1 User is offline   Gerhard Liebenberg 

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

Posted 02 February 2013 - 05:16 PM

Hi guys

Active Record works fine across multiple databases, because each model can be linked to an individual DB.

But in DAO SQL, it seems that you are limited to one connection: $connection=Yii::app()->db;

Do I have it wrong? Or, is this something that could be addressed in Yii 2.0?

Many thanx
0

#2 User is offline   CeBe 

  • Advanced Member
  • Yii
  • Group: Yii Dev Team
  • Posts: 534
  • Joined: 16-July 10
  • Location:Berlin. Germany

Posted 02 February 2013 - 05:29 PM

Why do you think you are limited to one connection? You can configure multiple CDbConnections and use them.
In yii2 AR relations over multiple dbs will work better as no join is used.
0

#3 User is offline   Gerhard Liebenberg 

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

Posted 03 February 2013 - 05:04 AM

Hi CeBe

Yes, I hope I am wrong.

Can you please show a simple DAO SQL example using two tables in different databases.
0

#4 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,085
  • Joined: 16-February 11
  • Location:Japan

Posted 03 February 2013 - 05:33 AM

View PostCeBe, on 02 February 2013 - 05:29 PM, said:

In yii2 AR relations over multiple dbs will work better as no join is used.


Hi CeBe,

Do you mean that relational AR in yii2 will be something very different from that of yii1?
Or will it still join the related tables when we specify "with" in our criteria?
0

#5 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 3,778
  • Joined: 17-January 09
  • Location:Russia

Posted 03 February 2013 - 03:45 PM

http://www.yiiframew...support-in-yii/

Quote

Do you mean that relational AR in yii2 will be something very different from that of yii1?
Or will it still join the related tables when we specify "with" in our criteria?


Not entirely different but different enough. It still can join related tables, I think.
Yii 1.1 Application Development Cookbook

Enjoying Yii? Star us at github: 1.1 and 2.0.
0

#6 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,085
  • Joined: 16-February 11
  • Location:Japan

Posted 03 February 2013 - 08:35 PM

View Postsamdark, on 03 February 2013 - 03:45 PM, said:

Not entirely different but different enough. It still can join related tables, I think.


Sounds exciting enough.
I'm sure yii2 will have a better strategy for relational AR and the things around eager loading, lazy loading, "with" and "together" will be more straight and easy.

Thank you for your work, dev team. :)
0

#7 User is offline   Gerhard Liebenberg 

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

Posted 04 February 2013 - 03:33 AM

I'm glad all you "masters" are replying. More brains together - you know.

But I still need to know how to do a single DAO SQL statement using two tables in different databases.
0

#8 User is offline   CeBe 

  • Advanced Member
  • Yii
  • Group: Yii Dev Team
  • Posts: 534
  • Joined: 16-July 10
  • Location:Berlin. Germany

Posted 04 February 2013 - 04:11 AM

This only works when you go over the same db connection and your DBMS supports it.
In MySQL you can prefix the tablename with db name to access tables from different dbs.
0

#9 User is offline   Gerhard Liebenberg 

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

Posted 04 February 2013 - 06:14 AM

Hi CeBe

How do you do that?
I mean, $connection=Yii::app()->db only connects to a single db at a time.

I'm using MySQL.
0

#10 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 3,778
  • Joined: 17-January 09
  • Location:Russia

Posted 04 February 2013 - 06:52 AM

Gerhard, I've already pasted a link: http://www.yiiframew...support-in-yii/
Yii 1.1 Application Development Cookbook

Enjoying Yii? Star us at github: 1.1 and 2.0.
0

#11 User is offline   CeBe 

  • Advanced Member
  • Yii
  • Group: Yii Dev Team
  • Posts: 534
  • Joined: 16-July 10
  • Location:Berlin. Germany

Posted 04 February 2013 - 06:53 AM

MySQL connection is to a specific server and it uses a default db for queries. You can run a query on multiple dbs like it is done here: http://stackoverflow...1675354/1106908
0

#12 User is offline   Haensel 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 444
  • Joined: 14-January 11
  • Location:Vienna (Austria)

Posted 04 February 2013 - 06:55 AM

What CeBe is trying to say is that you have to change your queries in order to query the "right" database with a query like "SELECT * FROM database2.tablename" (the table is on DB2 here). If the mysql user you are using to query DB1 has the same privileges on DB2 and both databases are on the same hosts this trick should work if you are only using one db connection (in your config the db component would be configured using DB1 for example). Hope it helps

EDIT: Too late....:)
0

#13 User is offline   Gerhard Liebenberg 

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

Posted 04 February 2013 - 07:44 AM

Samdark, thanx for the link (sorry, I missed it the first time).

I already incorporated everything in that wiki into my system. It works great - in AR. But I can't see anything in the wiki about using sql.

I have monster size reports that I must convert to Yii. They incorporate data from various tables and some of the sql statements are half a page long. I guess this could be difficult with AR, so I would prefer to keep it in sql.

CeBe and Haensel, this is news to me. I thought $connection1=Yii::app()->db1 connects to a database and $connection2=Yii::app()->db2 connects to a different database, regardless of whether thay are both in the same RDMS or not. Do I understand you correctly that I only need one connection for two separate databases that run on the same MySQL instance?
What about running the databases on different machines? I suppose you need two connections then. How will the sql in Yii look for two connections?

Note that the wiki Samdark was referring to, uses 2 connection strings for the different databases. Are we saying that 1 connection string is enough?
0

#14 User is offline   CeBe 

  • Advanced Member
  • Yii
  • Group: Yii Dev Team
  • Posts: 534
  • Joined: 16-July 10
  • Location:Berlin. Germany

Posted 04 February 2013 - 02:13 PM

You can not write an sql query that joins over 2 different connections. Not with plain php and not with yii. AR can do that as it runs 2 different queries and joins the result itself. you have to go over one connection with the syntax I wrote above when you need one query to do it.
0

#15 User is offline   Gerhard Liebenberg 

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

Posted 05 February 2013 - 02:38 AM

OK, many thanks CeBe. I will do so.
0

#16 User is offline   yJeroen 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 94
  • Joined: 06-September 11
  • Location:The Netherlands

Posted 06 February 2013 - 01:18 PM

View PostGerhard Liebenberg, on 04 February 2013 - 07:44 AM, said:

Do I understand you correctly that I only need one connection for two separate databases that run on the same MySQL instance?

That is correct. As long as theyre on the same machine, you only need a single connection to that machine, even if that machine has multiple databases configured.

There's an example in the comments of that wiki page:
http://www.yiiframew...t-in-yii/#c4816
0

#17 User is offline   Gerhard Liebenberg 

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

Posted 06 February 2013 - 02:39 PM

Hi guys

I would think that, splitting your tables into multiple databases, only to have them located on the same machine, sort of defeats the purpose - doesn't it? I mean, the only good reason I can think of for having multiple databases, is to have them on DIFFERENT machines. Otherwise, what on earth is the purpose of doing it in the first place?

yJeroen, thank you for your reply. It confirms.
However, it seems that the example you refer to in the wiki works with AR, and not with DAO sql - which is exactly my problem. (I might be wrong here, but it looks like it only pertains to AR models.)

So, I guess for databases on different machines, you will have to run separate sql queries; and then merge them afterwords.
0

#18 User is offline   yJeroen 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 94
  • Joined: 06-September 11
  • Location:The Netherlands

Posted 06 February 2013 - 03:11 PM

There are multiple reasons to have multiple databases. And also multiple reasons to have them on a single, or on multiple machines.

CeBe already gave you an example how to do it with DAO. Because that's basically just using custom SQL. However, like explained earlier that can only be done if the multiple databases are on the same machine; so you can use a single connection to acces multiple databases.
0

#19 User is offline   Gerhard Liebenberg 

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

Posted 06 February 2013 - 03:31 PM

Okay, we need one connection for multiple databases on the same machine.

But what about DAO for databases on different machines? I suppose we will have to run separate sql queries via separate connections; and then merge them afterwords?
0

#20 User is offline   CeBe 

  • Advanced Member
  • Yii
  • Group: Yii Dev Team
  • Posts: 534
  • Joined: 16-July 10
  • Location:Berlin. Germany

Posted 06 February 2013 - 03:35 PM

View PostGerhard Liebenberg, on 06 February 2013 - 03:31 PM, said:

But what about DAO for databases on different machines? I suppose we will have to run separate sql queries via separate connections; and then merge them afterwords?


Exactly that.
0

Share this topic:


  • (2 Pages)
  • +
  • 1
  • 2
  • 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