Mistake in documentation ? : Query posts by custom fields

• unikmedia

• October 14, 2015 at 8:16 pm

Hi,
(sorry for my english :})
On http://www.advancedcustomfields.com/resources/query-posts-custom-fields/

Section 4 contains information about searching sub fields.

Excerpt:

In this example, we will find all posts that have a post_type of ‘event’ where the current date is within a repeater field’s start and end date.

OK so for the example we are the 2015/10/14.
And we have an event with theses dates : (first date is start, last is end)
0 : 2015/10/10 – 2015/10/12
1 : 2015/10/20 – 2015/10/22

So today the event is closed but the code in documentation returns the event.

Indeed, because of the like, the request uses dates_0_start_date and dates_1_end_date meta_key! You see the problem ?

So i’m looking for the correct solution.

I could use a loop to repeat meta keys in array like this :

$args = array(
	'numberposts'	=> -1,
	'post_type'		=> 'event',
	'meta_query'	=> array(
	'relation'		=> 'OR'
	)
);
$max = 10;
for ($row=0; $row<$max; $row++) {
    $row_query = array(
        'relation' = 'AND',
        array(
            'key' => 'dates__'.$row.'_start_date',
            'value' => 'value to search for',
            'compare'	=> '<=',
        ),
        array(
            'key' => 'dates__'.$row.'_end_date',
            'value' => 'value to search for',
            'compare'	=> '>=',
        ),
    );
    $args['meta_query'][] = $row_query;
}

This code has not been tested, so it can contain some errors

However I would prefer a more elegant solution.

• John Huebner

• October 15, 2015 at 11:53 am

That is a solution to the problem I had not thought of, and it may be the only way to actually do this, at least using WP_Query. The problem will come in when you have more than 10 rows in a repeater.

The only error I see is that you have an extra underscore in you meta keys, they should be.

'key' => 'dates_'.$row.'_start_date',

Sometimes there is no elegant solution. My only concern for this would might be the length of time it takes the query to run, that might give you a problem.

• unikmedia

• October 15, 2015 at 5:56 pm

That’s what i thought :/

But you confirm there is a mistake in documentation ? How we can report the error ?

• John Huebner

• October 15, 2015 at 10:42 pm

There isn’t really a mistake in the documentation, I guess other than that it does not say that the query does not differentiate between fields in different rows. I’ll mark this thread for the developer to look at but not sure what can be done, or if anything will be done about it.

• unikmedia

• October 16, 2015 at 6:25 pm

If the query does not differentiate between fields in different rows, i don’t understand why there is a repeater field. One field stat_date and one field end_field are enough ^^’.

Finally, WP_Query is too slow : 17 seconds with 2 rows and timeout with 3 rows and i don’t know why, the request does not seem so complicated :

Array
(
    [post_type] => distributeur
    [posts_per_page] => 1
    [meta_query] => Array
        (
            [relation] => AND
            [0] => Array
                (
                    [key] => code pays
                    [value] => fr
                )

            [1] => Array
                (
                    [relation] => OR
                    [0] => Array
                        (
                            [relation] => AND
                            [0] => Array
                                (
                                    [key] => rc_cp_min_0
                                    [value] => 7000
                                    [type] => numeric
                                    [compare] => <=
                                )

                            [1] => Array
                                (
                                    [key] => rc_cp_max_0
                                    [value] => 7000
                                    [type] => numeric
                                    [compare] => >=
                                )

                        )

                    [1] => Array
                        (
                            [relation] => AND
                            [0] => Array
                                (
                                    [key] => rc_cp_min_1
                                    [value] => 7000
                                    [type] => numeric
                                    [compare] => <=
                                )

                            [1] => Array
                                (
                                    [key] => rc_cp_max_1
                                    [value] => 7000
                                    [type] => numeric
                                    [compare] => >=
                                )

                        )

                    [2] => Array
                        (
                            [relation] => AND
                            [0] => Array
                                (
                                    [key] => rc_cp_min_2
                                    [value] => 7000
                                    [type] => numeric
                                    [compare] => <=
                                )

                            [1] => Array
                                (
                                    [key] => rc_cp_max_2
                                    [value] => 7000
                                    [type] => numeric
                                    [compare] => >=
                                )

                        )

                )

        )

    [error] => 
    [m] => 
    [p] => 0
    [post_parent] => 
    [subpost] => 
    [subpost_id] => 
    [attachment] => 
    [attachment_id] => 0
    [name] => 
    [static] => 
    [pagename] => 
    [page_id] => 0
    [second] => 
    [minute] => 
    [hour] => 
    [day] => 0
    [monthnum] => 0
    [year] => 0
    [w] => 0
    [category_name] => 
    [tag] => 
    [cat] => 
    [tag_id] => 
    [author] => 
    [author_name] => 
    [feed] => 
    [tb] => 
    [paged] => 0
    [comments_popup] => 
    [meta_key] => 
    [meta_value] => 
    [preview] => 
    [s] => 
    [sentence] => 
    [fields] => 
    [menu_order] => 
    [...]
    [ignore_sticky_posts] => 
    [suppress_filters] => 
    [cache_results] => 1
    [update_post_term_cache] => 1
    [update_post_meta_cache] => 1
    [nopaging] => 
    [comments_per_page] => 50
    [no_found_rows] => 
    [order] => DESC
)

