Drupal 7 Views: Making a block of links to related pages

Views and SQL

Using Drupal views basically is trying to figure out how to trick the machine into making the SQL query I would have written in five minutes. As a matter of fact, I don’t think I had a chance of getting this right, hadn’t I known SQL pretty well.

Or, as one of the help pages was kind enough to clarify (the left side is merely examples):

SELECT n.title, u.name <–> fields
FROM {node} n base table <–> view type
INNER JOIN {users} u ON n.uid = u.uid <–> relationship
WHERE n.status = 1 <–> filter
AND u.uid = arg(1) <–> argument
ORDER BY n.changed DESC <–> sort

and I’ll add:

WHERE n.nid = p.nid <–> Contextual filter
GROUP BY <–> Aggregation

A word about relationships: The relationship entries in the Views page define the “ON” part in the JOIN. To access the values of the fields in the joined tables (e.g. as a displayed field), just select the desired value in the first menu (e.g. “add field”), and then pick the administrative name in the “relationship” drop-down menu. For some reason, I had the initial expectation that the “ON” value would appear in itself in the field list, but it doesn’t, since it’s available just as any value, only picked from the respective table.

Recommended modules

The Development module (“devel”) allows a dump of all SQL queries made while producing the currently shown page. It’s recommended just to get an idea of what a database hog Drupal is. The second, very recommended module is PHP views, despite its development release status. The latter allows injecting small pieces of PHP exactly where they are needed. In particular, there’s the PHP:Global pseudofield for both display and sorting, so rather than banging the head on how to twist things around, just write a small and elegant snippet in PHP.

Injecting a view with PHP

Possibly using the PHP text format, just type in any node’s text input (useful for making a view within a book node):

<?php print views_embed_view('doc_outline', $display_id = 'default') ?>

where ‘doc_outline’ is the machine name for the view (as it appears in URLs related to the view) and ‘default’ could be exchanged with ‘page’ or ‘block’, but why bother if the view has a single format? See the API page.

There has also been suggestions about embedding a block in PHP, but I never tried that.

Strategy

The purpose: Making a “relevant pages” block, based upon common tags of the pages. Don’t tell me Drupal has something doing that, because I know. It’s just that I’ve learned that these easy solutions always end up with more work. Besides, I have a special thing I want.

The setting is as follows: I have a special taxonomy named “relevance terms”. Pages with common terms in this taxonomy are considered to have similar content. I didn’t use the original tags, because I may want to use them for something representative.

Also, each content page has an extra field “LinkText”, which contains the text to appear on links to the page. For example, the download page’s title is simply “Download” but the link to this page should say something more imperative.

The immediate (and wrong) way to go is to make a view of content titles. Without any filtering, you get all content pages. So use the context filter to get only the current page, and use relations to list all taxonomy terms. Now another relation filter to expand this to pages for each taxonomy term? But the context filter kills any other page than the currently displayed. It’s a dead end.

So the right way is to make a view of the page’s taxonomy terms. For each term, list the pages using it, and then squash duplicates. And then make the output nice. Easier said than done.

First steps

Add a new View, showing Taxonomy Terms of type Relevance Taxonomy. Don’t create a page, but a block. Display as an HTML list of fields. Save & Exit, and insert the block somewhere in the page, so it can be tested. Previews won’t work here, because it runs  on Taxonomy nodes, not pages. Set title and such.

The number of items should be limited, and I don’t use a pager.

Contextual Filter & Relationship

This is done first, so we don’t mess with aggregation, which is going to be set up pretty soon. Under Advanced, add a contextual filter on “Taxonomy Term ID”. The following window will complain that there’s no source for contextual filter, so a default must be supplied. This is because we’re running in block context. The source is taken from the page.

We want the node ID to be compared with the current page, so pick “provide default value” and “Taxonomy term ID from URL”. Uncheck “Load default filter from term page” but check “Load default page from node page” and also “Limit terms by vocabulary” and pick the Relevance Taxonomy as the chosen vocabulary. Under “More” check “Allow multiple values” This is necessary, since we don’t want just the first term to be used. I’m not sure if this item appears without setting up relationships. So if it’s missing, set up a relationship and come back to add this.

That’s it. Save and check up. We should now have a simple list of relevance terms in the view.

Next we add a relationship with the pages having the terms: Check “Taxonomy term: Content using relevance” (note that “relevance” is the name of the vocabulary here), check “Require this relationship” on the next screen (I suppose this makes an INNER JOIN as opposed to a LEFT JOIN), and save this.

