Complex View Contextual Filter with User Relationships — how would I achieve this? 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&gt; 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‑&gt;uid, let's say this has a requester_id of $follower‑&gt;uid. There will be no row, however where $current_user‑&gt;uid is the requester_id and $follower‑&gt;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)
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-&gt;uid
User: User ID (bound to Requestee), with an administrative title of "Current UID (not displayed)". This gets passed as the variable $row-&gt;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' =&gt; $row-&gt;uid_1,
        ':follower_uid' =&gt; $row-&gt;uid,
    ));

$count = $filter_result-&gt;rowCount();

return $count &gt; 0;


Then those superfluous rows no longer appear in my output.

For some reason, I do need to assign $filter_result-&gt;rowCount() to the variable $count; trying return $filter_result-&gt;rowCount() &gt; 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-&gt;uid and $row-&gt;uid_1 when passed to the PHP Field; I haven't yet found a way around this.)