SQL :

SELECT SQL_CALC_FOUND_ROWS
    wp_posts.ID
FROM
    wp_posts
        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)
        INNER JOIN
    wp_postmeta AS mt5 ON (wp_posts.ID = mt5.post_id)
        INNER JOIN
    wp_postmeta AS mt6 ON (wp_posts.ID = mt6.post_id)
WHERE
    1 = 1
        AND ((wp_postmeta.meta_key = 'code pays'
        AND CAST(wp_postmeta.meta_value AS CHAR) = 'fr')
        AND (((mt1.meta_key = 'rc_cp_min_0'
        AND CAST(mt1.meta_value AS SIGNED) <= '7000')
        AND (mt2.meta_key = 'rc_cp_max_0'
        AND CAST(mt2.meta_value AS SIGNED) >= '7000'))
        OR ((mt3.meta_key = 'rc_cp_min_1'
        AND CAST(mt3.meta_value AS SIGNED) <= '7000')
        AND (mt4.meta_key = 'rc_cp_max_1'
        AND CAST(mt4.meta_value AS SIGNED) >= '7000'))
        OR ((mt5.meta_key = 'rc_cp_min_2'
        AND CAST(mt5.meta_value AS SIGNED) <= '7000')
        AND (mt6.meta_key = 'rc_cp_max_2'
        AND CAST(mt6.meta_value AS SIGNED) >= '7000'))))
        AND wp_posts.post_type = 'distributeur'
        AND (wp_posts.post_status = 'publish'
        OR wp_posts.post_status = 'acf-disabled'
        OR wp_posts.post_status = 'future'
        OR wp_posts.post_status = 'draft'
        OR wp_posts.post_status = 'pending')
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0 , 1

Yes it’s a little more complicated than ACF example and they are postal codes and not dates but principle is the same.

• John Huebner

• October 17, 2015 at 12:24 am

This is the reason it times out

FROM
    wp_posts
        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)
        INNER JOIN
    wp_postmeta AS mt5 ON (wp_posts.ID = mt5.post_id)
        INNER JOIN
    wp_postmeta AS mt6 ON (wp_posts.ID = mt6.post_id)

This is why I said

My only concern for this would might be the length of time it takes the query to run, that might give you a problem.

There is a known issue in WP with multiple joins on the meta table.

I’m not sure that there is a solution, at least I have not seen one so far, at least not a direct solution.

• unikmedia

• October 17, 2015 at 12:40 am

Finally i used wpdb to execute this request:

SELECT
    wp_posts.ID
FROM
    wp_posts
    INNER JOIN    wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
    LEFT JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id AND mt1.meta_key = 'rc_cp_min_0') 
    LEFT JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id AND mt2.meta_key = 'rc_cp_max_0') 
    LEFT JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id AND mt3.meta_key = 'rc_cp_min_1') 
    LEFT JOIN wp_postmeta AS mt4 ON (wp_posts.ID = mt4.post_id AND mt4.meta_key = 'rc_cp_max_1') 
    LEFT JOIN wp_postmeta AS mt5 ON (wp_posts.ID = mt5.post_id AND mt5.meta_key = 'rc_cp_min_2') 
    LEFT JOIN wp_postmeta AS mt6 ON (wp_posts.ID = mt6.post_id AND mt6.meta_key = 'rc_cp_max_2')
WHERE
    1 = 1
        AND (wp_postmeta.meta_key = 'code pays'
        AND wp_postmeta.meta_value = 'fr')
        AND ((mt1.meta_value <= 7000 AND mt2.meta_value  >= 7000) 
          OR (mt3.meta_value <= 7000 AND mt4.meta_value  >= 7000) 
          OR (mt5.meta_value <= 7000 AND mt6.meta_value  >= 7000))
        AND wp_posts.post_type = 'distributeur'
        AND wp_posts.post_status = 'publish'
LIMIT 0 , 1

Duration: 0.031 sec

It’s exactly the same code except that meta_key conditions are on join and not on where and i replaced INNER JOIN by LEFT JOIN.

My knowledge of Mysql optimization is limited so i don’t know exactly why it’s really faster but it works 🙂

• John Huebner

• October 17, 2015 at 12:58 am

This is great information. I know just enough MySQL to be dangerous so this will help. Now if we could only package this up into a filter that alters the query…