How to sort empty values last in WordPress

For the past several days I’ve been hammering my head against a conundrum: how to get WordPress to sort a set of posts in ascending order, but with empty values at the end of the list instead of the beginning.

This seems like it should be a simple option in the query. But MySQL doesn’t offer a straightforward way to do this. There are some fairly simple MySQL tricks that will accomplish it, but there’s no way to apply those tricks within the context of WP_Query because they require manipulating either the SELECT or ORDER BY portions of the SQL query in ways WP_Query doesn’t allow. (I mean, you can write custom SQL for WP_Query, but if you’re trying to alter the output of the main query, good luck.)

I tried everything I could possibly think of yesterday with the pre_get_posts hook, but it all went nowhere, other than discovering a very weird quirk of MySQL that I don’t fully understand and won’t bother explaining here.

Sleep on it

I woke up this morning with an idea! I resigned myself to the fact that this ordering can’t happen before the query runs, but I should be able to write a pretty simple function to do it after the query has run.

Bear one key thing in mind: This is not going to work properly with paginated results. I mean, it’ll sort of work. The empty values will get sorted to the end of the list, but they’ll stay on the same “page” they were on before the query was run. In other words, they’ll be sorted to the bottom of page one, not of the last page. Anyway… consider this most useful in cases where you’re setting posts_per_page to -1 or some arbitrarily large number (e.g. 999).

The function

This simple (and highly compact) function accepts a field name (and a boolean for whether or not it’s a custom field [meta data]), then takes the array of posts in the main query ($wp_query), splits them into two separate arrays — one with the non-empty values for your selected field, one with the empty values — and then merges those arrays back together, with all of the non-empty values first. (Other than shifting empties to the back, it retains the same post order from the original query.)

function sort_empty_last($field, $is_meta=false) {
  global $wp_query;
  if (!$wp_query->is_main_query()) { return; }
  $not_empty = $empty = array();
  foreach ((array)$wp_query->posts as $post) {
    $field_value = !empty($is_meta) ? get_post_meta($post->ID, $field) : $post->{$field};
    if (empty(implode((array)$field_value))) { $empty[] = $post; }
    else { $not_empty[] = $post; }
  }
  $wp_query->posts = array_merge($not_empty,$empty);
}

Calling the function

As I said, this function is designed to work directly on the main query. You just need to call the function right before if (have_posts()) in any archive template where you want it to apply. Because of the way it works — especially the posts_per_page consideration — I thought calling it directly in the template was the most clear-cut way to work with it. Here’s an example of the first few lines of a really basic archive template that uses it, looking for a custom field (meta data) called deadline:

<?php

get_header();

sort_empty_last('deadline', true);

