Hello everyone,
I’m stuck with this problem and I will really appreciate any kind of help.
For woocommerce products I have created a custom field “date_end” and I like to order the products based on that value. The custom field type is Date Picker with Display Format: d/F/Y and Save Format: dd/MM/yy.
I’m using this function to order the products:
add_filter('woocommerce_get_catalog_ordering_args', 'woocommerce_catalog_orderby');
function woocommerce_catalog_orderby( $args ) {
$args['order'] = 'ASC';
$args['meta_key'] = 'date_end';
$args['orderby'] = 'meta_value';
return $args;
}
But this is not ordering correctly. For example, I have products with dates: 3 September 2021, 22 February 2022, 01 April 2022.
Instead of that correct ordering, I have: 01 April 2022, 03 September 2021, 22 February 2022
So I guess that the ordering is made only based on the day ( 1,3,22) and the month and the year are not considered. I really don’t know what’s causing this issue. Thanks.
I don’t see anything wrong with your code. Sorting is done by value stored in the DB. The value for a date field in the DB is always “YYYYMMDD” and is not changed by the display format.
Is date_end a top level field or is it a sub field of a group field or some other type of field.
The date_end is one of the fields in the group.
I think that the problem is the Save Format: dd/MM/yy, because if I create a new Custom field and I set Return Format: Ymd, then the code is working and the products are ordered correctly. I can’t change the save format of my custom field because I have many functions that are using it and If I change it, the site will break.
How can I convert this in the query, so that this works?
Also, this field is old (before update of acf plugin), and now I see that If I create a new field Date Picker, the options are different ( instead of Save Format, there is Return Format etc).
I’m really confused.
Thanks.
The date_end is one of the fields in the group.
I think that the problem is the Save Format: dd/MM/yy, because if I create a new Custom field and I set Return Format: Ymd, then the code is working and the products are ordered correctly. I can’t change the save format of my custom field because I have many functions that are using it and If I change it, the site will break.
How can I convert this in the query, so that this works?
Also, this field is old (before update of acf plugin), and now I see that If I create a new field Date Picker, the options are different ( instead of Save Format, there is Return Format etc). Right now I’m using version 5.11.4.
I’m really confused.
Thanks.
I can’t edit my reply so I have to write a new one. I’ve checked into database, and I saw that the custom field date_end is saved in the following format:
02/April/2022
So I’ve tried to manipulate and change the code:
add_filter('woocommerce_get_catalog_ordering_args', 'woocommerce_catalog_orderby');
function woocommerce_catalog_orderby( $args ) {
// <- define category slug- returns true or false
$args['order'] = 'ASC'; // <- order ASC or DESC
$args['meta_key'] = 'date_end'; // <- _price is meta_value_num's key - required'
$args['orderby'] = date('Ymd', strtotime(preg_replace("/\//", " ", 'meta_value')));
return $args;
}
But again, I don’t have correct ordering.
The only reason that I can come up with for your original code not working is that the date field is some type of sub field. You have done it according to the documentation I can find.
You do not understand the relationship between the display format and the save format, unless you are using an older version of ACF the value is always saved “YMD” and this format does not need to be reflected in the query.
The only reason it would is if you are using a very old version of ACF or the values in these fields were saved using a very old version of ACF and have not been updated since they were originally created. Even here that would not make sense because if the site was kept up to date all along then when ACF changed the way is saved values your fields should have been updated during the upgrade process. This upgrade may not have been done but only if you updated from a very old version of ACF to a very new version of ACF.
My suggestion is to look in the DB and see exactly what is stored for this date field.
The date field is not a sub field, its only grouped with some other fields. I have checked into DB and the date_end field is stored like I mentioned in this format: 02/April/2022
I’m sending you an image of the wp_postmeta table to see how it is stored:
That’s why I’ve used this line of code, to remove the backslash and transform the date into Ymd format, but it still isn’t working:
$args['orderby'] = date('Ymd', strtotime(preg_replace("/\//", " ", 'meta_value')));
To mention that the latest upgrade was from 4.4.2 to 5.11.4.
I really ran out of ideas how to solve this issue.
Thanks for the help.
Since they are stored that way then it is not sortable by year. Date fields are treated like text fields. They are being sorted in alphanumeric order. To be able to sort dates they must must be in some format that orders the text in year/month/day order.
I get it. How would you suggest to fix this? Should I create a new Date Picker field that will store the date in Ymd format? That will cause a lot of work since all the products already used that field (a lot of products), and that field with that specific format is used in many functions, so will need to re-program a lot of code. Isn’t there any way to manually transform the date in other format, and then sort them ?
Thank you again for the help.
I would I suggest you fix it? I haven’t got an answer. WP_Query cannot be used to sort these values in any meaningful way. I suspect that this is one of the reasons the save format setting was removed from later versions of ACF.
You can still use the return format in ACF, as long as you are using get_field() to get these values in your functions then those functions should not be affected by what is stored in the database.
I’ve started to type suggestions several times and deleted them. I can’t even come up with a valid plan for replacing all the fields in the DB. In the end I would likely create a new field, manually update every product and then re-code the site to use the new field. This would be a big job.
