search

spotorm / spot2

Spot v2.x DataMapper built on top of Doctrine's Database Abstraction Layer
http://phpdatamapper.com
BSD 3-Clause "New" or "Revised" License
601 stars 101 forks source link

Cross DB Relationships? #288

Closed ellisgl closed 4 years ago

ellisgl commented 5 years ago

Say I have system where I have a couple main DBs (let's say they are called 'main', 'a', 'b', 'c') and I have a bunch of DB that are segmented for customer data, but their structures are all the same (let say they are named 'cust-1', 'cust-2' ... 'cust-n'). These DBs are all in the same server. Now there can be FKs that could be make in the structures that point to the other DBs (which is totally doable in MySQL / Maria and several others). Is this achievable in Spot?

FlipEverything commented 5 years ago

I don't think so. Based on my experience you can only use one database inside a request. You can define multiple connections but these are just datasources, not cross-db references.

merry-goblin commented 5 years ago

Last time I checked PDO didn't allow it.

ellisgl commented 5 years ago

@merry-goblin Why wouldn't PDO allow it? FKs are DB side and PDO shouldn't have a reason to know about them, since it's basically just a query passer.

ellisgl commented 5 years ago

@FlipEverything So, no queries like this are generated:

SELECT t1.*, t2.*
FROM db1.table t1 
INNER JOIN db2.table ON t1.id = t2.someId;
FlipEverything commented 5 years ago

No, joins are not supported right now (#175).

The Query Builder is bound to the default connection which (I think) is bound to a single database. https://github.com/spotorm/spot2/blob/70f5d92594f9c8f03f03b1dbf4737225740c80ff/lib/Query.php#L112-L113

https://github.com/spotorm/spot2/blob/e4ada9591a2900fbb8dd9c81862772f9b3d998a3/lib/Config.php#L65-L69

vlucas commented 5 years ago

If you are using Spot elsewhere in your application, you can also write raw SQL queries in Spot and still populate collections and entities with the results, but Spot itself may not directly support this use case.