meta query - How to compare Event period with Week period using get_posts(), meta_query, relation and compare

admin管理员组

文章数量:1133970

I have many posts, each post is an event with a period . This period can be 3 hours, 3 days, 15 days, 3 month. I defined the start time of event with start_timestampevents and the end with end_timestampevents. These data come from postmeta. I have four buttons : "first week", week +1, week +2 and year list. For the moment I'm focusing on the "current week"

Until now I thought my meta_query do what I wanted but it's not. Because in my meta_query ('meta_query' => array( array())) I compared the start_timestampevents ('key'=>) with the period of first day until the last day of current week ('value'=>). I did the same comparison with end_timestampevents.

Assuming that 'value' => array($firstdayweek_ts,$lastdaymidnight_ts) get the date (timestamp) of each day between 18 to 25 September, I'm not comparing things in the right way.

I keep the week from 18 to 25 September 2023 and event from 12 to 25 September for below example *.

My goal is to compare each timestamp/date of the event period (12,13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25) with these of week range (18, 19, 20, 21, 22, 23, 24).

Until now, my events have only been displayed for the range of days in this week* if the start and end dates of this event are within it. Start and end dates outside (before or after) this weekly period are ignored, but the event always takes place during this week.

According to wp documentation, WP_Query or get_posts() doesn't allow to use array inside 'key'=> parameter .

I need to compare all the days (timestamps) of the event with the range of days in the current week*, so that at least one timestamp of the event must correspond to one of the days in the week after using strtotime().

array(7) { [0]=> string(10) "18-09-2023" [1]=> string(10) "19-09-2023" [2]=> string(10) "20-09-2023" [3]=> string(10) "21-09-2023" [4]=> string(10) "22-09-2023" [5]=> string(10) "23-09-2023" [6]=> string(10) "24-09-2023" }

the same array with timestamp : array(7) { [0]=> string(10) "1694988000" [1]=> string(10) "1695074400" [2]=> string(10) "1695160800" [3]=> string(10) "1695247200" [4]=> string(10) "1695333600" [5]=> string(10) "1695420000" [6]=> string(10) "1695506400" }

