1

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?

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',);
    }
}
| improve this answer | |
0

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.

| improve this answer | |
  • that userpoints fields doesn't have a filter option "is empty". – uwe Jul 28 '12 at 14:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.