Complicated MySQL Query

Question

I'm creating a site in wordpress which holds information on television programs. I'm using custom fields to select each post.

The table looks something like this

+----+---------+----------+------------+
| id | post_id | meta_key | meta_value |
+----+---------+----------+------------+
| 1  |    1    |   name   | Smallville |
| 2  |    1    |  season  |     1      |
| 3  |    1    |  episode |     1      |
| 4  |    2    |   name   | Smallville |
| 5  |    2    |  season  |     1      |
| 6  |    2    |  episode |     2      |
+----+---------+----------+------------+

Basically what I need to do is select all of the tv shows with the name "Smallville" and sort them by season then by episodes. I thought it would be fairly simple but everything I have tried returns nothing.

Could you please explain how I can do this?

Answer 1

A more general case: sort-of conversion from your format to a more normal relational DB format:

SELECT (SELECT meta_value FROM data t1 WHERE t1.post_id = t0.post_id AND meta_key = "season") AS season,
   (SELECT meta_value FROM data t1 WHERE t1.post_id = t0.post_id AND meta_key = "episode") AS episode
FROM data t0 WHERE meta_key = "name" AND meta_value = "Smallville"

For the actual sorting you can't reuse the season / episode values (those aren't assigned yet while sorting), so you have to copy/paste the subquery into the ORDER BY clause:

ORDER BY (SELECT ... "season") ASC, (SELECT ... "episode") ASC,

Answer 2

Combine the rows using a self-join, and you're good to go:

SELECT      *
FROM        yourtable name
INNER JOIN  yourtable season 
            on season.post_id = name.post_id
            and season.meta_key = 'season'
INNER JOIN  yourtable episode
            on episode.post_id = name.post_id
            and episode.meta_key = 'episode'
WHERE       name.meta_key = 'name'
            and name.meta_value = 'Smallville'
ORDER BY    season.meta_value, episode.meta_value

Answer 3

the idea is to join the table to itself 3 times where for each of them take rows for a given meta_key:

SELECT t1.meta_value name, t2.meta_value season, t3.meta_value episode
FROM table t1
JOIN table t2 on t1.post_id = t2.post_id and t2.meta_key = 'season'
JOIN table t3 on t1.post_id = t3.post_id and t3.meta_key = 'episode'
WHERE t1.meta_key = 'name'
  AND t1.meta_value = 'Smallville'
ORDER BY t2.meta_value, t3.meta_value

Answer 4

You can do something like this:

SELECT 
    t1.post_id, 
    t1.meta_value AS name, 
    t2.meta_value AS season, 
    t3.meta_value AS episode
FROM
    (
    SELECT *
    FROM the_table
    WHERE meta_key = 'name'
    ) t1
INNER JOIN
    (
    SELECT *
    FROM the_table
    WHERE meta_key = 'season'
    ) t2 ON t1.post_id = t2.post_id
INNER JOIN
    (
    SELECT *
    FROM the_table
    WHERE meta_key = 'episode'
    ) t3 ON t1.post_id = t3.post_id

This will give you the result:

| post_id | name       | season | episode |
-------------------------------------------
|    1    | Smallville | 1      | 1       |
|    2    | Smallville | 1      | 2       |

In this form it is much easier for any operations.

What you need is to add:

WHERE name = 'Smallville'
ORDER BY season, episode

Answer 5

No need to do direct SQL. You've got access to the SQL query through the WP_Query object. Check out the filters surrounding the where clause in the WP_Query object (there is more than 1 way to get at it) and simply modify the default WP_Query parts before they're concatenated together.

Start by setting up a WP_Query object that gets all the posts by postmeta key & postmeta value, and then tack on a bit more to the where clause to do some extra conditionals.

There's another filter that allows you to get at the ordering section of the SQL query so you can modify that.

There's no reason to hand write SQL here, just modify what has already been built for you.

Answer 6

Thanks so much for this!! WordPress has it's shortcomings and this is one of them. Problem solved!