I'm working with the Private Message module and applied a patch for Views integration from dawehner (yay!). The patch works pretty well so I was able to create an admin dashboard that shows private messages and lets you filter the messages by subject text, body text, has been read, etc.

Then, I noticed that messages with the same message id were showing up in the results. This is because you get one private message sent to the recipient and one "sent" to the author (i.e. you get a copy for yourself when sending private messages). Ok, easy enough (I thought). I enabled Distinct in Views but that didn't work. Then I enabled Pure Distinct but that didn't work either. Bummer.

Ok, so then I remembered something about using aggregation in Views so I enabled Aggregate. Then all my results were gone except for one :/ I started googling and eventually found an issue comment that says you can't use Aggregate with Pure Distinct. Oh!

So... I turned off of Pure Distinct and low and behold I only had one result for each message id. Great! Until I looked more carefully. The "From" (author) and "To" (recipient) fields were the same for some results. Which is what I was trying to get rid of in the first place. Doh!

Then, I decided to scratch the "distinct" method altogether and do a hook_views_query_alter to add the where clause I needed:

where pm_index.recipient != pm_message.author

Should be easy, right? I tried to use the:

$query->add_where()

but couldn't figure out how to add my where clause. It appears that add_where is similar to DBTNG's condition() which is good for adding where clauses like:

where pm_index.recipient != 43

If you know how to add this where clause in Views, PLEASE LEAVE A COMMENT :) Or, better yet, if you know how to configure Views in the UI to do field1 != field2 that would be awesome!

Then I decided I would use hook_query_alter to add my where clause after Views was done with it. I've used condition() (which isn't what I needed) and looked through the docs and googled couldn't find what to do (maybe I was too tired by this point and claiming defeat :P). Then I had the recollection that I had used addExpression() before so I went down that path (whoops!). That was a dead end.

Meanwhile, I sent my plea to the twitterverse and a wonderful Fox replied with:

outside of views, api.drupal.org/api/drupal/inc…?

Well... silly me. <facepalm> There is a where() in DBTNG too. How did I miss that??? Thanks, Fox!!!

I figured my woes were solved and celebrated in advance :)

Then, I added the wonderfully simple code to my hook_query_alter:

$query->where('pm_index.recipient != pm_message.author');

and this is what ended up in the where clause:

where (11 is NULL) and (pm_index.recipient != pm_message.author)

What the?

I assumed I must have had some query alters left over... I searched and didn't. I assumed there was something wrong with my filters so I looked and all seemed fine (they were all exposed filters so none were in the query string by default). Then I used one of the exposed filters and the "11 is NULL" went away from the query.

I googled the "11 is NULL" thing and funnily enough I see it on some sites that have their query strings exposed on the page (maybe a coincidence?). But, no mention to how it gets there. I checked the Views issue queue for "11 is NULL" and "11" (though that is probably too short) and didn't notice anything obvious.

No worries! I just added a filter (not exposed) that I knew would always be okay. I set a filter so that it only grabbed messages if the body text was not null.

Voila :) Happy dance! After lots of pain and suffering, my view has my where clause added. It only needed 5 simple lines of code:

function mymodule_query_alter($query) {
  if ($query->hasTag('mytag')) {
    $query->where('pm_index.recipient != pm_message.author');
   }
}

I'm writing this up so that maybe it will help someone even if that someone is just my future self ;)

Note that I removed that body filter to try to reproduce the "11 is NULL" issue and it no longer shows up. So... my guess is that this post will be high in google for "11 is NULL"... if you are seeing this issue, my suggestion is to add a Views filter and then remove it and see the "11 is NULL" goes away ;)

Happy Friday!

Adding some keywords so that people might find this when they are running into a similar problem:

• compare fields in views
• compare columns in views
• field comparison
• column comparison
• comparing two fields
• comparing two columns