Wordpress: Getting out of meta queries
Tags: webdev, wordpress, php, programming
Published: 2023-02-03 19:19:35

I recently faced a really weird issue. I need to fetch data for event posts where the scenario is

- There are two meta keys `start_date` and `expire_date`.
- I need to show all the events where these posts `expire_date` value is greater than today's date and the events `start_date` should 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 `OR` condition 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 `expire_date` and `start_date` condition an array with `AND` condition. In another array other condition for the greater `start_date`.
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 `start_date` and `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! 🫶