Views: Using relationships
Views allows site builders to join field data from several tables by means of Relationships. This chapter shows some common use cases for using relationships.
Table of contents
Drupal projects discussed in this chapter: Devel, Entity Registration.
Introduction
In Drupal 8/9, Views is in core, so there is no need to install a contributed project.
When a new view is added in Drupal 8/9, the first option to set is
the entity to show (e.g. Custom Block, Comments, Log entries, Files, Content Revisions, Content, Taxonomy Terms, Users,, etc.). Each entity have its own base
table in SQL database. Most of these tables have the same name as the
entity, but not all: For example, the machine name of the base table for
the Content entity is {node}.
In the "View settings" panel, you select the type of entity to
show, etc. This produces a basic SQL SELECT statement to
pull out data of that entity.
However Views is capable of adding a left join to the query. This is called a "relationship". Relationships are mainly used to pull in fields to display them, or to filter on fields that only exist in related tables.
Setting up
Install the Views and Views UI modules as you would normally install Drupal modules.
If your site already has content, you may skip this step and work with that content instead. However, if you're testing this on a newly created test site, you can generate dummy content as follows:
Install the Devel project. Enable the Devel generate module. Create a test user (name it "test"). Navigate to and generate 50 nodes of type Article. They will be randomly assigned to existing users.
Show user name
A much requested use case is to show the user name of the user that created the content.
In Drupal 7 it was necessary to add an relationship to do this. Now it looks like adding the field "Authored by" does the trick.
Initially, after adding a new and selecting the base table, you
will only be able to select fields to show that exists in that base
table. For example, with a Content view (base
table {node}), you can get the User ID of the
author, but not the author's username as a string, because the
user name only exists in the Users table.
By creating a relationship to "User: name" you can add a field with the user name.
See DO: Add a relationship to a view and DSE: How to get user name in a view instead of uid for recipes.
[More TBA]
Filter on relationship
An often requested use-case is to show the user a list of only his or her own content. The regular filter in Views let us use "Content: Author uid" as filter criterion, but only with a static value for Usernames.
To filter on the currently logged in user, we need to create a relationship. Do as follows:
Navigate to . Name it "Own content". Change the display format to be a HTML list of fields. Just leave everyting else set to the defaults.
Click .
Add a relationship. Expand the third column ("Advanced") and next to "Relationships", click . Locate the "Content: author" relationship and select it by ticking the box.
Click twice (i.e. use the default configuration).
Locate the "Filter Criteria" area in the first colum side and click . Locate the "User: current" relationship and select it by ticking the box."
Click .
On the next screen, select "Yes" to configure the filter criterion to only show the current user.
Click (top right) to save the view. It should look like the image below.
Now, when a user visits the path /own-content, she or he should see her/his own content.
If you enable showing the SQL (it can be toggled by navigating
to and
visiting the tab), the SQL should
look like this, where x is the uid of the
currently logged in user:
SELECT node.title AS node_title, node.nid AS nid, node.created AS node_created
FROM
{node} node
LEFT JOIN {users} users_node ON node.uid = users_node.uid
WHERE (( (node.status = '1') AND( (users_node.uid = 'x') )))
ORDER BY node_created DESC
LIMIT 10 OFFSET 0
You may also use a contextual filter to filter a view to only show content created by the currently logged in user. See the note: Views: Contextual filters for details.
Showing node title
The image below show a view that is set up to show the
node Titles and Registration IDs of registratins for
some events. The base table in this case
is {registration} (from the
Entity Registration project).
In addition to pulling the node title from the {node},
we're reusing the method described above for filtering the content to
onlu show registrations owned by the current user.
This produces the following SQL (where x is the uid of the currently logged in user):
SELECT registration.registration_id AS registration_id,
node_registration.title AS node_registration_title,
node_registration.nid AS node_registration_nid
FROM
{registration} registration
LEFT JOIN {users} users_registration ON
registration.user_uid = users_registration.uid
LEFT JOIN {node} node_registration ON
registration.entity_id = node_registration.nid AND
registration.entity_type = 'node'
WHERE ((( (users_registration.uid = 'x') )))
LIMIT 10 OFFSET 0
Final word
Here are links to tutorials about using Relationships:
- Display a parent node and a view of child nodes that reference it.
- Add a relationship to a view (to show user name).
Last update: 2016-04-10 [gh].