if (have_posts()) {

Edge cases

Link

On the Behavior of the iPhone Mute Switch
Yesterday the New York Times reported on an incident where an audience member at a New York Philharmonic performance interrupted a Mahler symphony (literally… the conductor stopped the performance) with an iPhone alarm.

It was understandable, in context. And I think John Gruber gets it exactly right:

You can’t design around every single edge case, and a new iPhone user who makes the reasonable but mistaken assumption that the mute switch silences everything, with an alarm set that he wasn’t aware of, and who is sitting in the front row of the New York Philharmonic when the accidental alarm goes off, is a pretty good example of an edge case.

Why I’ve (mostly) stopped using Facebook

I’ve never been one for New Year’s resolutions, but I made a pair of them this year. And now that we’re almost two weeks into the new year, it seems like a good time to investigate whether I’ve been able to keep up with them.

My first resolution was a healthier lifestyle. Nothing drastic, and nothing too rigid. It’s a few simple rules:

1. No alcohol more than once a week. That glass of beer or wine… or two… at dinner adds a lot of calories.

2. No second helpings. It doesn’t take long for your appetite to adjust to match the amount of food you’re shoving down your gullet. Eat less, consistently, and pretty soon you’ll want less.

3. Eat more fresh fruit and vegetables. I’ve typically done OK with vegetables, but rarely touched fruit. Clementines are my new best friend.

4. Get at least some exercise. Who needs workout equipment when you’ve got a flight of stairs in your house?

And most importantly:

5. Don’t stress it too much. One day of breaking the rules doesn’t mean you’re off the diet.

So far, the first resolution is going along… pretty well. Mainly because of #5.

As for the second resolution… it was “No more Facebook.”

A few years ago, the need for such a resolution would have seemed absurd to me: I was incredibly disdainful of Facebook (and social networking in general) for ages. It wasn’t until I sort of (yeah, sort of) needed a Facebook account, for work-related research purposes in early 2008, that I got into it, and I was quickly hooked. Checking Facebook on my iPhone in every spare moment. Carrying on a third-person monologue in my head as I constantly anticipated my next Facebook post. It was kind of ridiculous. (Yeah, kind of.)

But this was another fairly easy resolution for me, and not just because of #5 (although it’s there). I had already begun to sour on Facebook in the final months of 2009, getting to the point where most of my posts on Facebook were only arriving there second-hand, via automatic cross-posting from Twitter (my new, slightly healthier, if only because it has less empty calories, social networking metaphorical junk food of choice) or from my blogs (via NetworkedBlogs).

So what was it that soured me on Facebook, that once indispensable hub of my ersatz social life? It was a variety of factors; factors which I have in my head named in honor of the Facebook friends in whom they’ve most strongly manifested for me (though whose names I will conceal here, since NetworkedBlogs is going to post a link to this blog entry on Facebook on my behalf). It was also the growing privacy and security concerns surrounding the fact that though many Americans fear the specter of an implausible — nay, impossible, owing to a lack of both funding and competence — Big Brother in our government, we’re only too willing to share every minute detail of our personal lives with the very real and technologically marvelous Big Brother of private enterprise.

This interview may be fake (and, then again, maybe not), but the programmatic surveillance it describes is real. I know, because in my line of work, I not only pay attention to the man behind the curtain, I am him. And unlike the Wizard of Oz, who’s all smoke and mirrors, these web applications really do work. The databases really are there. And the engineers really can peek inside them. (Even if they can’t actually read your one-way encrypted password, they don’t really need to. They can see the Matrix. And now I’m taking the movie metaphors a bridge too far. See?)

So then, we’re back to the real reason I decided to (mostly) stop using Facebook. Those friends, and the ways that my online interactions with them soured my experience of Facebook and, in a way, my life.

Cognitive dissonance

We all have our political views. Some of us hold to them more dearly than others. Some of us make our lives about our politics more than others. But in general, when we deal with each other face-to-face, we smooth over them. We focus on the things we have in common, the things that bring us together. If those things do not include our politics (and, in meatspace, they most often do not), then we check them at the door. But by the time Facebook came along, many of us were already used to a new social world online: a distilled, supersaturated, echo chamber of a world, where we only expose ourselves to the things we are most interested in, only interact with those who share our interests, and do it all under a cloak of anonymity that allows us to brazenly parade the crackpot ideas we wisely keep to ourselves in our “real-life” interactions. So what happens when we bring our “offline” friends into this online realm? It’s not pretty.

I found myself engaging in frustrating arguments over all manner of political, economic and philosophical topics with people I had never (or rarely) had cause to discuss them with before. And I realized why. It’s easy for someone to counter my revulsion over this experience with claims that I need to be open to differing opinions and all of that. Sure. I recognize that not everyone agrees with me. I know those opinions exist. But I don’t enjoy debating all of them ad nauseum like I’m in a freshman seminar course. And I don’t need to start thinking as negatively of my friends as I do of Glenn Beck. But since I still can’t let this kind of thing go without trying to get in the last word, I just want to leave this topic with a great (if not so inclusively worded) quote from the late Senator Daniel Patrick Moynihan: “Everyone is entitled to his own opinion, but not to his own fact.”

Three’s Company

I watched a lot of Three’s Company as a kid. Given the level of sexual innuendo in the show, perhaps a lot more than I should have been allowed to. But I turned out relatively OK. More than the lewd themes, however, the one thing that has stuck with me most about that show is how just about everything that goes wrong in life can be attributed to a lack of communication. Facebook is all about communication. And yet, there are so many ways in which it can fail us. Put aside any legitimate technical glitches like server outages. Facebook is programmed to filter out certain communications. There’s just so much information (if it can honestly be called that) people are dumping into Facebook’s databases every second of the day, and that could be shown on so many other people’s news feeds, that the site has to have a threshold for messages to hide, whether we as the users set it or the site’s programmers impose it arbitrarily. Bottom line: just because it’s there doesn’t mean you’re going to see it.

Plus, even though Facebook does show you when your friends are online at the same time as you are, it doesn’t tell you anything else about their viewing history on the site. To do so would be an unforgivable breach of the illusion of privacy all of Facebook’s users operate under. But the downside is that if you sent a friend a message and they never responded, you have no way of knowing if they read the message or not, or if they’ve even logged into Facebook since you sent it. And, depending on the situation and the nature of your message, this can lead to misunderstandings and hurt feelings. Listen, Larry. Just because you see Jack out on a date with a pair of twins by himself, doesn’t mean he didn’t stop by the Regal Beagle looking for you to join in on the fun. You just happened to be helping Mr. Roper fix the leaky faucet in Janet and Chrissy’s bathroom in a way that sounded like something completely different to Mrs. Roper when she came upstairs unexpectedly and heard the two of you behind that closed door.*

Where was I going with this?

Ceci n’est pas un ami

What is a friend? Everyone in our social networks on Facebook is a “friend,” but that’s an empty term. I have 137 “friends” in my Facebook network, which is apparently just below the median of approximately 150 that was much discussed in the blogoverse over the summer. It still sounds like a hell of a lot of friends to me! I never knew I was such a social butterfly.

As I suspect is the case with most average Facebook users (let’s exclude Roger Federer for the moment), they’re a mix of old friends from high school and college; former coworkers from the various jobs I’ve had since then; and current “offline” friends, neighbors and relatives — this last group being the only ones I really still interact with in my real life.

It’s been great catching up with old high school friends via Facebook. I suspect that Facebook has singlehandedly redefined the purpose of (or perhaps obviated entirely) that venerable institution: the class reunion. We’ll see when I have my 20th in a couple years. It’s clear though that right now I have ready access to the minutiae of the daily lives of lots of people I haven’t seen regularly since the era when Milli Vanilli was still considered a legitimate musical act. They’re people with whom, when we first reunited at the beginning of this decade after ten years apart, I had already completely lost touch. But now, thanks to Facebook, I feel like I have the keys to all of their diaries in my desk drawer.

Most of them are people I still like well enough, and of whom I have fond (if faded) memories from the “glory days” (not that they ever really were). But if I’m honest about it, I really don’t have much in common with any of them anymore (not that I ever really did), and there’s pretty much no way we’d even know each other today, much less be friends, if we didn’t share the common bond of having grown up in the ’80s in a small town on a dirty river that smelled like pork byproducts.

And yet, there are some among them with whom I do share a lot of common interests, and with whom I still could be good friends today. We even live in the same city. Come to think of it, we’ve even run into each other on the street in recent years, and have talked about “getting together sometime soon.” We have ready access to each other on a daily basis via Facebook, and yet, “sometime soon” never arrives.

There’s a saying (at least I think it’s a saying, which is to say I recently heard someone say it) that you never really explore your own city until you have out-of-town visitors. And I’m not entirely sure how that relates to the question of why I don’t get together with my former (and potentially future) very good friends with whom I share common interests, and who happen to live in the same city as I do, and with whom I can easily communicate on a daily basis via Facebook. But I think it’s probably a lot like that saying about getting to know your city. When something is always there, you don’t bother to take advantage of it because, well, it’s always there. Facebook makes it too easy to reconnect with old friends, at least in a superficial way. And that superficial way is almost more harmful than having no contact, because it makes it way too easy to settle for what it is, instead of investing the small amount of additional effort required to make a deeper connection.

So, after all of that, am I really off Facebook?

I’ve given plenty of good (if “good” is measured by verbosity) reasons why I’ve intended to quit Facebook as one of this year’s resolutions, but have I lived up to it?

The short answer, coming from someone not known for short answers (hence this long build-up): sort of. I have taken several concrete steps to significantly reduce my involvement with Facebook. I changed my notification preferences — I had previously set Facebook to never notify me by email about anything, since I was always checking the site anyway — so that if something really important does come my way, I can find out about it without constantly wading through Farmville updates** and alerts about which of my friends are now friends with other people I will never know. I never post status updates directly to Facebook, leaving it to Twitter (with which I also have an ambivalent relationship) and NetworkedBlogs to do my dirty work, although I do occasionally follow up on comments on my posts. Once every couple of days I’ll quickly scan the live feed to see if anyone’s saying or doing anything I’m genuinely interested in, but I limit it to a minute or two. And on special occasions (like when I took my dad to our first ever Vikings game on January 3), I’ll post photos to my profile. But that’s it, and it’s more than enough.

Is my life better with less Facebook in it? Unequivocally, yes. I do feel slightly less connected with my widely dispersed “network” of social acquaintances, but I also have a much more realistic view of how disconnected we really were anyway, despite the fact that I knew what they were eating for lunch.

Final thought

Knowing that this blog entry is going to appear in my Facebook feed, I feel the situation can best be described in the words of Sideshow Bob: “By the way, I am aware of the irony of appearing on television in order to decry it. So don’t bother pointing that out.”

* OK, I don’t think this scenario ever actually happened on Three’s Company. And yet, in a way, didn’t it happen on every single episode?

** Yes, I know you can hide that crap, and I always do with such a vengeance that at one point I almost considered unhiding all of them just so I could have the pleasure of hiding them all again.

Why oh why won’t CakePHP store my tinyint(1) data?

CakePHPOK, I actually know the answer to the question posed in the subject line. Despite the palpable suspense, I am sure 99.9% of my audience can tune out now. That leaves the remaining 0.001 readers to dive into this problem with me.

I’m doing a lot of CakePHP development these days, and I’m loving it. (What? The McDonald’s legal team is on its way over here? Damn. Fine, McDonalds. Have it your way. What, Burger King too? I guess I better make a run for the border. OK, joke’s over.)

Of course, plunging into a sea of someone else’s code is always fraught with a little peril, and today I found some. Here’s the scenario:

MySQL is a pretty cool database. I’m very loyal to it. But there are some things it should do, but it just doesn’t. One of those things is support a boolean data type. So, we make do. A common way to make do, and the way preferred by CakePHP, is to use a tinyint(1) field and just store 0 or 1 in it. In fact, CakePHP loves this approach so much that whenever it sees a data field that’s a tinyint(1), it “automagically” refuses to accept any values for that field other than 0 or 1.

That’s super-dee-duper. If that’s what you want. But I have a data table, my users table, as it happens, and in that table I used to have a field called admin, a boolean value. Either the user’s an admin or not. Great. But I decided to upgrade this to allow more access levels than just 0 or 1 (and while I was at it, changing the field name, shockingly, to access_level). I wanted to be able to support up to ten levels. Well, great! A tinyint(1) will nicely store values from 0 through 9, so I’m set!

Except… it didn’t work. Every time I tried to save a value greater than 1, I’d find that it had saved 1 as the value. I could change it to 0 just fine, but anything else became 1.

I checked the documentation and found ample evidence for the “automagic” behavior, so I figured there were a couple of possible changes I could make that would fix the problem: I could change it to a tinyint(2), or I could change it to a char(1). Since I decided I’d rather (in theory) allow letters in the field than double-digit numbers, I went with char(1).

Only it still didn’t work.

I did some more research, found that I seemed to be on the right track, but I was still confounded.

Then it occurred to me. I knew I had seen a cache directory somewhere in the labyrinth of directories and subdirectories and sub-sub-sub-sub-subdirectories in the CakePHP package. And I also knew that a framework as developed as CakePHP probably wouldn’t hit up the database for schema information constantly, so maybe… just maybe… it was cached.

I burrowed down in my application to the app/tmp/cache/models directory, and sure enough… there’s a cache file for each data table, with the schema in a serialized form. Well, it’s a cache, right? Nothin’ to lose. Trash can, here we come! I refreshed my page, and voilà! Success!

So… word to the wise (or, not so wise, like me… otherwise you’d probably already know):

  1. CakePHP will only store 0 or 1 in a tinyint(1) field. Period.
  2. If you change the schema for any of your data tables, and CakePHP acts like you didn’t… dump that cache!

We now return you to our regular, slightly less geeky programming.

Division by Zero is possible after all!

Division by Zero (Volume One)OK, it’s not. But my latest musical creation, the 3-song EP ÷0 [Division by Zero] (Volume One), now has an official release date of January 15, 2008, and I’ve submitted it via TuneCore for online distribution through iTunes and a few other services. It will probably begin to show up in their catalogs sometime in March; I’ll post more about availability as I learn about it. In the meantime, you can read more about the project (and listen to medium-quality streaming versions of all of the tracks) here.

Also, special thanks to my spitting-image son for acting as a stand-in for my 1978 self in the cover photo taken (last summer) in one of my favorite places from that time, Two Harbors, Minnesota.