Complicated MySQL Query For Wordpress

Question

Sometimes I think I haven't got the brain power for programming, I just can't get my head round this.

I've got a table called wp_postmeta which looks kind of like this

+-----------+-----------+------------+--------------+
|  meta_id  |  post_id  |  meta_key  |  meta_value  |
+-----------+-----------+------------+--------------+
|    1      |     1     |   type     |   movie      |
+-----------+-----------+------------+--------------+
|    2      |     1     |   name     |  dark knight |
+-----------+-----------+------------+--------------+
|    3      |     2     |    type    |   tv show    |
+-----------+-----------+------------+--------------+
|    4      |     2     |    name    |   lost       |
+-----------+-----------+------------+--------------+
|    5      |     3     |    type    |   tv show    |
+-----------+-----------+------------+--------------+
|    6      |     3     |   name     |  house       |
+-----------+-----------+------------+--------------+
|    7      |     4     |   type     |   movie      |
+-----------+-----------+------------+--------------+
|    8      |     4     |   name     |  godfather   |
+-----------+-----------+------------+--------------+

What I want to do is select the all the movies. I need to find WHERE meta_key = 'type' AND meta_value = 'movie' then get the meta_key "name" where the post_id's match and select the meta_value thus giving me "dark knight" and "godfather".

Sorry for the poor explanation but WordPress doesn't have a logical structure to this part of the database, making it quite hard to explain.

Ayman Hourieh · Accepted Answer · 2009-11-01 13:02:42Z

3

select pm2.meta_value as movie_name
  from wp_postmeta as pm1 join wp_postmeta as pm2
    on pm1.post_id = pm2.post_id
  where pm1.meta_key = 'type' and pm1.meta_value = 'movie' and
    pm2.meta_key = 'name'

answered Nov 1, 2009 at 13:02

Ayman Hourieh

138k23 gold badges149 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user83632 · Accepted Answer · 2009-11-01 13:05:36Z

0

SELECT b.meta_value as movie
FROM wp_postmeta a
LEFT JOIN wp_postmeta b ON (a.post_id = b.post_id)
WHERE a.meta_key = 'type'
  AND a.meta_value = 'movie'
  AND b.meta_key = 'name';

answered Nov 1, 2009 at 13:05

user83632

2 Comments

Paul Tomblin Over a year ago

Why 'LEFT JOIN' instead of 'JOIN'? Is there a difference?

user83632 Over a year ago

I use LEFT JOIN in my own code so that I can check the data returned form the query is what I'd expect, and also useful for finding orphaned data i.e where the post is a movie, but the name key hasn't been set or has been deleted. In this case a either JOIN or LEFT JOIN is appropriate.

Paul Tomblin · Accepted Answer · 2009-11-01 13:07:01Z

0

Here's a slight variant of Amyam's where the join is implicit. I learnt it this way a long time ago, so it's probably the old fashioned way:

select pm2.meta_value as movie_name
from wp_postmeta pm1, wp_postmeta pm2
where pm1.post_id = pm2.post_id AND
      pm1.meta_key = 'type' AND pm1.meta_value = 'movie' AND
      pm2.meta_key = 'name';

answered Nov 1, 2009 at 13:07

Paul Tomblin

184k59 gold badges324 silver badges412 bronze badges

Comments

James Goodwin · Accepted Answer · 2009-11-01 13:23:12Z

0

SELECT meta_value FROM wp_postmeta WHERE meta_key = 'name' AND post_id IN
    (SELECT post_id FROM wp_postmeta
    WHERE meta_key  = 'type' AND meta_value = 'movie');

edited Nov 1, 2009 at 13:23

answered Nov 1, 2009 at 13:04

James Goodwin

7,4065 gold badges32 silver badges41 bronze badges

Collectives™ on Stack Overflow

Complicated MySQL Query For Wordpress

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related