[Resolved] Sort by 2 custom fields

[Author]

Posts

[robM-9]

The result i am after is like the screenshot attached.

I have created 2 views. Parent and child view.

The child view outputs the white rows. The parent view outputs the blue row (month) as well as the child view.

I have custom fields that are nested. I have a custom field for year (which is a select field) and I have a custom field for month (which is also select field). Then nested are the custom fields that contain the white row info.

The year and month are used for sorting and also displaying the month heading.

I can display everything fine. However i needed to do a sort on the 2 custom fields (year and month). When i added code to do the sort then it stopped displaying the white rows (child view). (just displayed no items found).

This is the sorting code i added.

{code}
/* Current Entries - Sort by custom field year and then by custom field month */
/* https://toolset.com/forums/topic/custom-order-by-with-2-custom-fields/#post-387775 */

add_filter('wpv_filter_query', 'add_custom_fields_to_view_query', 99, 3);
function add_custom_fields_to_view_query($query_args, $view_settings, $view_id ) {
if ( $view_id != 4497 ){
return;
}

$meta_query = array();
$meta_query[] = array('key' => 'wpcf-ce-year');
$meta_query[] = array('key' => 'wpcf-current-entries-month');
if ( !isset($query_args['meta_query']) ){
$query_args['meta_query'] = array();
}
$query_args['meta_query'] = array_merge($meta_query, $query_args['meta_query']);
add_filter('posts_orderby', 'custom_order');

return $query_args;
}
function custom_order($orderby) {
global $wpdb;
return $wpdb->postmeta.'.meta_value, mt1.meta_value, post_date ASC';
}
{/code}

Any ideas why the white rows are no longer displayed after adding that code?

Thanks.

[Christian Cox]

Hi, the first thing I noticed is here:

if ( $view_id != 4497 ){
return;
}

The wpv_filter_query function should always return an array of query arguments, even if it's not the view_id you want to filter. You should probably change it to:

if ( $view_id != 4497 ){
return $query_args;
}

Try that first, then let me know the results. We can go from there.

[robM-9]

Thanks Christian, i changed that code.

I can get it to work if i don't pass the view_id and just pass 2 parameters. i.e if i change

function add_custom_fields_to_view_query($query_args, $view_settings, $view_id )

to

function add_custom_fields_to_view_query($query_args, $view_settings)

But i guess if i do that then the filter will apply to all my views?

Is it an issue because i have a nested child view? Do i need to pass 2 view_ids (although i only need to sort on the parent view).

Thanks for any help. (i am just hacking around, and don't know what i am doing, lol)

[Christian Cox]

You definitely need to keep the $view_id param, or this filter will apply to all Views. You shouldn't need to pass 2 View IDs, only the parent ID. There's another example here you should look at:
https://toolset.com/forums/topic/sorting-custom-post-type-by-two-meta-fields/#post-395746

Nigel shows how to use an array in the orderby argument. It may be more straightforward than using the posts_orderby filter with custom SQL (which I am quite poor with unfortunately).

[robM-9]

Thanks Christian, I will try that. Currently i am adding the filter to functions.php. If i try doing it the other method, where does that code need adding?

[Christian Cox]

Both options require you to create a filter in functions.php. The second option just shows how to use an array in the orderby argument. This shows how you might combine the two:

add_filter('wpv_filter_query', 'add_custom_fields_to_view_query', 99, 3);
function add_custom_fields_to_view_query($query_args, $view_settings, $view_id ) {
if ( $view_id != 4497 ){
return $query_args;
}

$meta_query = array();
$meta_query[] = array('key' => 'wpcf-ce-year');
$meta_query[] = array('key' => 'wpcf-current-entries-month');
if ( !isset($query_args['meta_query']) ){
$query_args['meta_query'] = array();
}
$query_args['meta_query'] = array_merge($meta_query, $query_args['meta_query']);
$query_args['orderby']  = array( 
            'wpcf-ce-year' => 'DESC', 
            'wpcf-current-entries-month' => 'DESC', );


return $query_args;
}

[robM-9]

Thank you. I tried the new code, but it didn't seem to have any effect.

The first lot of code did the sorting fine, however still not displaying the child view. If i remove all the code then the child view displays (but isn't sorted - see screencaps).

Any other ideas?

It is going into the child view but it slips through straight into the no items found.

Thanks.

[Christian Cox]

I would need to log in and take a closer look. If your site is online, I can log in to your wp-admin area. If not, I would need a database dump file and a copy of your theme code so I can see what's going on. Let me know how you would like to proceed.

[Christian Cox]

Hi, I adjusted the code to the following:

add_filter('wpv_filter_query', 'add_custom_fields_to_view_query', 199, 3);
function add_custom_fields_to_view_query($query_args, $view_settings, $view_id ) {
  $view_ids = array( 4497 );
  if( in_array( $view_id, $view_ids)) {
    $meta_query = array();
    $meta_query[] = array('key' => 'wpcf-ce-year');
    $meta_query[] = array('key' => 'wpcf-current-entries-month');
    if ( !isset($query_args['meta_query']) ){
        $query_args['meta_query'] = array();
    }
    $query_args['meta_query'] = array_merge($meta_query, $query_args['meta_query']);
    $query_args['orderby']  = array(
      'wpcf-ce-year' => 'ASC',
      'wpcf-current-entries-month' => 'ASC',
    );
  }
  return $query_args;
}

Please be sure to pull down the update from the server to your local repository.

[robM-9]

Thank you, thats really appreciated. I can see it sorts by year, but it is not sorting by month. (you can see March 2018 is before Feb 2018).

I changed ASC to DESC and it didn't make any difference, suggesting that something not working quite right.

Any ideas? Thank you.

[Christian Cox]

I must have overlooked that somehow, my apologies. Here is the updated code:

add_filter('wpv_filter_query', 'add_custom_fields_to_view_query', 199, 3);
function add_custom_fields_to_view_query($query_args, $view_settings, $view_id ) {
  $view_ids = array( 4497 );
  if( in_array( $view_id, $view_ids)) {
    $query_args['meta_query'] = array(
        'relation' => 'AND',
        'year_clause' => array(
            'key' => 'wpcf-ce-year',
            'compare' => 'EXISTS',
            'type' => 'NUMERIC',
        ),
        'month_clause' => array(
            'key' => 'wpcf-current-entries-month',
            'compare' => 'EXISTS',
            'type' => 'NUMERIC',
        ),
    );
    $query_args['orderby'] = array(
        'year_clause' => 'ASC',
        'month_clause' => 'ASC',
    );
  }
  return $query_args;
}

I've added it to the site and it appears to be sorting correctly, can you confirm?

[robM-9]

Hi Christian, thanks for the updated code. However it doesn't appear to be sorting on either month or year now?

[Christian Cox]

Not sure what happened there, but I uploaded the correct functions.php file again. I'm attaching a screenshot showing what I see. Can you confirm, and download the latest version of the functions.php file to your local repository?

[robM-9]

Hi Christian, ah yes i can see its working now. Thank you so much for all your help, much appreciated! 🙂

[robM-9]

My issue is resolved now. Thank you!