[Resolved] "Order by" is not working when using custom field

[Author]

Posts

[joseL-6]

I am trying to: implement a view where the items (WooCommece Events) are ordered by their event date, in ascending order

I visited this URL: hidden link

I expected to see: The events in order from the earliest to the farthest out

Instead, I got: The sort order is reversed, BUT it is reversed no matter WHAT I choose in the "Ordering:" paramter when setting up the view.

I am using the WooCommerce ad the WooCommerce Events Manager plugin, which has a field "event_datetime" which is what I am trying to sort on.

[Nigel Supporter Languages: English (English ) Spanish (Español ) Timezone: Europe/London (GMT+01:00)]

Hi Jose

I don't have the events plugin to test, I may need to get it from you, but first, can you confirm, you are using the standard UI for setting up the view and the event_datetime field appears in the dropdown for you to select (as per my screenshot, which is missing that particular field).

The issue may be because of how the field is stored in the database.

Are you familiar with inspecting the database (using phpMyAdmin, for example)?

Can you look in the wp_postmeta table for one of your posts that has this custom field and check how it is stored? If it is not a simple value (e.g. a number or string) and is a serialized array then it won't be possible to use it for ordering.

I'll wait to hear back from you before suggesting a next step.

[joseL-6]

Thanks for the reply. Yes, I was able to examine the database and the field is stored as 'event_datetime' in the wp_postmeta table. I examined post id 924, "An Evening With Spirit" which is here: hidden link

The 'event_datetime' field is stored in the database as "2017-05-12 18:00".

NOTE: This was originally how the field was displaying in my views and product pages. I had to write a custom function and shortcode in order to display it as "May 12, 2017 6:00pm".

HOWEVER, the sorting problem was and is happening both before AND after I added the shortcode to my views, so I don't think that is the issue. I have tried sorting the field as a string, number and native field and it seems to have no effect on my sort.

FORGOT TO UPDATE: yes, I am using the standard Views UI and the field "event_datetime" does indeed show up in the dropdown.

[Nigel Supporter Languages: English (English ) Spanish (Español ) Timezone: Europe/London (GMT+01:00)]

Hi Jose

I've done some testing using a string field stored in the same format you describe and didn't experience any problems using that field for the sorting.

I'd like to test it with the actual plugin. Can you zip a copy of the plugin and upload it somewhere you can share it with me, such as dropbox.

I'll mark your next reply as private so that you can share the URL. (You can ignore the template which asks for site credentials.)

[Nigel Supporter Languages: English (English ) Spanish (Español ) Timezone: Europe/London (GMT+01:00)]

Hi Jose

Thanks for that.

I did some testing locally with the Woo Events Manager plugin, and I may have identified the problem and a possible workaround, see what you think.

Looking at the query arguments, they appear to be correctly formed, with the post meta field 'event_datetime' the argument for orderby, and order changing to ASC or DESC according to my settings.

But what's returned by the query doesn't correspond with the query argument for order.

I think the Woo plugin is interfering, and on first page load there are a series of PHP warnings from that plugin ("Undefined index"). I could see if I can escalate this to our compatibility team to see if they can identify the issue.

However, I found that the problem seems to be bypassed if you add front-end order controls (new in Views 2.3) to the view.

In the Filter Editor there is a button to insert sorting controls on the front end (so that your users can modify the sort order). See the screenshot for how that looks once inserted.

I found that when the sort controls are added to the page the sorting works (both on initial page load and when using the controls).

Is that a viable solution for you?

[joseL-6]

This might work but I have a few questions.
1) If I understand correctly I need to REPLACE this view with a new one that uses custom search, so that I can add these filters, correct?
2) I am already filtering the view, because the event products are a sub-category of Products. How do I set that particular filter as "static" and not on the front end?

If I am able to somehow "prefilter" the products to show only events, then I think we'd be OK with using front-end filtering to sort by date.

[Nigel Supporter Languages: English (English ) Spanish (Español ) Timezone: Europe/London (GMT+01:00)]

Hi Jose

If I understand correctly, looking at the link to your events page, it is a static page that you have designed with Beaver Builder and inserted a View that lists your events, yes?

It doesn't matter whether you have added any query filters to this view, you don't have to create a different view, you can edit this one and add the sort controls to it.

If you cannot see the Filter Editor then go to the top of the page and open the "Screen Options" tab and mark the checkbox to expose the Filter Editor.

Then go back down to the Filter Editor, place your cursor between the filter control shortcodes and use the Sorting Controls button to insert the required shortcodes.

The query filters and the sorting controls are separate and adding the sorting controls won't affect your filters in any way.

[joseL-6]

Ah got it! Almost there...

If you check my page you can see that the sorting does work, BUT, by default, on page load the events are still showing in descending order (the later ones first). The client really wants the sort to go the other way around. How do I set that default?

Here is my filter shortcode:

[wpv-filter-start hide="false"]
[wpv-filter-controls]
[wpv-sort-orderby type="select" options="field-event_datetime" label_for_field-event_datetime="Field - event_datetime" orderby_ascending_for="field-event_datetime"][wpv-sort-order type="select" options="desc,asc" label_for_asc="Ascending" label_for_desc="Descending" label_asc_for_field-event_datetime="Ascending" label_desc_for_field-event_datetime="Descending"]
[/wpv-filter-controls]
[wpv-filter-end]

[Nigel Supporter Languages: English (English ) Spanish (Español ) Timezone: Europe/London (GMT+01:00)]

Hi Jose

Triple-checking, yes, you are right, although the front-end order controls work the initial value is ignored when the page first loads.

It's definitely an issue related to the Woo Events Manager plugin. Now that the data is created (and the event_datetime field exists in the wp_postmeta table) you can de-activate the plugin and then you will find that the ordering works when the page initially loads.

So, I need to escalate this to our compatibility team to investigate further and see if they can narrow down the problem (and whether we can fix it from this end or need to liaise with the plugin author).

I will keep you posted of any updates.

[joseL-6]

Thank you! Although I think the issue is still in need of resolution, I am going to mark it as resolved because it is something that now goes beyond my particular case, and in theory affects anyone who uses this plugin combo, so I'm assuming it will continue to be worked on without me 🙂

For now, although it is not best practice, I have modified the Events plugin and made notes as to what I did... hopefully an update will fix it down the road.

Thanks for helping me come up with a solution I can live with for now!