[Resolved] How to apply custom code to order view by multiple custom fields

[Author]

Posts

[peterI]

Hello,

I have a staff list that includes custom fields:
on-contact-page
stafflevel
override-order
lastname

I want to use the content to create a view that filters for on-contact-page = yes and then order by stafflevel, override-order, lastname.

I can set a view to filter 'on-contact-page' = 'yes.' I also know I can't use multiple custom fields in a View order (not sure why...). Basically, I want to apply the following to the view:

'orderby' => array( 'wpcf-stafflevel' => 'ASC', 'wpcf-override-order' => 'ASC', 'wpcf-last-name' => 'ASC' ) ) );

I just don't know where to apply that.

Or, if I need a custom query that filters an orders, I don't know where to put that.

There are bits and pieces on https://toolset.com/forums/topic/sorting-custom-post-type-by-two-meta-fields/ - but I can't see where it helps me actually apply the sort order.

It seems to me that this would be a common enough request to have a page in documentation, but I can't find it.

Here is my development page (output using CPT and view at top, original page with desired order at bottom).

Help is appreciated.

- Peter

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

Hi Peter

It sounds like you are familiar with the arguments for WP_Query.

Views is effectively a UI-wrapper for WP_Query, and for ordering you can set a primary and secondary sort order within the UI, but that's it. Anything else and you will need to modify the query arguments yourself.

What you need is the wpv_filter_query API hook, available just before WP_Query is called, where you can modify the arguments, including the orderby argument.

If you add the following code to your functions.php (and edit the View ID), it will dump the $view_args array to your error log so you can inspect it, where it should be clear what you need to change to modify the sort order.

function tssupp_modify_orderby( $view_args, $view_settings, $view_id ){

	if ( 999 == $view_id ) { // Edit ID

		error_log(print_r($view_args, true));
	}

	return $view_args;
}
add_filter( 'wpv_filter_query', 'tssupp_modify_orderby', 101, 3);

[peterI]

Thanks, Nigel.

Thanks for the vote of confidence, but I'm not quite there yet. I think I have the correct pieces. I'm just not sure where to place. Specifically, I'm not sure exactly where to use the 'staffquery.'

So if the view ID is 522661, how far off am I with the following?

$args = array( 
  'post_type'       => 'staff',
  'posts_per_page'  => -1,
  'meta_query'      => array(
    'relation'  => 'AND',
 
    'stafflevel' => array(
      'key'     => 'wpcf-stafflevel',
      'compare' => 'EXISTS',
    ),
    'override-order'   => array(
      'key'     => 'wpcf-override-order',
      'type'    => 'NUMERIC',
      'compare' => 'EXISTS',
    ),
    'last-name'   => array(
      'key'     => 'wpcf-last-name',
      'compare' => 'EXISTS',
    ),
    'staff-page'   => array(
      'key'     => 'wpcf-staff-page',
	  'value'   => 'Yes'
      'compare' => '=',	
	)
  ),
  'orderby'     => array(
      'stafflevel' => 'ASC',
      'override-order' => 'ASC',
	  'last-name' => 'ASC')
  )
);

$staffquery = new WP_Query( $args );


function tssupp_modify_orderby( $view_args, $view_settings, $view_id ){
 
    if ( 52661 == $view_id ) { // Edit ID
 
        error_log(print_r($view_args, true));
    }
 
    return $view_args;
}
add_filter( 'wpv_filter_query', 'tssupp_modify_orderby', 101, 3);

And this (once correct) all goes into functions.php?

Also, do I have to add all the fields I will use in the view in the $args array? I assume that I can still use all the fields in the 'staff' CPT in my view, (e.g. fullname, jobtitle, featured image).

Thanks
- Peter

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

Hi Peter

You've done most of the hard work, correctly identifying that a specific format is required when adding multiple custom fields to the sort order (which is why it isn't currently supported in the Views GUI).

I updated your code:

function tssupp_modify_orderby( $view_args, $view_settings, $view_id ){
  
	if ( 52661 == $view_id ) { // Edit ID

		$view_args['meta_query'] = array(
			
			'relation'	=> 'AND',

			'stafflevel'		=> array(
				'key'     => 'wpcf-stafflevel',
				'compare' => 'EXISTS',
			),
			'override-order'	=> array(
				'key'     => 'wpcf-override-order',
				'compare' => 'EXISTS',
			),
			'last-name'			=> array(
				'key'     => 'wpcf-last-name',
				'compare' => 'EXISTS',
			),
			'staff-page'		=> array(
				'key'     => 'wpcf-staff-page',
				'value'   => 'Yes',
				'compare' => '=',
			)
		);

		$view_args['orderby'] = array(
				'stafflevel'		=> 'ASC',
				'override-order'	=> 'ASC',
				'last-name'			=> 'ASC'
		);
	}
  
	return $view_args;
}
add_filter( 'wpv_filter_query', 'tssupp_modify_orderby', 101, 3);

You are using wpv_filter_query to intercept the query for View id = 52661 and manually setting the meta_query and orderby arguments.

You have included all of the fields which will be used for sorting in the meta_query with the compare = EXISTS test, and you have also included the filter for wpcf-staff-page = Yes.

So that means your View should return the correct results in the required order.

This doesn't have any bearing on what you output in the Loop Output section.

[peterI]

Hi Nigel,

Excellent. It now works, after two tweaks

1. We had a missing comma the staff-page compare ( 'value' => 'Yes',)
2. I had to set a default value for override-order. Most staff members had a null value and therefore were not returned in the results.

So my final question:

Is there a way to set a default value in the query when a custom field has a null value, or otherwise include null values in the results (and also include them in the sort)?

- Peter

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

We are getting into the nuances of WP_Query here.

Some of those are not obviously logical (e.g. if you include a meta_query with some field != 'red' it will only return posts where the field != 'red' if the field has any value at all, even though no value != 'red').

But in this case we are adding a test for override-order EXISTS (purely to be able to use it in the orderby argument) and for posts where override-order does not exist then clearly the results should be excluded.

Unfortunately, you can't change the condition to something meaningless like override-order != 'bogus' because the posts with no value will be excluded.

You either have to run a one-time script to make sure that the posts all have something set for override-order (and make it required going forward), or you could play around with WP_Query to see if you can somehow get past the above limitation.

How about making the override-order condition not a single condition but an array of conditions combined with OR that check for both EXISTS or NOT EXISTS which, were it to work, wouldn't exclude any posts.

[peterI]

Thanks, Nigel.

Yes - my thought was to use EXISTS OR NOT EXISTS nested within the AND array. I just have to work through the syntax (which appears to be an array within the array). Then I have to see if the order output works as expected.

My short term solution was to assign a '999' value for each override-order for each post that was previously null (and add the default value to the custom field for new staff members).

Again, thanks for your help. It's greatly appreciated.

- Peter

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

If you try the EXISTS or NOT EXISTS and it works let me know as I'm curious to see, it might come in useful.