How do I join across two different databases?

Question

I have two databases, the normal drupal db and another db to another system. I want to join two tables from these two different dbs. I have already set up the second database using information from here, but I don't know how I would go about doing a join across two databases.

Imagine both have a users table, but the second database also has a field called "telephone number". I want to join the two users table on uid, so that telephone number (and other fields) are also included in the result.

Any ideas on how to go about this? Is it even possible?

Answer 1

Yes, with caveats that your DB infrastructure config will dictate if you can do this.

While MySQL allows you to do cross DB table joins, the DB user needs privileges to access both DB's to do it. Additionally, Drupal can only use one DB connection to execute a cross DB table join query.

So if you had a connection defined in your settings.php, called my_connection, and it had access to 2 DBs called DB1 / DB2 You could do things like:

// Assuming 'my_connection' defined in settings.php has access to DB1 & DB2
$db_key = 'my_connection';
$example_join_query = 'SELECT * FROM `DB1`.users AS u1 LEFT JOIN `DB2`.users AS u2 ON u1.uid = u2.uid';
$connection = \Drupal\Core\Database\Database::getConnection('default', $db_key);
$example_results = $connection->query($example_join_query)->fetchAll();

// Same idea, with dynamic queries
$query = \Drupal::database()->select('`DB1`.users', 'u1')->fields('u1');
$query->join('`DB2`.users', 'u2', 'u1.uid = u2.uid');
$query = $query->fields('u1');
$query = $query->fields('u2');
$example_results = $query->execute()->fetchAll();

If your DB infra config is something like 2 isolated hosts, you'll need to take a different approach, like querying data for each DB connection then joining the resulting datasets in PHP code (instead of using a SQL JOIN).