Views & external database

By Alex Monaghan on 2 Jun 2010 at 21:32 UTC

I've connected OK to my external database and can key using the $user object making a link between Drupal and my database tables.

Pulling back a simple query from the other database as a block of text in a module is simple enough, however, what I would love to be able to do is create a view of clickable links to a page where I can build a form with data from the database, but I can't see any obvious way of doing this, I've read through the views API and can't see how to link to an external database. The alternative is to build the links in the module, but I'd like to use the views built in pager functionality. I've used views on other sites for filtered node lists and like the flexibility offered, so would really like to stick with views.

Can anyone point me at an idiot's guide to building a view from an external database table (well actually several joined tables)?

Comments

Yes, you can use views with

nevets commented 2 June 2010 at 22:08

Yes, you can use views with an external database but there is the limitation the view can not cross databases. The other aspect is you have to code the handlers for the tables you want to use in the external database..

The key to using an external database is in hook_views_data() when you define a table you include the 'database' setting, for example

  $data['Grants_Screen']['table']['base'] = array(
    'field' => 'Grant_',
    'title' => t('CUT Grant'),
    'help' => t("CUT Grants data."),
    'database' => 'db_name'
  );

Where db_name is one of your db_names defined in settings.php. Because I had a number of tables to work with and I did not want the database name hard coded I did

  $cut_db = variable_get('cut_database', 0);	
  ...

  // Advertise this table as a possible base table
  $data['Grants_Screen']['table']['base'] = array(
    'field' => 'Grant_',
    'title' => t('CUT Grant'),
    'help' => t("CUT Grants data."),
    'database' => $cut_db
  );

And made a small admin module that allowed the admin to select from the set of defined databases.

From experience, there is no idiot's guide to this, you need a good understanding of how you tables are related.

Also, in my case to avoid hard coding some data there where a few tables that we shadowed. In this case we shadowed some selected vocabularies, used to Drupal to maintain the vocabularies/terms and updated the external database at the same time. This allowed us show the same values regardless if we where looking at Drupal data or the external database.

Thanks, this has helped me

Alex Monaghan commented 3 June 2010 at 09:23

Thanks, this has helped me get a simple list from the database in the views page, now comes the complex bit where I work out how I'm going to implement it in the site :-)