Must I repeat array('key' for each date of event period range ? If yes can I do a loop from array of dates event ?

My query:

$ActivitiesType = array(
    'post_type' => $activityType,
    'post_status' => 'publish',
    'posts_per_page'   => -1,
    //'meta_key' =>'start_timestampevents',
    'orderby' => 'meta_value_num',
    'order' => 'ASC',

    'meta_query' => array(
        'relation' => 'OR',
            array(
                'key' => 'end_timestampevents',
                'value' => array($firstdayweek_ts,$lastdaymidnight_ts),
                'compare' => 'BETWEEN',
                'type' => 'NUMERIC'
            ),
            array(
                'key' => 'start_timestampevents',
                'value' => array($firstdayweek_ts,$lastdaymidnight_ts),
                'compare' => 'BETWEEN',
                'type' => 'NUMERIC'
            )
    )       
);

I have many posts, each post is an event with a period . This period can be 3 hours, 3 days, 15 days, 3 month. I defined the start time of event with start_timestampevents and the end with end_timestampevents. These data come from postmeta. I have four buttons : "first week", week +1, week +2 and year list. For the moment I'm focusing on the "current week"

Until now I thought my meta_query do what I wanted but it's not. Because in my meta_query ('meta_query' => array( array())) I compared the start_timestampevents ('key'=>) with the period of first day until the last day of current week ('value'=>). I did the same comparison with end_timestampevents.

Assuming that 'value' => array($firstdayweek_ts,$lastdaymidnight_ts) get the date (timestamp) of each day between 18 to 25 September, I'm not comparing things in the right way.

I keep the week from 18 to 25 September 2023 and event from 12 to 25 September for below example *.

My goal is to compare each timestamp/date of the event period (12,13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25) with these of week range (18, 19, 20, 21, 22, 23, 24).

Until now, my events have only been displayed for the range of days in this week* if the start and end dates of this event are within it. Start and end dates outside (before or after) this weekly period are ignored, but the event always takes place during this week.

According to wp documentation, WP_Query or get_posts() doesn't allow to use array inside 'key'=> parameter .

I need to compare all the days (timestamps) of the event with the range of days in the current week*, so that at least one timestamp of the event must correspond to one of the days in the week after using strtotime().

array(7) { [0]=> string(10) "18-09-2023" [1]=> string(10) "19-09-2023" [2]=> string(10) "20-09-2023" [3]=> string(10) "21-09-2023" [4]=> string(10) "22-09-2023" [5]=> string(10) "23-09-2023" [6]=> string(10) "24-09-2023" }

the same array with timestamp : array(7) { [0]=> string(10) "1694988000" [1]=> string(10) "1695074400" [2]=> string(10) "1695160800" [3]=> string(10) "1695247200" [4]=> string(10) "1695333600" [5]=> string(10) "1695420000" [6]=> string(10) "1695506400" }

Must I repeat array('key' for each date of event period range ? If yes can I do a loop from array of dates event ?

My query:

$ActivitiesType = array(
    'post_type' => $activityType,
    'post_status' => 'publish',
    'posts_per_page'   => -1,
    //'meta_key' =>'start_timestampevents',
    'orderby' => 'meta_value_num',
    'order' => 'ASC',

    'meta_query' => array(
        'relation' => 'OR',
            array(
                'key' => 'end_timestampevents',
                'value' => array($firstdayweek_ts,$lastdaymidnight_ts),
                'compare' => 'BETWEEN',
                'type' => 'NUMERIC'
            ),
            array(
                'key' => 'start_timestampevents',
                'value' => array($firstdayweek_ts,$lastdaymidnight_ts),
                'compare' => 'BETWEEN',
                'type' => 'NUMERIC'
            )
    )       
);

• meta-query
• get-posts
• events
• timestamp
• comparison

Share Improve this question edited Sep 23, 2023 at 10:01 imagIne asked Sep 22, 2023 at 16:38 imagIneimagIne 1051111 bronze badges 4

• You need to change how you store the the timestamps for your event. You're not going to be able to do this if they are in an array. If your events only occur once, you'd be better off if you just store the start time and end time of the event. If your events occur multiple times this gets much more difficult. – Jacob Peattie Commented Sep 22, 2023 at 17:50
• I'm a little confused, your question has a lot of dense information but it never directly states what the goal is, the closest you get is "My goal is to compare the range of an event with a period running from 12 to 25 September." which implies/indirectly states the goal in a way that leaves it open to interpretation. Are you saying you want to find all events that start/end between the 12th and 25th of December? You mention a comparison but never shared what kind of comparison you meant. – Tom J Nowell ♦ Commented Sep 22, 2023 at 17:51
• Also this will never work with that format of data storage, you need singular strings in MySQL friendly formats, e.g. 2022-01-31 12:00:00 UTC, and they cannot be structured data. No arrays, no objects, no JSON, no XML, etc, MySQL cannot search inside an array for values, they just get stored as a single serialized string. You can attempt to do it by searching for substrings but you'll get lots of false positives, and you can't do comparisons like BETWEEN or perform logic. Note that meta keys are not unique, you can have multiple independent meta values with the same key – Tom J Nowell ♦ Commented Sep 22, 2023 at 17:54
• You also can't sort/order by this meta because it's a serialised array, there's no way for MySQL to parse and sort through the numbers for sorting. This also opens you up to a class of exploits known as object deserialisation attacks – Tom J Nowell ♦ Commented Sep 22, 2023 at 17:56

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

To be able to do this your post meta values need to be strings in a valid date format, e.g. 2022/01/31 for January 31st 2022. Note I used ISO standard timestamp format with the biggest value first, then everything descending, and avoided using a regional value such as 31/1/2022 from the UK, or 1/31/2023 from the USA.

Unfortunately though, none of your values fit this format, making your task impossible until the data is stored in the correct way. Your data is not a timestamp string, it's a string of a serialised PHP array that just happens to contain timestamps. MySQL has no idea there is an array in the meta value column, it sees a string, not a PHP data structure. This is why you can't sort/order/filter by it or use clauses that depend on logic such as BETWEEN >= etc. The most you can do is search the string or use a regular expression, both of which can't be used for this task.

Fixing The Data

Instead you need to do several things to fix this:

• stop using structure data! It's very likely you're passing an array to update_post_meta/add_post_meta, or using a metabox/fields library that does this behind the scenes. That needs to stop immediatley
• store the individual timestamps as individual separate meta. Post meta keys are not unique, instead of storing a single start_timestampevents that holds lots of data, store multiple start_timestampevents.
• This is why we have add_post_meta, why delete_post_meta takes a value, and why get_post_meta has a 3rd parameter ( it's usually set to true to return only one result, but if you set it to false it will return an array of results instead! )

You will also need to convert all your old data to the new system. A custom WP CLI commands will help here, however it may be easier to change the name of your meta key, then fetch 20 posts that have the old key and convert them on the admin_init hook followed by deleting them so it doesn't process the same posts again. Once that loop stops finding posts it will have finished migrating and you can remove that code, with the benefit being that by running on admin_init and only doing 50 at a time you're not killing your site or ruining visitors page performance.

Once the data is fixed, you can set your meta query data type to date and time as specified in WP_Query's dev docs, and all the examples from that page should work and apply to your use case. The problems you mentioned about trying to specify keys should vanish.

本文标签：