How WordPress Taxonomy Query URLs Could Be More Awesomer

(Updated, see below) WordPress 3.1 introduced some awesome new taxonomy query features, and the URL parsing allows some rudimentary syntax to query multiple terms and choose if the query is OR’d or AND’d. The URL syntax is as follows: A comma (,) between terms will return posts containing either term (logical OR), like this,mysql/ . A […] » about 300 words

Bits Of MySQL Query Syntax I’ve Learned This Week

Watching the WordPress hacker list this week, a couple messages related to selecting information about users schooled me on MySQL syntax. I obviously knew the following would work, but I’d previously used the UNION syntax in similar situations and somehow hadn’t thought of writing it this way:

``` SELECT (SELECT meta_value FROM wp_usermeta WHERE meta_key = 'first_name' AND user_id = 2) AS FIRST, (SELECT meta_value FROM wp_usermeta WHERE meta_key = 'last_name' AND user_id = 2) AS LAST, wp_users.* FROM wp_users WHERE wp_users.ID = 2 ```

That’s much cleaner to my thinking, though I’ve no idea which is more optimal. When somebody replied asking for a solution that would work in pre-MySQL 5, this was the response:

``` SELECT ID, user_login, FIRST.meta_value AS fname, LAST.meta_value AS lname FROM wp_users LEFT JOIN wp_usermeta AS FIRST ON (wp_users.ID = FIRST.user_id AND FIRST.meta_key = 'first_name') LEFT JOIN wp_usermeta AS LAST ON (wp_users.ID = LAST.user_id AND LAST.meta_key = 'last_name') ```

And the lesson to me here is that I didn’t realize the syntax allowed us to match multiple conditions for the JOIN. Makes sense, but I just hadn’t thought of it. Thanks go to Phil Williams and Otto for tipping me to these.