Symfony query with multiple databases

Question

This site has been great for a Symfony newbie such as myself and hopefully this will be the same experience. I have searched a lot for this question so maybe I am not using the right terminology. I have read about using services but none seem to give an example of what I need using multiple databases with different tables. So here goes, first off I am at the discretion of the current database design and I can't merge databases or recreate them, I have to use them as is. Here is the mysql query I want to use:

select name, title, rank from db1.tbl1, 
db2.tbl1,db2.tbl2 
where db2.tbl1.id=db2.tbl.id
and db1.tbl1.person_id=db2.tbl2.person_id;

I have created connections to the db in parameters.yml and config.yml. I was thinking about creating a repository for one of the entities and then having it innerjoin the other tables from the same database but couldn't find any examples. I want to do this using best practice. I am all ears.

I should also mention all the databases are managed by the same server.

Take a look at this answer stackoverflow.com/questions/13648974/… — D_R
– D_R, Commented Aug 20, 2015 at 20:39
That seems more for if they reside in different bundles but that just may be semantics. Is this the best practice? @DanRevah — shayster01
– shayster01, Commented Aug 20, 2015 at 21:25

Z-WolF · Accepted Answer · 2015-08-21 05:09:10Z

1

You can't use multiple databases in a single query because for multiple databases to work, you need a manager for each.

I can't think of a solution using arrays or objects that is not resource intensive. Because you need to load at least one entire table.

answered Aug 21, 2015 at 5:09

Z-WolF

4092 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

shayster01 Over a year ago

I may not have used the right terminology but I have created multiple managers for each as per the instruction you listed. So are you saying the only option is to use a straight php function for this? Symfony has never considered the idea that people may want to query across databases? @Z-WoIF

Z-WolF Over a year ago

Not with the ORM or the DQL (that i know of). More than a symfony issue, this is a Doctrine one. Another option is to create a third manager with a user with privileges over those multiples databases and then use Native SQL

shayster01 Over a year ago

As a pre cursor remember I didn't create these databases so, the funny thing is all the databases have the same user privileges. I think that might be the best solution as well but wanted to be sure. Thanks.

Collectives™ on Stack Overflow

Symfony query with multiple databases

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related