How to Use MySQL Views As Entities for Drupal Views

The Problem

Drupal's Views module is a powerful tool for displaying data from your database. However, it's limited to displaying data from entities that are already defined in Drupal. What if you need to display data that comes from multiple tables or requires complex SQL queries? This is where MySQL views come in.

The Solution

By creating a MySQL view and then exposing it as an entity in Drupal, you can use the Views module to display complex data that would otherwise be difficult or impossible to show.

Step 1: Create a MySQL View

First, you need to create a MySQL view that contains the data you want to display. For example, let's say you want to show a list of users along with the number of nodes they've created of a certain content type.

CREATE VIEW user_node_counts AS
SELECT u.uid, u.name, COUNT(n.nid) AS node_count
FROM users u
LEFT JOIN node n ON u.uid = n.uid AND n.type = 'article'
GROUP BY u.uid, u.name;

This creates a MySQL view called user_node_counts that shows each user's ID, name, and the number of article nodes they've created.

Step 2: Create a Custom Module

Next, you need to create a custom module that will expose your MySQL view as an entity in Drupal. Create a new folder in your modules directory called mysql_views_entity and add the following files:

mysql_views_entity.info

name = MySQL Views Entity
description = Exposes MySQL views as entities for use with the Views module.
core = 7.x
dependencies[] = entity

mysql_views_entity.module

<?php

/**
 * Implements hook_entity_info().
 */
function mysql_views_entity_entity_info() {
  $entities = array();
  
  $entities['user_node_counts'] = array(
    'label' => t('User Node Counts'),
    'controller class' => 'EntityAPIController',
    'base table' => 'user_node_counts',
    'entity keys' => array(
      'id' => 'uid',
      'label' => 'name',
    ),
    'module' => 'mysql_views_entity',
    'entity class' => 'Entity',
    'view modes' => array(
      'full' => array(
        'label' => t('Full'),
        'custom settings' => FALSE,
      ),
    ),
  );
  
  return $entities;
}

/**
 * Implements hook_views_api().
 */
function mysql_views_entity_views_api() {
  return array(
    'api' => 3,
    'path' => drupal_get_path('module', 'mysql_views_entity') . '/views',
  );
}

mysql_views_entity/views/mysql_views_entity.views.inc

<?php

/**
 * Implements hook_views_data().
 */
function mysql_views_entity_views_data() {
  $data = array();
  
  $data['user_node_counts'] = array(
    'table' => array(
      'group' => t('User Node Counts'),
      'base' => array(
        'field' => 'uid',
        'title' => t('User Node Counts'),
        'help' => t('Contains user IDs, names, and node counts.'),
      ),
    ),
    'uid' => array(
      'title' => t('User ID'),
      'help' => t('The user ID.'),
      'field' => array(
        'handler' => 'views_handler_field_numeric',
        'click sortable' => TRUE,
      ),
      'filter' => array(
        'handler' => 'views_handler_filter_numeric',
      ),
      'sort' => array(
        'handler' => 'views_handler_sort',
      ),
      'relationship' => array(
        'base' => 'users',
        'field' => 'uid',
        'handler' => 'views_handler_relationship',
        'label' => t('User'),
      ),
    ),
    'name' => array(
      'title' => t('Username'),
      'help' => t('The username.'),
      'field' => array(
        'handler' => 'views_handler_field',
        'click sortable' => TRUE,
      ),
      'filter' => array(
        'handler' => 'views_handler_filter_string',
      ),
      'sort' => array(
        'handler' => 'views_handler_sort',
      ),
    ),
    'node_count' => array(
      'title' => t('Node Count'),
      'help' => t('The number of nodes created by the user.'),
      'field' => array(
        'handler' => 'views_handler_field_numeric',
        'click sortable' => TRUE,
      ),
      'filter' => array(
        'handler' => 'views_handler_filter_numeric',
      ),
      'sort' => array(
        'handler' => 'views_handler_sort',
      ),
    ),
  );
  
  return $data;
}

Step 3: Enable the Module

Enable your new module by going to the Modules page in your Drupal admin interface or by using Drush:

drush en mysql_views_entity -y

Step 4: Create a View

Now you can create a View that uses your MySQL view as its base. Go to Structure > Views > Add new view and select "User Node Counts" as the "Show" option.

Benefits of This Approach

Using MySQL views as entities for Drupal Views has several benefits:

1. Complex Queries: You can use the full power of SQL to create complex queries that would be difficult or impossible to create using the Views UI.
2. Performance: MySQL views can be optimized for performance, especially for complex queries that join multiple tables.
3. Flexibility: You can create multiple MySQL views for different purposes and expose them all as entities.

Limitations

There are a few limitations to be aware of:

1. Read-Only: MySQL views exposed as entities are read-only. You can't use them for creating or editing data.
2. Maintenance: If your database schema changes, you'll need to update your MySQL views accordingly.

Conclusion

By combining MySQL views with Drupal's entity system and Views module, you can create powerful, flexible displays of complex data. This approach is especially useful for reporting and analytics, where you need to show data from multiple sources in a single view.