Multiple Databases Dao Sql In Yii 2.0

Yii 2.0 General Discussions
1

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

2

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.

3

Hi CeBe

Yes, I hope I am wrong.

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

4

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?

5

http://www.yiiframework.com/wiki/123/multiple-database-support-in-yii/

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

6

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. :)

7

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.

8

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.

9

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.

10

Gerhard, I’ve already pasted a link: http://www.yiiframework.com/wiki/123/multiple-database-support-in-yii/

11

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.com/a/1675354/1106908

12

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…:)

13

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?

14

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.

15

OK, many thanks CeBe. I will do so.

16

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.yiiframework.com/wiki/123/multiple-database-support-in-yii/#c4816

17

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.

18

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.

19

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?

20

Exactly that.