The Views module has been a cornerstone of Drupal ever since it was introduced in 2006. In honor of its being rolled into the core application in Drupal 8, we’d like to share some tricks we’ve been using to make views even more flexible and powerful.
Since a large part of our focus is on integrations, and we like to use Drupal as a front-end, we’ve learned to love Entity API and Views. In this post we’re going to show you how easy it is to create a Mysql view, define an Entity for it using Entity API, and use that Entity in a View.
First things first, why use a Mysql view?
A Mysql view allows you create a relationship map between core Drupal database tables and a table or tables in an external database. This means that you can define a relationship to content from a completely different application without the need for a service layer, provided you have read access to the other database.
How to setup a view in your Drupal database?
mysql create view drupal_database.my_entity_table_name
AS
SELECT
ddusers.uid as uid,
odusers.user_email as email,
odusers.student_id as suid,
odusers.first_name as first,
odusers.last_name as last
FROM
(drupal_database.users dduser
JOIN other_database.users odusers
ON dduser.mail = oduser.user_email);Imagine you have an install of Drupal and an install of another application on the same web server. Both applications store user data independently, and you’d like to map the data between Drupal and SystemX so you can use Views in Drupal to display it all or use it in-line with other user profile information. The first thing you need to do is decide exactly what points of data you want from SystemX, in our above example we’ve chosen email, student_id, first_name, and last_name. The next thing you need to determine is how those records correlate to one another. In the example we’ve provided above the ‘email’ field is the same in Drupal and SystemX. So we’ve decided what fields we need and how they relate to Drupal users, now we create a view with a name that makes sense for the context, and we’ve crafted a ‘SELECT’ query to populate the view with data mapped between the systems.
We’ll start with the ‘FROM’ section. You’ll see we’re calling data from the ‘drupal_database’.’users’ table, and we’re aliasing that as ‘dduser’. Next, we’re ‘JOIN’ ing the dduser table to the ‘other_database.users’ table. The ‘other_database’ is for SystemX, and we’ve aliased that as ‘odusers’. We’re joining those two tables on the user email value, because they’re the same in both Drupal and SystemX. What this means, is that when we call ‘ddusers.uid as uid’ and ‘odusers.student_id as suid’ we’re going to get the student_id value from SystemX for the uid of the user account in Drupal that have that email address in common.
When this command is done, we can test it by performing a simple SELECT query against the table to make sure we’ve set up the mysql view properly.
SELECT * FROM drupal_database.my_entity_table_name LIMIT 1;We’ve put a limit of 1 records to return from this view because there could be hundreds or thousands of results depending on how many users you have in Drupal and SystemX, and we really only need to confirm that the view is returning the values we expect. If this all worked out you should see output similar to this.
+-----+-------+------+-----------+----------+
| uid | email | suid | firstname | lastname |
+—–+———————+———–+————+———–+
| 1 | admin@here.com | 24 | admin | admin |
+—–+———————-+———-+————+———-+
1 row in set (0.00 sec)How do I create an Entity using EntityAPI to map to a Mysql view?
EntityAPI has been a profound contribution to Drupal and has made more things possible with far less effort in terms of creating entities programatically. The first step here is going to be creating a new module. You’ll need to put a .install file, a .info file, and a .module file into a directory for your new module. For those of you unfamiliar with basic module development I would suggest you check out the following excellent write-ups already provided by the Drupal community.
Writing module .info files (Drupal 7.x)
Creating modules – a tutorial: Drupal 7.x
I’m not going to cover the .info file in this post because I feel that it is adequately covered in the links I’ve provided, but I will explain what needs to happen in the .install file. The .install file is where you’re going to define the ‘schema’ of the mysql view you’ve created for consumption by the EntityAPI.
/**
* Implements hook_schema().
*/
function my_module _schema() {
$schema[‘ my_entity_table_name’] = array(
‘description’ => ‘Stores SystemX user data associated with a Drupal user.’,
‘fields’ => array(
‘uid’ => array(
‘type’ => ‘int’,
‘not null’ => TRUE,
‘description’ => ‘The foreign key to the drupal users table.’,
),
’email’ => array(
‘type’ => ‘varchar’,
‘length’ => 100,
‘not null’ => TRUE,
‘description’ => “The user’s email address.”,
),
‘suid’ => array(
‘type’ => ‘int’,
‘not null’ => TRUE,
‘description’ => “The student_id from SystemX.”,
),
‘first_name’ => array(
‘type’ => ‘varchar’,
‘length’ => 100,
‘not null’ => TRUE,
‘description’ => “The user’s first_name from SystemX.”,
),
‘last_name’ => array(
‘type’ => ‘varchar’,
‘length’ => 100,
‘not null’ => TRUE,
‘description’ => “The user’s last_name from SystemX.”,
),
),
‘primary key’ => array(‘uid’),
);
return $schema;Above we’ve provided an example of what our .install file would look like. We’ve created a schema mirroring the fields in the mysql view we created in the Drupal database above. This is what allows us to create a Drupal entity that can read from our database view.
Next, you will create the Entity object in your .module file.
/**
* Implements hook_entity_info().
*/
function my_module_entity_info() {
$entity_info = array();
$entity_info[‘my_entity’] = array(
‘label’ => t(‘My Entity’),
‘entity class’ => ‘Entity’,
//Here we use the ‘EntityAPIController to do all of the heavy lifting.
‘controller class’ => ‘EntityAPIController’,
//Here we invoke the integration between Drupal entities and Views.
‘views controller class’ => ‘EntityDefaultViewsController’,
//Here we make sure to use the table name of the mysql view we created
‘base table’ => ‘ my_entity_table_name ‘,
‘fieldable’ => FALSE,
‘exportable’ => FALSE,
//Here we define the ‘key(s)’ that Drupal uses to understand which entity is which.
‘entity keys’ => array(
‘uid’ => ‘uid’,
‘label’ => ‘User Id’,
),
‘bundles’ => array(),
‘label callback’ => ‘entity_class_label’,
‘uri callback’ => ‘entity_class_uri’,
//Here we set the Drupal permission that users need to view this entity content.
‘access callback’ => ‘access content’,
‘module’ => ‘my_module’,
);
/**
* Implements hook_entity _property_info().
*/
function my_module_entity_property_info() {
$info = array();
$my_entity_properties = &$info[‘my_entity’][‘properties’];
/*Here we’re defining the properties of the entity and what those properties map to in the database. We need to define each of the fields from the mysql view we created as properties in our entity, and map those properties to the ‘schema field’ value from the schema we created in our .install file.
*/
$my_entity_properties = array(
‘uid’ => array(
‘label’ => t(‘UID’),
‘description’ => t(‘Drupal UID of associated user’),
‘type’ => ‘integer’,
‘schema field’ => ‘uid’,
),
’email’ => array(
‘label’ => t(‘City’),
‘description’ => t(‘SystemX user email’),
‘type’ => ‘text’,
‘schema field’ => ’email’,
),
‘first_name’ => array(
‘label’ => t(‘First Name’),
‘description’ => t(‘ SystemX user First Name’),
‘type’ => ‘text’,
‘schema field’ => ‘first_name’,
),
‘last_name’ => array(
‘label’ => t(‘Last Name’),
‘description’ => t(‘SystemX user Last Name’),
‘type’ => ‘text’,
‘schema field’ => ‘last_name’,
)
);
return $info;
}
/**
* Implements hook_entity_property_info_alter().
*/
function my_module_entity_property_info_alter(&$info) {
$properties = &$info[‘my_entity’][‘properties’];
/*Here we are altering the ‘uid’ property of our entity so Drupal will know that it should see the ‘uid’ value as the Drupal user object, which is what allows us to reference our entity from a User View.
*/
$properties[‘uid’] = array(
‘label’ => t(“User”),
‘type’ => ‘user’,
‘description’ =>
t(“The user account for this information.”),
‘required’ => TRUE,
‘schema field’ => ‘uid’,
);
}If you look through the above provided code the inline comments should explain how to create an Entity programmatically using EntityAPI. It should also provide details as to what the parts are doing and how they all fit together.
Put the pieces together, turn your new module on in Drupal, and get started using your integrated data in Views.
How do I use Views to show my custom Entity information when I’m looking at a User View?
Here you create a View and select the ‘Users’ option from the select drop-down. Name it whatever you like.
Here you expand the ‘Advanced’ section on the right side of the View settings page, and click on ‘Add’ next to Relationships.
Here you should see your entity as a checkbox option, if you do not, then something has gone wrong and you should go back over the steps, check the Drupal report logs, and/or consult the Drupal API documentation. Sometimes something as simple as clearing Drupal cache will resolve problems at this stage, other times a typo here or a missing quote or semi-colon there can lead you down a rabbit hole.
In any case, check the checkbox for your entity and add the relationship to the view.
Finally, when you go to add fields to your View you should now see the fields that you defined as properties of the entity and be able to add them to your view and use them as you see fit.
Using this method we have been able to create rich and rock-solid integrations between Drupal and a handful of other systems that use Mysql. It has afforded us a lot of flexibility in how we approach bringing our clients the very best Drupal has to offer.