Filtering a Drupal 7 View based on multiple user profile fields

This is basically continued from the previous post Filtering a Drupal 7 View based on a user profile field, where a user called Juan asked a simple question:

“How do I filter by multiple user profile fields, so that all the terms must match?”

tl;dr: I just want the solution

This stumped me. I spent a good while clicking in the Views admin UI, and even longer writing a hook_views_query_alter() to bend the Views SQL query to my will.

Limitations with the Views admin UI

The problem is that with the Views UI, you can’t add multiple ON-clauses to the JOINs, which was exactly what would’ve done it. Here’s the query that Views generates when you follow the instructions in my previous post:

SELECT 
  node_taxonomy_index.title AS node_taxonomy_index_title, node_taxonomy_index.nid AS node_taxonomy_index_nid
FROM users users
LEFT JOIN field_data_field_colors field_data_field_colors
  ON users.uid = field_data_field_colors.entity_id 
  AND (field_data_field_colors.entity_type = 'user' 
  AND field_data_field_colors.deleted = '0')
INNER JOIN taxonomy_term_data taxonomy_term_data_field_data_field_colors 
  ON field_data_field_colors.field_colors_tid = taxonomy_term_data_field_data_field_colors.tid
LEFT JOIN field_data_field_fruit field_data_field_fruit 
  ON users.uid = field_data_field_fruit.entity_id 
  AND (field_data_field_fruit.entity_type = 'user' 
  AND field_data_field_fruit.deleted = '0')
INNER JOIN taxonomy_term_data taxonomy_term_data_field_data_field_fruit 
  ON field_data_field_fruit.field_fruit_tid = taxonomy_term_data_field_data_field_fruit.tid
LEFT JOIN taxonomy_index taxonomy_term_data_field_data_field_colors__taxonomy_index 
  ON taxonomy_term_data_field_data_field_colors.tid = taxonomy_term_data_field_data_field_colors__taxonomy_index.tid
INNER JOIN node node_taxonomy_index 
  ON taxonomy_term_data_field_data_field_colors__taxonomy_index.nid = node_taxonomy_index.nid
WHERE (( (users.uid = '1' ) ))
LIMIT 10 OFFSET 0

And here’s the query that would return the results we want, with added parts highlighted in red:

SELECT
  node_taxonomy_index.title AS node_taxonomy_index_title, 
  node_taxonomy_index.nid AS node_taxonomy_index_nid
FROM users users
LEFT JOIN field_data_field_colors field_data_field_colors 
  ON users.uid = field_data_field_colors.entity_id 
  AND (field_data_field_colors.entity_type = 'user' 
  AND field_data_field_colors.deleted = '0')
INNER JOIN taxonomy_term_data taxonomy_term_data_field_data_field_colors 
  ON field_data_field_colors.field_colors_tid = taxonomy_term_data_field_data_field_colors.tid
LEFT JOIN field_data_field_fruit field_data_field_fruit 
  ON users.uid = field_data_field_fruit.entity_id 
  AND (field_data_field_fruit.entity_type = 'user' 
  AND field_data_field_fruit.deleted = '0')
INNER JOIN taxonomy_term_data taxonomy_term_data_field_data_field_fruit 
  ON field_data_field_fruit.field_fruit_tid = taxonomy_term_data_field_data_field_fruit.tid
LEFT JOIN taxonomy_index taxonomy_term_data_field_data_field_colors__taxonomy_index 
  ON taxonomy_term_data_field_data_field_colors.tid = taxonomy_term_data_field_data_field_colors__taxonomy_index.tid
LEFT JOIN taxonomy_index taxonomy_term_data_field_data_field_fruit__taxonomy_index 
  ON taxonomy_term_data_field_data_field_fruit.tid = taxonomy_term_data_field_data_field_fruit__taxonomy_index.tid
INNER JOIN node node_taxonomy_index 
  ON taxonomy_term_data_field_data_field_colors__taxonomy_index.nid = node_taxonomy_index.nid
  AND taxonomy_term_data_field_data_field_fruit__taxonomy_index.nid = node_taxonomy_index.nid
WHERE (( (users.uid = '1' ) ))
LIMIT 10 OFFSET 0

Nearly a solution

Adding the first part is relatively straightforward with a hook_views_query_alter():

  // Add a new table in the query, based on an existing table.
  $new = $query->table_queue['taxonomy_term_data_field_data_field_colors__taxonomy_index'];
  $new['alias'] = str_replace('colors', 'fruit', $new['alias']);
  $new['join']->left_table = str_replace('colors', 'fruit', $new['join']->left_table);
  $new['relationship'] = str_replace('colors', 'fruit', $new['relationship']);
  
  // Need to make sure the tables are JOINed in the right order.
  $key = array_pop(array_keys($query->table_queue));
  $temp = array_pop($query->table_queue);
  $query->table_queue['taxonomy_term_data_field_data_field_fruit__taxonomy_index'] = $new;
  $query->table_queue[$key] = $temp;

The next part, however, which is adding the other ON-clause to the last JOIN, I couldn’t figure out. There is a place where this could be placed in the query object’s table_queue structure. This very nearly does what we need:

  $query->table_queue['node_taxonomy_index']['join']->extra = array(
    array(
      'table' => 'taxonomy_term_data_field_data_field_fruit__taxonomy_index',
      'field' => 'nid',
      'value' => 99,
    ),
  );

