Hi there,
I have website with about 600+ events (custom post types) and each event can have genres (there are about 18, these are also custom post types).
I link genres to events with the relationship field, works great.
When I’m on an event page, which has for instance the genres jazz, blues and rock, I would like to query all the events that also have jazz OR blues OR rock (that’s why I’m using OR and LIKE in the query).
Using echo $query->request I’m able to see the query (see below). If I put this in PHPMyAdmin, the query takes very long to complete (have to restart the server because it doesn’t finish at all actually).
Someone suggested to me that there should be an extra table in the db which holds the relationships to make them easily queryable. Is that so? If not; is there a way to make my query more efficient? (please note: if I change the OR statement to an AND statement the query works).
The SQL query:
SELECT SQL_CALC_FOUND_ROWS yt_posts.ID
FROM yt_posts
INNER JOIN yt_postmeta ON (yt_posts.ID = yt_postmeta.post_id)
INNER JOIN yt_postmeta AS mt1 ON (yt_posts.ID = mt1.post_id)
INNER JOIN yt_postmeta AS mt2 ON (yt_posts.ID = mt2.post_id)
WHERE 1=1
AND yt_posts.post_type = 'events'
AND (yt_posts.post_status = 'publish')
AND (
(yt_postmeta.meta_key = 'genres' AND CAST(yt_postmeta.meta_value AS CHAR) LIKE '%\"1241\"%')
OR (mt1.meta_key = 'genres' AND CAST(mt1.meta_value AS CHAR) LIKE '%\"1242\"%')
OR (mt2.meta_key = 'genres' AND CAST(mt2.meta_value AS CHAR) LIKE '%\"1240\"%')
)
GROUP BY yt_posts.ID ORDER BY yt_posts.post_date ASC LIMIT 0, 4
And my wp_query:
$query_genres = array();
$query_genres['relationship'] = 'OR';
$genres = get_field('genres');
foreach($genres as $genre) {
array_push($query_genres,
array(
'key' => 'genres',
'value' => '"' . $genre->ID . '"',
'compare' => 'LIKE'
)
);
}
$args = array(
'post_type' => 'events',
'meta_query' => $query_genres,
'posts_per_page' => 4,
'post_status' => 'publish',
'orderby' => 'date_from',
'order' => 'ASC'
);
