Adding complex conditions to views with a query alter hook

Adding complex conditions to views with a query alter hookMay 26th 2015
What we were doing

Recently one of our clients requested an update to the visibility settings in their content and for these access settings to filter through to some views they had set up. Using Organic Groups, we could easily create public and private content, but the client wanted more fine-grained permissions that allowed for content to be restricted by user status as well.

With the help of the OG Extras module, we created the following options:

Publicly accessible to all users
Restricted to only logged-in users
Restricted to only users in one of the content's groups
Private to only the user creating the content

That was easy enough: we updated the field base to include the new options, keyed using the constants defined in the OG Access submodule (and adding one of our own in a custom module where all of this work took place). The tricky part was adding filtering to the views, as there was now no easy way of telling views to filter correctly. So, hook_views_query_alter() to the rescue!

Why we were doing it

It's important to point out that this isn't always the solution you should seek. Adding a query alter hook makes it more difficult for a site to be maintained - site builders can't see it in the UI, and if you don't clearly document your work, then you run the risk of having it overridden by an over-zealous administrator who renames the view, or something similar.

In our case, we were unable to use the filtering that Views offers out-of-the-box to get the results we needed. Not only did we need to know what permissions were set on the content, but we also needed to know whether the user was logged in, whether the user looking at it was the user who created it, and whether the user looking at it was in one of the groups that the content appeared in.

Simply put, Views couldn't handle all of those filters at once through the UI, so our only option was to modify the query directly.

Planning

Before diving into the code, it became apparent that we would need to figure out exactly what data needed to be displayed in each use case, and how to set up the code. We began with a list of criteria that would lead to the view being displayed:

User is anonymous AND the group content access is set to 'public - all including anon'.
User is logged in AND the group content access is set to 'restricted - only auth users' OR content is in one of the user's groups.
User is logged in AND the group content access is set to 'private - only users in these groups' AND content is in one of the user's groups.
User is logged in AND they created the content.

From there, it was easy to start breaking the checks down and order them in such a way that each new check will override the previous ones.

Is the user logged in? If no, then only show public content. If yes, show content for members.
Is the content in one of the user's groups? If yes, then show it. If not, don't.
Did the user create the content? If yes, show it.

This could all be achieved by adding a db_or() condition to the query in hook_views_query_alter().

Setting up the code - step by step

Bear in mind that hook_views_query_alter() operates on all Views queries, so it's a good idea to make sure you're working with the correct view/query before going ahead. For us, a simple check against the $view->name was sufficient.

In order to retrieve some of this data, we need some extra tables to be added and to cue up some other variables.

$anon = user_is_anonymous();

$query->add_table('field_data_group_content_access');
$query->add_table('og_membership_node');
$query->add_table('users_node');

// Set up our db_or. Either...
$conditions = db_or();

The first check we need to make is whether or not the user viewing the view is anonymous. If they are, we don't need to do too much else - just add the condition and continue.

if ($anon) {
// ... user is anonymous and content is public.
$conditions->condition('field_data_group_content_access.group_content_access_value', array(OG_CONTENT_ACCESS_PUBLIC));
}

Then comes the trickier bit. The user is obviously not anonymous, so we need to check the status of the content, which groups it belongs to, and whether the author of the content is the one viewing it.

First things first, using the global $user variable, we can check the user's roles. For this client, we were also checking for the 'member' role, but you can just as easily remove this check (assuming all authenticated users can view this content).

else {
global $user;

// ... user is logged in and content is restricted to member users.
$access_values = array(OG_CONTENT_ACCESS_PUBLIC);

if (in_array('member', $user->roles)) {
$access_values[] = OG_CONTENT_ACCESS_RESTRICTED;
}

// Add the condition.
$conditions->condition('field_data_group_content_access.group_content_access_value', $access_values);

The next step is to load the logged in user's groups and filter the view by the group IDs. For this, we'll need to create a db_and() - we want to make sure the content has one of the $access_values we've set up, and also that it is in one of their groups. If the user doesn't have any groups, there's no point in adding this condition.

// ... user is logged in and content is in their groups.
$gids = og_get_groups_by_user(NULL, 'node');
if (!empty($gids)) {
$access_values[] = OG_CONTENT_ACCESS_PRIVATE;

$group_and = db_and();
$group_and->condition('og_membership_node.gid', $gids, 'IN');
$group_and->condition('field_data_group_content_access.group_content_access_value', $access_values);

// Add the condition.
$conditions->condition($group_and);
}

Finally, add a check to see if the content being viewed is content that the user has created themselves. If they've created it, they should be able to view it, regardless of any other permissions set on it.

// ... user is logged in and they created the content.
$conditions->condition('users_node.uid', $user->uid);
}

And to wrap it all up, we need to add the condition to the query.

$query->add_where(0, $conditions);
Final structure

Fragmenting the code like this makes it difficult to understand what's going on, and I whizzed quite quickly through it. In the end, though, you should end up with a db_or() statement that looks something like one of these. Remember, there is a different $conditions value depending on whether the user is logged in or not.

For anonymous users:

$conditions = db_or()
->condition('field_data_group_content_access.group_content_access_value', array(OG_CONTENT_ACCESS_PUBLIC));

For logged-in users:

$conditions = db_or()
->condition('field_data_group_content_access.group_content_access_value', $access_values)
->condition(db_and()
->condition('og_membership_node.gid', $gids, 'IN')
->condition('field_data_group_content_access.group_content_access_value', $access_values)
)
->condition('users_node.uid', $user->uid);

Good luck!