DEV Community

Ahmad Jamaly Rabib
Ahmad Jamaly Rabib

Posted on

Wordpress: Getting out of meta queries

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'
            ]
        ];
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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')))
Enter fullscreen mode Exit fullscreen mode

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`");
Enter fullscreen mode Exit fullscreen mode

$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! 🫶

Latest comments (0)