I recently faced a really weird issue. I need to fetch data for event posts where the scenario is
- There are two meta keys
- I need to show all the events where these posts
expire_datevalue is greater than today's date and the events
start_dateshould be less than or equal today's date. Pretty straight forward right?
- But here is a catch. I also need to add a query where the start_date id greater than today's date. This can be a
ORcondition where I need to show the events in both of these cases.
So normally what I did was to create a
meta_query where the relation will be
OR at first then for the
start_date condition an array with
AND condition. In another array other condition for the greater
It looks like this:
'meta_query' => [ 'relation' => 'OR', [ [ 'key' => 'start_date', 'value' => date('Y-m-d'), 'compare' => '<=', 'type' => 'DATE' ], [ 'key' => 'expire_date', 'value' => date('Y-m-d'), 'compare' => '>=', 'type' => 'DATE' ], 'relation' => 'AND' ], [ 'key' => 'start_date', 'value' => date('Y-m-d'), 'compare' => '>', 'type' => 'DATE' ] ];
This actually giving the correct result but it made the query complex. It made several inner joins and the condition was not efficient. So it was taking a lot of time to show the event results.
These are the joins made for this meta query:
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id) INNER JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id) INNER JOIN wp_postmeta AS mt4 ON (wp_posts.ID = mt4.post_id)
Here is the where condition only for this meta_query:
wp_postmeta.meta_key = 'start_date' AND((mt1.meta_key = 'expire_date') AND(((mt2.meta_key = 'start_date' AND CAST(mt2.meta_value AS DATE) <= '2023-02-03') AND(mt3.meta_key = 'expire_date' AND mt3.meta_value >= '1675350000')) OR(mt4.meta_key = 'start_date' AND CAST(mt4.meta_value AS DATE) > '2023-02-03')))
It was taking almost 30 seconds to 1 minute to fetch all the event datas and showing.
So I removed all the meta queries and replaced with the
wpdb query to write custom queries.
From this above meta_query I replaced it. Queried to get the id's from two separate conditions for
expire_date then merging these two arrays. Removed extra joins and too much complex meta_queries.
$ids1 = $wpdb->get_col("`QUERY for the expire and start date`"); $ids2 = $wpdb->get_col("`Query for start_date post ids`");
$ids = array_merge($ids1, $ids2);
in the wp args
'post__in' => $ids
Above wpdb queries made my query clear and I also understood what I was doing. It's performance improved. And I am now feeling relax! 😅
See you again!
Top comments (0)