Cross DB Relationships?

[ellisgl]

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]

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]

Last time I checked PDO didn't allow it.

[ellisgl]

@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]

@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]

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.

spot2/lib/Query.php

Lines 112 to 113 in 70f5d92

	// Create Doctrine DBAL query builder from Doctrine\DBAL\Connection
	$this->_queryBuilder = $mapper->connection()->createQueryBuilder();

spot2/lib/Config.php

Lines 65 to 69 in e4ada95

	public function connection($name = null)
	{
	if ($name === null) {
	return $this->defaultConnection();
	}

[vlucas]

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.