3

Im running a travelling-site with journeys which have a start and ending date. Now these journeys can be several times a year.

Now I have a single-post with three repeated start and ending dates

01.03.2015 - 14.03.2015       2015-03-01  - 2015-03-14
01.04.2015 - 14.04.2015       2015-04-01  - 2015-04-14
01.05.2015 - 14.05.2015       2014-05-01  - 2015-05-14

I want to show my post with its start and ending dates contained in the search date range.

Searching 01.06.2015 - 01.07.2015 should return no results. Searching 01.03.2015 - 01.04.2015 should return one result.

$reisen_query_args = array(
'post_type' => 'reisen',
'post_status' => 'publish',
'meta_query' => array(
    array(
        'key'       => 'reisezeiten_%_start',
        'compare'   => '>=',
        'value'     => $startdate,
        'type'      => 'DATE'
    ),
     array(
        'key'       => 'reisezeiten_%_end',
        'compare'   => '<=',
        'value'     => $enddate,
        'type'      => 'DATE'
    )
)

This works half-way and I cant figure it out why:

if the search range is 01.03.2015 - 10.03.2015 (2015-03-01 - 2015-03-10) the post doesn't show up which is correct

BUT

if the range is 05.03.2015 - 16.03.2015 (2015-03-05 - 2015-03-16) the post shows up which is WRONG because the search starting date must be 01.03.2015 (2015-03.01) or before to return results.

AND

this only happens when Im having repeated fields. As long theres only one date stored 01.03.2015 - 14.03.2015 (2015-03-01 - 2015-03-14) the query behaves as desired.

Could someone pls help me with this? I'm searching for weeks now to get this done.

This is how my query looks like:

SELECT SQL_CALC_FOUND_ROWS wp410_posts.ID
FROM wp410_posts
INNER JOIN wp410_term_relationships
ON (wp410_posts.ID = wp410_term_relationships.object_id)
INNER JOIN wp410_postmeta
ON ( wp410_posts.ID = wp410_postmeta.post_id )
INNER JOIN wp410_postmeta AS mt1
ON ( wp410_posts.ID = mt1.post_id )
WHERE 1=1
AND ( wp410_term_relationships.term_taxonomy_id IN (29) )
AND wp410_posts.post_type = 'reisen'
AND ((wp410_posts.post_status = 'publish'))
AND ( ( wp410_postmeta.meta_key LIKE 'reisezeiten_%_start'
AND CAST(wp410_postmeta.meta_value AS DATE) >= '2015-03-02' )
AND ( mt1.meta_key LIKE 'reisezeiten_%_end'
AND CAST(mt1.meta_value AS DATE) <= '2015-03-14' ) )
GROUP BY wp410_posts.ID
ORDER BY wp410_posts.menu_order ASC
LIMIT 0, 6
hes
  • 121
  • 2
  • 14
  • Can you clarify? Does a *single* post have three `wp_postmeta` rows with the `reisezeiten_%_start` key and another three with the `...end` key? Or is it three *different* posts each with their own `wp_postmeta` rows? – O. Jones Feb 24 '15 at 13:16
  • yes its a single-post with six wp_postmeta. three start and three end keys – hes Feb 24 '15 at 13:25
  • Another clarification, please! It seems like you want to find *reisen* with start and end dates entirely contained in the searched date range. So, searching for 03-06-2015 - 03-07-2015 should return no results. And searching for 02-20-2015 - 03-20-2015 should return one result. Correct? Please edit your question to clarify. – O. Jones Feb 24 '15 at 13:28
  • You may wish to install the WP `query-monitor` plugin from https://wordpress.org/plugins/query-monitor/ and use it to examine the generated SQL. That's what I would do if I were trying to debug this myself. – O. Jones Feb 24 '15 at 13:36
  • By the way, welcome to Stack Overflow. Thanks for posting a genuine (and difficult!) question. – O. Jones Feb 24 '15 at 13:36
  • Yes. I installed query monitor. Besides that im no sql-expert I couldnt see any difference on any search query I tried. – hes Feb 24 '15 at 13:46
  • I wonder if you would edit your question to include the SQL query text; this `wp_postmeta` business is a gigantic *Haarballen*. – O. Jones Feb 24 '15 at 13:54
  • Couple of ideas, might be way off, the meta_query outer array doesn't have a relationship, I can't remember if the default is AND or OR, it could be OR'ing the 2 instead of AND'ing? Second: Is it possible due to your wildcard in the acf subfield key (_row_) that it is matching the startDate from one row and the endDate from another row? – Matt.C Feb 24 '15 at 16:54
  • Ok, tested same setup scenario, it's the multiple rows, in your failed test, it's matching the start date from the 3rd row. and the end date from the first row. Proof: change your last failed test range to 05.05 - 16.05 and it should return no posts, solution wise, not sure yet, but I'm pretty certain with my test that's what is causing it. Possibly match start dates, loop results and validate end date or something similar? – Matt.C Feb 24 '15 at 17:53
  • Matt you´re right. Changing the range from 02.05 - 15.05 returns no post. Could it be the missing relationship in my outer array? Sry, I´m no programmer at all.. – hes Feb 25 '15 at 15:17
  • I'm not sure the relationship is the issue, I think the wildcard in the meta_key is the problem, I don't think you'll do it with one query, you might need multiple querys, or multiple items in the meta_query array depends on whether there is always three rows, or many more – Matt.C Feb 25 '15 at 16:59

1 Answers1

0

The problem is that your request doesn't know that reisezeiten_1_start corresponds to reisezeiten_1_end. So it can uses reisezeiten_2_start and reisezeiten_1_start for your example.

Your range : >=2015-03-05, <=2015-03-16

Your data :

2015-03-01  - 2015-03-14
2015-04-01  - 2015-04-14
2014-05-01  - 2015-05-14

Your request means "I want post which have one start date greater than 2015-03-05 and one ending date less than 2015-03-16"

So :

2015-03-01  - 2015-03-14 => ending date correponds to your range
2015-04-01  - 2015-04-14 => start date corresponds to your range
2014-05-01  - 2015-05-14

So you comparison is valid on the two meta_key and the post is returned. This explains why you code works with one date.

Like said Matt. C, one solution would be to use multiple items in meta_query array (and like he said: it won't work if you have unlimited rows)

$reisen_query_args = array(
    'post_type' => 'reisen',
    'post_status' => 'publish',
    'meta_query' => array(
        'relation' => 'OR',
        array(
            'relation' => 'AND',
            array(
                'key'       => 'reisezeiten_1_start',
                'compare'   => '>=',
                'value'     => $startdate,
                'type'      => 'DATE'
            ),
             array(
                'key'       => 'reisezeiten_1_end',
                'compare'   => '<=',
                'value'     => $enddate,
                'type'      => 'DATE'
            )
        ),
        array(
            'relation' => 'AND',
            array(
                'key'       => 'reisezeiten_2_start',
                'compare'   => '>=',
                'value'     => $startdate,
                'type'      => 'DATE'
            ),
             array(
                'key'       => 'reisezeiten_2_end',
                'compare'   => '<=',
                'value'     => $enddate,
                'type'      => 'DATE'
            )
        ),
        array(
            'relation' => 'AND',
            array(
                'key'       => 'reisezeiten_3_start',
                'compare'   => '>=',
                'value'     => $startdate,
                'type'      => 'DATE'
            ),
             array(
                'key'       => 'reisezeiten_3_end',
                'compare'   => '<=',
                'value'     => $enddate,
                'type'      => 'DATE'
            )
        )

    )
)

Be careful, this code works only on WP 4.1+

See codex for more informations (end of paragraphe)

Some help on ACF website

(Sorry for my english)

A. Joahny
  • 301
  • 2
  • 14
  • Hey. Its been a long time. The project was on hold till now. Seems like this is the only working solution for this issue. Right now Im trying to get your solution in an proper array so I dont have to write every single block for each number. I found this topic but I cant get it work for my issue: https://stackoverflow.com/a/14446234/4504590. I dont know how to deal with the ''relation' => 'OR',' or even how to repeat the loop for 10 times. Could somebody please assist me with this or should I open a new question for this? – hes Jun 10 '18 at 19:14
  • Hi @hes, could you show your code ? (with pastbin or any other service) – A. Joahny Jun 13 '18 at 16:54
  • Hey Mr. Joahny. I already solved this quesiton here: https://stackoverflow.com/questions/50788251/while-loop-in-wp-query-array. But then an other problem showed up which I discribed here: https://wordpress.stackexchange.com/questions/305856/wp-query-loads-forever-when-meta-key-is-missing. Maybe you could assist me there? – hes Jun 13 '18 at 21:48
  • ok, i have not the reputation to comment on your other question (but you can accept my answer to increase it ;)) so have you tried to execute sql of the WP_Query directly on the database to see the problem ? (with phpmyadmin or other software). To get the sql : `$query->request` Moreover for 10 repeating dates, i think there may be some performance issues – A. Joahny Jun 14 '18 at 09:03