Recently while working at my day job the need arose to utilize multiple databases to deliver a result in Drupal’s Views module. Not only did we need to utilize multiple databases but the databases reside on two different servers. I know that Drupal has the ability to connect to multiple databases but I had never considered how Views would utilize both databases or if it was even possible.
After some initial research I found the following posts on Drupal.org:
Essentially what I gathered from my research was that Views simply can’t utilize two databases to generate it’s results. This was not the answer I was hoping for. :(
Before abandoning Views on this project in favor of a custom solution I decided to see if I could exploit custom Views Handler’s, Views hooks, and the fact that Drupal supports connecting to multiple databases. My theory was this:
- Within a custom module I could define some pseudo columns on the node table within hook_views_data().
- I would then add these columns to the view’s fields within the Views module GUI. Since there wouldn’t actually be any table column associated with these columns there just wouldn’t be any data in the result set for each row.
- In hook_views_pre_render() I could exploit Drupal’s ability to connect to multiple databases. Here I have access to the view with the results already queried so I can run an additional query on my second database.
- After running my second query in hook_views_pre_render to get the additional data from the second database I will just slap it onto the views object in a custom property. I make sure that the data set is keyed by nid so that I can match up the data from the view with the data from my secondary query.
- I would then create a custom Views handler that would format the field columns I had added via the custom module and Views GUI. I access the additional data I had attached to the views object in the custom property and merge it into the rows output.
Here is some code to help illustrate the procedure.
Note:** The following is just pseudo code to help illustrate the technique I used. It is not cut and paste ready. However if you are still confused on how the following code works, please feel free to ask questions in the comments.
First, make sure you have your secondary database setup in settings.php
In a custom module, implement the required hooks:
In your custom module’s .info file you will need to add the handler class file so that it gets required
Finally, create your views handler. It should probably extend from views_handler_field_entity as that is the parent class that other more custom views field classes derive from. You could also extend one of the more specific child classes if you wanted to utilize and inherit some of their functionality.
I hope the general concept of this views hacking procedure isn’t too convoluted and that most people will find it understandable. If anyone has any questions or knows of a cleaner more efficient way to accomplish what I did here please leave me a comment.