Complex View Contextual Filter with User Relationships — how would I achieve this?

Question

As I mentioned in the question I raised earlier this week, I'm using User Relationships to create unidirectional friendships between users on my site.

The friending paradigm is like Twitter, where Alice may follow Bob and that does not imply that Bob also follows Alice. That said, Bob is able subsequently to choose to follow Alice.

Looking in the database table user_relationships, I can see that this situation results in two rows for a reciprocated relationship:

mysql> select * from user_relationships where requester_id in (187767, 190348)
  and requestee_id in (187767, 190348);

+--------+--------------+--------------+------+----------+------------+------------+-------+
| rid    | requester_id | requestee_id | rtid | approved | created    | changed    | flags |
+--------+--------------+--------------+------+----------+------------+------------+-------+
| 200726 |       187767 |       190348 |    1 |        1 | 1331657565 | 1331657565 |     0 |
| 200723 |       190348 |       187767 |    1 |        1 | 1331656437 | 1331656437 |     0 |
+--------+--------------+--------------+------+----------+------------+------------+-------+
2 rows in set (0.00 sec)

For ease of discussion here, let's talk about a specific View — that of "users who follow you but you don't follow back". For these users, there will be a row in user_relationship with requestee_id being $current_user‑>uid, let's say this has a requester_id of $follower‑>uid. There will be no row, however where $current_user‑>uid is the requester_id and $follower‑>uid is the requestee_id (which would indicate that the current user had reciprocated by following back). So if we're looking at the output above, it would be the situation where only the first row (rid = 200726) exists, from the perspective of user 190348.

This is slightly easier to explain with a Venn diagram that I've hastily knocked-up:

; my View here is of the area labelled C.

Now in my View (of which there is a now out-of-date screenshot), I have two Contextual Filters:

• "User relationships: Requester user", bound to the "requester" Relationship and set to Exclude; and
• "User relationships: Requestee user", bound to the "requestee" Relationship, not set to Exclude.

So far so good. Except this shows me all users who follow $current_user, not just the ones that $current_user does not also follow back — B ∪ C. If I want to show only the users in C, then I also need to check that no reverse row exists. If I go to add a new Contextual Filter, I don't have any option to check something like this (as it is not a property of the individual row I'm showing).

So how do I achieve this? I guess I'm going to have to write some PHP to prevent displaying users where this is the case and there are custom modules in my site already, but I don't understand Drupal well enough to know what hook I'm looking for or where to intercept the processing of the rows being displayed.

Help, please? :o)

Answer 1

Fixed it.

Last night I noticed that I have the ability to add PHP Filter Criteria (which I'd somehow not noticed before!), coming from the Views PHP Module. It turns out that this allows me to enter arbitrary PHP and it will take anything that returns TRUE as meaning the row should be removed from the resultset.

So I added a Filter Criterion that checks the SQL query Select * From user_relationships Where requester_id = :current_uid And requestee_id = :follower_uid and I return TRUE; if that query results in more than zero rows.

To do this, I needed to add new Fields, both set to be "Excluded from display":

• User: User ID (bound to Requester), with an administrative title of "Follower UID (not displayed)". This gets passed as the variable $row->uid
• User: User ID (bound to Requestee), with an administrative title of "Current UID (not displayed)". This gets passed as the variable $row->uid_1

Then in a new PHP Filter Criterion, I created a filter as follows:

$filter_result = db_query('Select * From user_relationships '.
  'Where requester_id = :current_uid And requestee_id = :follower_uid',
    array(
        ':current_uid' => $row->uid_1,
        ':follower_uid' => $row->uid,
    ));

$count = $filter_result->rowCount();

return $count > 0;

Then those superfluous rows no longer appear in my output.

For some reason, I do need to assign $filter_result->rowCount() to the variable $count; trying return $filter_result->rowCount() > 0; directly didn't want to work for some reason.

But problem solved. At last :o)

---

Edit: I've just corrected the Field bindings, having noticed I was doing it differently in two different places and I'd put the wrong one in my answer here. Note that the User Relationships fields "UR: Requester user ID" and "UR: Requestee user ID" do not output user IDs, so do not work in this query; you do have to use two different "User: User ID" Fields, one bound to each end of the relationship. (Irritatingly, this means they have the really unhelpful variable names of $row->uid and $row->uid_1 when passed to the PHP Field; I haven't yet found a way around this.)

Answer 2

For the sake of usefulness for other users, other suggestions I didn't use include the following:

1. Turning on "Use aggregation" and messing with aggregation settings to look for the same Count() < 1 check.

2. Attaching to the hook_views_query_alter hook, like the instructions on Better WHEREs for your Drupal forums with hook_views_query_alter, which would allowme to have written some bespoke logic in a module using something like:

   function YOURMODULE_views_query_alter(&$view, &$query) {
       if ($view->name == 'YOURVIEW') {
           $view->query->add_where('advf_filter',
              "(node.type='%s' OR node_comment_statistics.comment_count >= %d)",
              'forum', 1);
           $view->query->set_group_operator('AND');
       }
   }
   

   which I helpfully found via #3686: Alter a Views query to use OR through the user interface from enjoylife.

There's also some documentation on Working with Views on Drupal.org, but I didn't find any of those particularly useful, as the information was mainly not what I was looking for.

It's also worth mentioning that the modeule Advanced Help does give some relatively basic contextual help within the Views UI, though you probably already know that, as my Drupal admin pages were screaming it at me ;o)

I hope this all proves useful to someone!