I've used a pice of code provided by webchick to successfully join a drupal table with a table in an external database through Views. The trick is (of course) to give the required privileges on the external database to the drupal user.

In essence, this is how it is done :

function hook_views_data() {

  // Define name for legacy db.
  $db_name = 'legacy_db';

  // Define structure for Views.
  $data['report'] = array(
    'table' => array(
      'name' => $db_name . '.report',
      'group' => t('Report'),
      'base' => array(
        'field' => 'id',
        'title' => t('Report'),
        'help' => t('Reports'),
        'weight' => -10,
        'database' => $db_name,
      ),
      'join' => array(
        'users' => array(
          'table' => $db_name . '.report',
          'left_field' => 'uid',
          'field' => 'reporter',
        ),
      ),
    ),
  );

  return $data;
}

Now, this does work, but there's a couple of things I'd like to understand:

1. Why can't Views just prefix the 'report' table with the name provided in $data['report']['base']['database']?
2. When I provided 'legacy_db.report' as table name, Views will attempt to create a query with this name inside curly brackets. Will Drupal then attempt to prefix the entire table 'path', just the actual table name which comes after the dot, or not at all since the table after all lives in another database?

Hope to hear about your thoughts on this.

Comments

sbrattla’s picture

I've tested this a little further, and came to the following conclusion on #2:

When Drupal runs a query, it assumes that all tables are in the same schema. Thus, even though the query is a join between two difference schemas (the database user has privileges in both schemas), Drupal will still think that it is dealing with one schema and will prefix tables according to the configuration set for the currently active database.

It is hard to blame Drupal for doing this. After all, the join could be between Drupal's 'users' database and a legacy 'users' database, but Drupal would have no reason to assume that the lagacy 'users' database is a different one in a different schema. However, this does not solve the issue of not being able to join other schemas through Views. I could solve this through a hook to alter queries, but judging from other tickets, it might be a feature worth implementing.

As it is right now, my above solution does not work when there's a prefix in action, because queries will put 'legacy_db.report' into curly brackets and Drupal will then attempt to prefix the entire 'legacy_db.report' path. What I would like to know is wether it would be doable and sensible for Views to investigate the tables it is doing joins on, and from there ignore putting table names inside curly brackets if the table lives in another schema? Alternatively, it could also investigate current database connections and attempt to configure things based on those.

I won't mind trying to look at the coding part, but I'm just checking if it is something that is worth going into or if it most likely will be rejected?

kars-t’s picture

Status: Active » Fixed

Dear fellow Drupal enthusiasts,

this issue is now lasting for a very long time in the issue queue and was unfortunately never solved. As Drupal is a open source project everyone is helping on voluntary basis. So that this is was not solved is nothing personal and means no harm. But perhaps no one had time to deal with this issue, maybe it is too complex or did not describe the problem comprehensibly.

But this issue is not the only one. There are thousands of issues on Drupal.org that have never been worked on or could not be processed. This means that we are building a wave that is unmanageable and just a problem for the Drupal project as a whole. Please help us keep the issue queue smaller and more manageable.

Please read again, "Making an issue report" and see if you can improve the issue. Test the problem with the current Core and modules. Maybe the problem doesn't exist anymore, is a duplicate or has even been solved within this issue but never closed.

Help can also be found for it on IRC and in the user groups.

In order to remove this issue, I have set this issue to "fixed".

If there is new information, please re-open the issue.

--
This issue was edited with the help of Issue Helper

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

omarlopesino’s picture

StatusFileSize
new1.02 KB

I had to do the same thing in a project and i obtain it :D

I made a class which extends from views_handler_relationship and,
in the moment before the relationship is executed, i modify the variables which says
what table we have to join, and i prepend a database name (extracted from the code in which i indicate the join with the external table
, a custom property named database). And it works. It maybe wouldn't be the best option, but it's useful at the moment.

I leave the code in the issue.

