Filter view by users without a matching record in the related userpoints table

Question

I found out that I'm getting a much better query performance if I use an INNER JOIN of users and userpoints compared to a LEFT JOIN.

Currently, new users have 0 userpoints when they register, I'll change that to 1 point with a rule.

To add 1 point to all users with 0 points I was thinking of using a view and Views Bulk Operations.

To get to "the point": I can't filter the view to show users with 0 points because they don't have a record in the userpoints table. This is the SQL that views tries to generate:

SELECT ..
FROM 
{users} users
LEFT JOIN {userpoints_total} userpoints_total ON users.uid = userpoints_total.uid
WHERE  (userpoints_total.points = '0') 

I don't think it's possible to generate a query with views to find users without a record in a related table? Or is there a trick?

Answer 1

I figured it out:

function MYMODULE_views_query_alter(&$view, $query) {
 if($view->name = "nameofview") {
     $query->where[1]['conditions'][] = array('field' => 'userpoints_total.points', 'operator' => 'IS NULL',);
    }
}

Answer 2

There is a trick: your condition needs to be … WHERE userpoints_total.points IS NULL this will be true for all users that don't yet have a row in userpoints_total. Afaik you can easily add that condition via the views interface.

To be on the safe side you can also use: WHERE userpoints_total.points=0 OR userpoints_total.points IS NULL.