[Resolved] View sorting by custom field is not working

[Author]

Posts

[Nigel]

Hi there

I've looked through the above and looked at your site.

The problem is that you are ordering by a field which most posts do not have a value for, and WordPress queries will exclude posts from the results if the field used for ordering is empty.

You can see this in a simple View I created using default post date ordering called Nigel which I added to this page: hidden link

Only 3 products have a value set for the New field (one with 1, two with 0).

So on your page where the View orders results by the New field, you only see 3 results (in the correct order).

So your View is working correctly.

Although you created a custom field New with zero as the default value, you created this after your product posts. New posts will have zero as the default, but existing posts are unaffected until they are saved again.

Rather than save them all, I could help you with a PHP snippet to set the default value where it doesn't exist if you like.

Note, as an aside, I noticed that the field group "Product additional fields" seems to be missing from the Fields and Views button fields. I suspect that could be because you created an ACF field group with the identical name, though I'm not sure. You could update the field group name to see if it fixes that glitch.

[lenas]

Hi Nigel,
Thanks for reviewing. That's correct, i undrestand that the products that will be pulled are only those that have the field data populated. I do see 5 products with 0 or 1 even in your view returned (towards the bottom there are a few), but the issue is that if we set the Order to by "New" field, the products are not ordering. I have added the Order setting to your view and here are the results: hidden link . As you can see Table Tops & Bases, which has a 1, should be at the top, and it is at the bottom.

[Nigel]

There is only one post with a 1 value, and it is at the top, as expected. (See screenshot.)

[lenas]

Did you chose United States for the country on the top right, as mentioned above with a screenshot?
This is what i get returned for your (and our current) view, image attached.

[Nigel]

OK, I see that when switching to the US.

I activated the debug info and reloaded the page and I can see that Views is specifying sort order based upon the new-product custom field, but the SQL generated to query the database is first ordering by menu order, so something is interfering in the query created by Views.

I'll need to do some testing to see if I can identify what.

Can I do that on this site? Or is it a live site?

[lenas]

You can certainly use this site. Thank you!!!!

[lenas]

I just realized that there is a Post Type Plugin that is probably interfering. I thought it's deactivated for products, but perhaps there is an issue.

[Nigel]

Yes, disabling the Post Types Order plugin fixes the issue.

I'm not familiar with the plugin to know whether you can limit where it operates...

[lenas]

Nigel, thank you so much!
I was able to disable the Post Order plugin for products. So, it seems to me that what the other rep (Beda) was saying is incorrect -- that in order to be able to sort, the custom field cannot be a checkbox field (as our existing New Product field). It seems to me that as long as "save 0 to the database" is checked for the custom field and a script is run to update all products to have 0 for the meta value when New Product field is not checked, sort on this custom field should work fine. Is that correct?

If you have the script handy for updating all products, i would appreciate it.

[Nigel]

Yes, you can use a checkbox field that saves 0 to the database when unchecked. (It looks like Beda had understood you were using checkboxes fields, which cannot be used, and which the erratum he linked to refers to.)

You can use the following code to set a zero on the custom field for existing products that have no value set.

$field = 'new-product'; // Edit slug of field

$products = get_posts( array(
    'post_type'     => 'product',
    'post_status'   => 'publish',
    'numberposts'   => -1
) );

foreach ($products as $product) {

    $value = get_post_meta( $product->ID, 'wpcf-'.$field, true);

    if ( empty( $value ) ) {

        update_post_meta( $product->ID, 'wpcf-'.$field, '0' );
    }
}

This code needs to be run one time only.

You can do that by adding it to Toolset > Settings > Custom Code, saving it, then choosing Run now.

You just need to edit the slug of the field you want to update.

[lenas]

My issue is resolved now. Thank you so much!