The code of the query() method is the same that views_handler_relationship method, but i add the following lines:

    // Alteration:
    // @NOTE: alter the table of the default database which we are
    // going to query. The only thing we do is prefix the table with the
    // name of the default database and a point.
    $join->definition['base'] = $join->definition['database'] .  '.' . $join->definition['base'];
    $join->definition['table'] = $join->definition['database'] . '.' . $join->definition['table'];
    $join->table = $join->definition['database'] . '.' . $join->table;

In your case, the only thing you have to do is this:

function hook_views_data() {
  // Define name for legacy db.
  $db_name = 'legacy_db';


  // Define structure for Views.
  $data['report'] = array(
    'table' => array(
      // Database property for use instead of the active database.
      'database' => $db_name,
      'name' =>'report',
      'group' => t('Report'),
      'base' => array(
        'field' => 'id',
        'title' => t('Report'),
        'help' => t('Reports'),
        'weight' => -10,
        'database' => $db_name,
      ),
      'join' => array(
        'users' => array(
          'table' => $db_name . '.report',
          'left_field' => 'uid',
          'field' => 'reporter',
        ),
      ),
    ),
  );

  $data['report']['reporter']  = array(
    'title' => t('Reporter'),
    'relationship' => array(
      // Database property for use instead of the active database.
      'database' => $db_name,
      'base' => 'user',
      'field' => 'reporter',
      'left_field' => 'uid',
      'handler' => 'views_handler_relationship_external_database',
      'label'     => t('User'),
    ),
  );
  return $data;
}
omarlopesino’s picture

Status: Closed (fixed) » Needs work
omarlopesino’s picture

StatusFileSize
new991 bytes
mustanggb’s picture

Status: Needs work » Fixed

Why NW?

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

naveko’s picture

This used to work well, but now we're on Drupal core 7.78 which broke this functionality. I traced down the code to includes/database/mysql/database.inc and found that the method 'setPrefix' broke the join.

My relationship handler looks like this:

class views_handler_relationship_external extends views_handler_relationship {
  /**
   * Called to implement a relationship in a query.
   */
  public function query() {
    $table_data = views_fetch_data($this->definition['base']);
    $base_field = empty($this->definition['base field']) ? $table_data['table']['base']['field'] : $this->definition['base field'];

    $this->ensure_my_table();

    $def = $this->definition;
    $def['table'] = $this->definition['base'];
    $def['field'] = $base_field;
    $def['left_table'] = $this->table_alias;
    $def['left_field'] = $this->real_field;
    if (!empty($this->options['required'])) {
      $def['type'] = 'INNER';
    }

    if (!empty($this->definition['extra'])) {
      $def['extra'] = $this->definition['extra'];
    }

    if (!empty($def['join_handler']) && class_exists($def['join_handler'])) {
      $join = new $def['join_handler']();
    }
    else {
      $join = new views_join();
    }

    $join->definition = $def;
    $join->options = $this->options;
    $join->construct();
    $join->adjusted = TRUE;

    // Use a short alias for this.
    $alias = $def['table'] . '_' . $this->table;

    $join->definition['base'] = $join->definition['database'] .  '.' . $join->definition['base'];
    $join->definition['table'] = $join->definition['database'] . '.' . $join->definition['table'];
    $join->table = $join->definition['database'] . '.' . $join->table;

    $this->alias = $this->query->add_relationship($alias, $join, $this->definition['base'], $this->relationship);

    // Add access tags if the base table provide it.
    if (empty($this->query->options['disable_sql_rewrite']) && isset($table_data['table']['base']['access query tag'])) {
      $access_tag = $table_data['table']['base']['access query tag'];
      $this->query->add_tag($access_tag);
    }
  }


}

I am still looking for a solution here.

strelkov.av’s picture

I understand correctly that earlier in Views it was possible to attach additional information from a third-party database via JOIN.
For example, there is a standard table USERS where there is a login field NAME. And let's say there is a third-party database (which is NOT DESCRIBED in settings.php) with a table that stores information of the type NAME->FULL_NAME.
So it turns out that earlier it was possible to add the FULL_NAME field to the main USERS table via hook_views_data_alter and join?

damienmckenna’s picture

Please open bug reports for the database system in the Drupal core issue queue.