Checking where we stand, we have each taxonomy term appearing a number of times. This is the natural behaviour of an inner join: Each combinations of terms and pages using them creates a line. Since the pages aren’t listed, we just see each term repeated.

And since we’re at it, let’s eliminate the shown page’s entry in the related pages’ list. We need negative contextual filtering here: So add a new contextual filter, check “Content: Nid” (it wasn’t there until we added the relationship). Provide default value as “Content ID of URL”, and under “More” check “Exclude”. So if the current page matches the shown page, it’s not shown.

Save and verify than one or a few items have disappeared from the list.

Aggregation

Aggregation is “GROUP BY” in SQL, meaning that several rows with the same value in one of the fields are turned into a single row. Instead of the field’s value we have the count of rows grouped together or the maximum, minimum, average value or whatever the database allows. Aggregation is needed to eliminate the duplicate rows created by the relationship (that is, the inner join).

True, there is a “Distinct” checkbox under “Query settings” but it’s ineffective, since each of these duplicate rows are indeed distinct when the database answers the query. What makes them duplicate is the fact that the taxonomy term is dropped in the display. “Distinct” just adds the DISTINCT word to the SQL query.

So at this point, change “Use aggregation” to Yes. Things will get slightly messier from this point on.

Adding fields

Rule #1 for fields: The order they appear matters. In particular when using rewrite rules: Inserting data from other fields in substitution patterns works only for fields above the displayed one (those declared before).

Remember that the goal is to show the Linktext field as a link to the page, and not just the title.

So the first field to add is the Node’s path (aliased link). We will use it later on. In the list, check “Content: Path”. Under “Aggregation type” pick “Group results together” which is what we pick all the time if not for any special reason. This choice wouldn’t appear without enabling aggregation, of course. On the third and last window, check “Exclude from display” unless you want to see it for debugging.

The second field to add is the link text. In the list, check “Content: LinkText”. Under “Aggregation type” pick “Group results together” and pick the “Entity ID” as group column, and no additional ones.

On the third page uncheck “Create a label” (no “LinkText:” shown to user). Under “Rewrite results” check “Output this field as a link”. Write [path] in the Link path text box. This string can be found in the Replacement Pattern list just below. The path was there because it was defined before the current field.

Check “Use absolute path” or the links start with a double-slash and render useless.

At this point I’ll mention that it’s possible to insert arbitrary HTML with replacement patterns. So it’s really useful.

Squashing duplicates

At this point it’s pretty evident that we have double entries, and these taxonomy terms should be removed.

So it’s time to edit the first field: The “Taxonomy Term: Name” and check “Exclude from Display”. But even more important, enter “Aggregation settings” and change “Aggregation type” to “Count”. The magic is that instead of a row for each taxonomy term, we get a single row with the number of them, ending up with a single row for each link.

Filter out inaccessible items

As is, users will see links to items the user can’t access. So let’s add a simple filter (“Filter Criteria”). Pick “Content: Published or admin” and “Group results together” in the two following menus. And then just apply on the next menu. Done.

Note that unpublished items will still appear for admins, since the criterion is access. Pick “Content: Published” and choose “Yes” to remove unpublished items for anyone.

Sorting

I have to admit that I failed on this one at first. My intention was to sort the output depending on the number of mutual tags. That would be easy in SQL, since the COUNT() can be applied a label with the AS keyword. I found nothing to assist this in the Views menus.

As it turned out, values of COUNT() are available, but not through the menu interface. With the Views PHP module, it’s a piece of cake.

Say that there’s already a field saying “COUNT(Taxonomy term: Name)”, then add a sort criteria of type global PHP and set the code to

return ($row2->name - $row1->name);

Since $row1->name is the count of the rows with the same name field, this simple chunk of code does the work.

Using the view in a non-Drupal page

Sometimes the whole framework is just too heavy for a site, and all that’s needed is just a view in a plain PHP file. So if a view block with the name “inject” exists in the system, the following code snippet displays it (and a lot of CSS mumbo-jumbo).

<?php

chdir('trydrupal');

define('DRUPAL_ROOT', getcwd());

require_once DRUPAL_ROOT . '/includes/bootstrap.inc';
drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);

print views_embed_view('inject', $display_id = 'default')
?>

Note the chdir(). It allows Drupal to be installed in a completely separate directory (“trydrupal” in this case).