We can add new ON clauses to JOINs, but the right hand side of the operator is always a value:

INNER JOIN {node} node_taxonomy_index 
  ON taxonomy_term_data_field_data_field_colors__taxonomy_index.nid = node_taxonomy_index.nid 
  AND taxonomy_term_data_field_data_field_fruit__taxonomy_index.nid = '99'

At least I could figure out no way to replace the ’99’ with a reference to node_taxonomy_index.nid. If anyone knows how to do that, please leave a comment.

I also tried another variant of this, where I added a new WHERE-clause at the end of the query for the same effect. The same problem prevented it from working; I couldn’t add table references to the new clauses.

The simple, working, solution

Juan left another comment for me, where he pointed me towards a couple of old posts on drupal.org, which were discussing doing very similar things with Drupal 6. Looking at those posts, it finally clicked.

It’s quite simple, really, but somewhat ugly in my book, since you need to write PHP in the Views UI. In any case here goes:

  • Start by following the instructions for filtering by a single term
  • We’ll assume that you have another Term field in your content and user objects called “colors”
  • Add another contextual filter, “Content: Has taxonomy term ID”
  • Set the relationship to node_fruit
  • For “When the filter value is NOT in the URL” select “Provide default value”
  • Type: PHP code
  • Add the following code:
global $user;
$user = user_load($user->uid);
return $user->field_colors['und'][0]['tid'];

..and you should be done!

This entry was posted in IT. Bookmark the permalink.

11 Responses to Filtering a Drupal 7 View based on multiple user profile fields

  1. Juan says:

    WOW, thanks a lot for this! I’ve tried it and works perfectly also for several terms (4 in my case).
    Now I’m trying to do the same thing but with Profile2 fields (instead core profile fields). I’ve tried adapting that code and also adding the PHP in a different contextual filter (similar to this: http://stackoverflow.com/a/10199765/1901663), but without any luck yet…
    One of the PHP codes that I tried was the following (‘profile’ is the name of the Profile2 type I’m using):
    global $user;
    $profile = profile2_load_by_user($account->uid, 'profile');
    return $profile->field_colors['und'][0]['tid'];

  2. Juan says:

    I´ve also tried this ($user instead of $account):
    global $user;
    $profile = profile2_load_by_user($user->uid, 'profile');
    return $profile->field_colors['und'][0]['tid'];

    and also “profile2_load” instead of “profile2_load_by_user”.

    • amplifier says:

      $user is the correct variable to use. That’s the you make available by using global.

      The profile object probably isn’t formatted the same way as the Drupal core user object. Install the devel-module, if you haven’t yet, and use one of the debugging functions to see what’s inside the $profile, once you’ve loaded it.

  3. Rik says:

    Global Filter is good for this kind of stuff. The latest 7.x-1.x-dev allows you to use any number of Views to be filtered by fields on the user profile (both core and Profile2).

    • amplifier says:

      Nice! I’m going to have to remember that. I can already think of a couple of projects where something like that would’ve come in handy.

  4. Paul says:

    Thanks for this. Any idea how to make this work for multiple values. At the moment my second contextual filter (not UID) only seems to return matches for the first value in the field.

    For example if the user has selected ‘blue’, ‘green’ and ‘red’ in the colour field, the filter only shows nodes matching ‘blue’. I’ve checked the ‘Allow multiple values’ box. Any ideas? Thanks in advance.

  5. Nuria says:

    Hi!

    Thanks a lot for this code!!! But I have the same problem than Paul. My user can choose from multiples terms and only show the first term. ¿Can anyone give some help?

    thanks!!!

  6. Nuria says:

    Hi!

    I found a possible solution in other page, but I had to make some changes to make it works fine.
    The original code is in this page https://drupal.org/node/1058674.
    ——————————————
    My code (on contextual filter > Provide default vaule > PHP Code):
    global $user;
    $uid = $user->uid;

    $result = db_query(“SELECT field_tax_tid FROM {field_data_field_tax} WHERE bundle=’user’ AND entity_id = :uid”, array(‘:uid’=>$uid));

    foreach ($result as $record) {
    $terms[] = $record->field_tax_tid;
    }

    return implode(‘+’,$terms);

    And you must to check the option contextual filter > MORE > Allow multiple values

    Seems work! I hope that it will help!!!

    • john says:

      sir good morning, i use your code and edit the field but it doesn’t display my list of users sir based on the taxonomy of my currently log-in user. Please help ty.

  7. fet says:

    I couldn’t find the PHP Code type in my version of D7 so I used the solution provided here instead. It’s very counter intuitive for me to start with a view for a user but it works perfectly.

    https://drupal.org/node/1111722#comment-4315620

    • amplifier says:

      Odd, I just tried the instructions with a clean install, and I have the PHP code option available. When you’re in the window configuring the contextual filter, it’s an option under “When the filter value is not in the url” -> “Provide default value” -> “Type”.

      The solution you linked to is the same one I described in a previous post. That’s a well known solution that works when you only need to filter by a single term, or if you want to filter by multiple terms and return nodes where _any_ term matches. The solution described in this post is only needed if you want to filter by multiple terms so that _all_ terms must match.

Leave a Reply

Your email address will not be published. Required fields are marked *