Multiple joins in SQL on a WordPress site with references through postmeta

2 Views

This one’s going to be a bit of a challenge to describe. I have a very unusual case I need to write a query for in WordPress.

I have a post type, called artists, and another one, called performances. Artists have a meta value with the key artist_id.
Performances have the same meta key, referencing the artist. I realize this alternate subsystem of IDs is ridiculous, but it’s the way things are for a bunch of other reasons, you’ll have to trust me that they’re sensible.

Read More

Performances have another meta key of show_set_time that contains a UNIX timestamp.

I need to write a query that can get me all artists that have a performance after a certain date.

Let me know if that’s unclear. I completely recognize, again, that the ID keys are ridiculous, but there are other factors involved.

Post Views: 2

Related posts

Leave a Reply

1 comment

  1. This is obviously untested, but I think it should work:

    SELECT *

FROM (SELECT NULL) AS dummy

INNER JOIN
wp_posts AS artist
ON artist.post_type = `artist`

INNER JOIN
wp_postmeta AS meta_artist_id
 ON meta_artist_id.post_id = artist.ID
AND meta_artist_id.meta_key = 'artist_id'


INNER JOIN
wp_posts AS performance
ON artist.post_type = `performance`

INNER JOIN
wp_postmeta AS meta_performance_artist_id
 ON meta_performance_artist_id.post_id = performance.ID
AND meta_performance_artist_id.meta_key = 'artist_id'
AND meta_performance_artist_id.meta_value = meta_artist_id.meta_value

INNER JOIN
wp_postmeta AS meta_performance_date
 ON meta_performance_date.post_id = performance.ID
AND meta_performance_date.meta_key = 'show_set_time'

WHERE
meta_performance_date.meta_value > DATE('2012-12-21')