Loading nodes from another Drupal database using Views

Question

I'm trying to use Views to load nodes from another Drupal database defined in settings.php with a key of 'cms'. Both sites are Drupal 7. The other database is a different Drupal install which is acting as a content repository or centralized CMS. My goal is to create a new view type/group on the destination site, so when creating a view the site build can choose "CMS Content" instead of "Content". I'm hoping a site builder would then be able to build a view normally based on content types and content from the centralized CMS site, even if I have to tell views about all the fields in each content type.

In my hook_views_data() implementation I've set the 'database' key, but can't figure out how to read from the node table without overriding $data['node'].

function cms_connector_views_data() {
  $data['cms_connector']['table']['group'] = t('CMS Content');
  $data['cms_connector']['table']['base'] = array(
    'field' => 'nid',
    'title' => t('CMS Content'),
    'help' => t('Content from the centralized CMS.'),
    'database' => 'cms',
  );

  return $data;
}

Of course this doesn't look for a node table, it looks for a cms_connector table in my cms database, which doesn't exist.

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'cms.cms_connector' doesn't exist

http://views-help.doc.logrus.com/help/views/api-tables says that the key within $data…

should be the actual database name of the table (not including prefix), but it can be an alias as long as the join information (explained later) contains the real name of the table.

I don't actually want to do a join, I want to create a base table from the other database's node table. But I tried this anyway (even though it doesn't really make sense) after seeing http://drupal.org/node/1713010#comment-6310438:

$data['cms_connector']['table']['join'] = array(
  'node' => array(
    'left_field' => 'nid',
    'left_table' => 'node',
    'field' => 'nid',
    'table' => 'node',
  ),
);

I've searched around here, d.o, and Stack Overflow, but most of what I'm finding is Some-other-database-to-Drupal, not Drupal-to-Drupal.

I found the https://drupal.stackexchange.com/questions/12736/using-nodes-from-another-site-database question here, but it's about node references, and is unanswered.

I thought about using Services to read XML/JSON from the centralized site, but there are too many hurdles to that approach. I don't really want to synchronize nodes either.

Answer 1

Consider looking at the Forena module. It can be used to query (not update) the data in other databases. Various DBMS formats are supported, such as another Drupal database also.

Forena is built of the idea of using SQL to get data out of a database and use XHTML and CSS to format it into web reports. For more details about Forena, 2 types of documentation are available:

• Community documentation.

• Documentation that comes with Forena, which you can access right after install and enable of the module. Checkout the demo site for an online example of the current:

  • Forena documentation - use the link 'Reporting documentation' or visit relative link /reports/help.
  • Forena samples - use the link 'Reporting samples' or visit relative link /reports/samples (these samples are fully functional, so make sure to experiment a bit with it, such as the drill downs available on the SVG Graph sample).

Disclosure: I'm a co-maintainer of Forena,
I hope this does not violate the site's policy on self-promotion.

Answer 2

You can export content using the Views module to export data and import them using the Feeds module (you can import periodically using Rules module).

Answer 3

Check this modules:

EntityFieldQuery Views Backend or Web Service Data

Answer 4

One option using settings.php file. Configure 2nd database instance in settings.php file. After setting up instance use that db using db_set_active('drupal2') function and do anything with your second db.

e.g.

$databases = array(
    'default' =>
    array(
        'default' =>
        array(
            'database' => 'drupal1',
            'username' => 'root',
            'password' => '',
            'host' => 'localhost',
            'port' => '',
            'driver' => 'mysql',
            'prefix' => '',
        ),
    ),
    'cms' => // additional database starts here
    array(
        'default' =>
        array(
            'database' => 'cms',
            'username' => 'root',
            'password' => '',
            'host' => 'localhost',
            'port' => '',
            'driver' => 'mysql',
            'prefix' => '',
        )
    ),
);

in your module you can use both database as below:

db_set_active('cms');
$node = node_load(10);
var_dump($node):