[Resolved] I would like to sort the view by the count of relationship posts.

[Author]

Posts

[ericaH]

Can someone please advise how can I sort the view by number of items in certain relationship. For example, if I have workers in relationship to offices and I would like to sort offices with the highest number of workers. Thank you!!

[Christian Cox]

Hi, there's not an easy way to sort a View by a calculated value based on related posts, because WordPress sorting algorithms don't offer that kind of sorting: https://codex.wordpress.org/Class_Reference/WP_Query#Order_.26_Orderby_Parameters

The only way I can think of to accomplish this is to create a custom field on the Office post that stores a number. Then use custom code to set that number based on the number of Workers. The WordPress save_post API can be used to trigger that code whenever a Worker is added or removed.

Then you can sort the View of Offices by that custom field value. If this sounds like something that could work for your case, I might be able to help you set it up if you tell me more about the post types and post relationships:
- the post types and post type slugs
- the relationship type (one to many, or many to many)
- if this relationship was created in the new relationship system or migrated into the new relationship system

[ericaH]

Great. I appreciate your help. I thought about creating a custom field, but didn't know how to update it based on number of workers dynamically.

Post Types: Offices (offices) and Workers (workers)
Relationship: Many to Many
I believe it was a new relationship system (done probably about a month ago).
Relationship slug (if you need it) is office-worker.

Again, thank you very much!

[ericaH]

Forgot to add. The new custom field in Offices type is worker-count.

[Christian Cox]

Here is some custom code to add to your child theme's functions.php file:

// update the number of workers custom field in the office post after association
add_action( 'toolset_association_created', 'refresh_counts_after_association_created', 10, 5 );
function refresh_counts_after_association_created( $relationship_slug, $parent_id, $child_id, $intermediary_id, $association_id ) {
  $workers = toolset_get_related_posts($parent_id,
    'office-worker',
    'parent',
    1000000,0,
    array(),
    'post_id',
    'child');
  $count = sizeof($workers);
  update_post_meta($parent_id, 'wpcf-worker-count', $count);
}

// update the number of workers custom field in the office post before deleting association
add_action( 'toolset_before_association_delete', 'refresh_counts_before_association_delete', 10, 5 );
function refresh_counts_before_association_delete( $relationship_slug, $parent_id, $child_id, $intermediary_id, $association_id ) {
  $workers = toolset_get_related_posts($parent_id,
    'office-worker',
    'parent',
    1000000,0,
    array(),
    'post_id',
    'child');
  $count = sizeof($workers) - 1;
  update_post_meta($parent_id, 'wpcf-worker-count', $count);
}

[ericaH]

Thank you very much.
It seems not to be working just yet. I copied the code to functions.php but the number field is not being updated when I connect new worker or disconnect previously connected workers. any ideas? Are the hooks "toolset_before_association_delete" and "refresh_counts_after_association_created" correct? I can't seem to find reference for that.

[Christian Cox]

Yes, the toolset_association_created and toolset_before_association_delete hooks are new, and the documentation hasn't been updated to include them yet. They should be functional in the latest plugins, though.
1. Are your plugins up-to-date?
2. Edit the office-worker post relationship and check to confirm Office is the "parent" role, and Worker is the "child" role.
3. If you're adding or removing Workers in the Office post editor screen, you may have to refresh the page to see the updated count number appear after relationship modifications. Since the relationship functions are performed via AJAX, the office worker count numbers will not appear to be updated until you refresh the page.

If you're still having issues, I'll need to take a closer look. Please provide wp-admin access in the private reply fields here.

[Christian Cox]

I think what's happening is that the "Update" button on the main post saves whatever value is currently shown in the Count field, so if you add or remove relationships and then click "update" on the main post, the automatically set values are overridden by the old values. I had not considered this, so I think we should switch to a hidden field value that does not get overwritten by the GUI.

Here's some updated code:

// update the number of workers custom field in the office post after association
add_action( 'toolset_association_created', 'refresh_counts_after_association_created', 10, 5 );
function refresh_counts_after_association_created( $relationship_slug, $parent_id, $child_id, $intermediary_id, $association_id ) {
  $workers = toolset_get_related_posts($parent_id,
    'office-worker',
    'parent',
    1000000,0,
    array(),
    'post_id',
    'child');
  $count = sizeof($workers);
  update_post_meta($parent_id, '_worker-count', $count);
}

// update the number of workers custom field in the office post before deleting association
add_action( 'toolset_before_association_delete', 'refresh_counts_before_association_delete', 10, 5 );
function refresh_counts_before_association_delete( $relationship_slug, $parent_id, $child_id, $intermediary_id, $association_id ) {
  $workers = toolset_get_related_posts($parent_id,
    'office-worker',
    'parent',
    1000000,0,
    array(),
    'post_id',
    'child');
  $count = sizeof($workers) - 1;
  update_post_meta($parent_id, '_worker-count', $count);
}

add_action( 'save_post', 'autoupdate_office', 15, 3 );
function autoupdate_office( $post_id, $post, $update ) {
  if ( $post->post_status == 'publish' && $post->post_type =='offices' ) {
    $workers = array();
    $workers = toolset_get_related_posts($post_id,
      'office-worker',
      'parent',
      1000000,0,
      array(),
      'post_id',
      'child');
    $count = sizeof($workers);
    update_post_meta($post_id, '_worker-count', $count);
  }
}


add_filter( 'wpv_filter_query', 'order_by_hidden_count',199,3 );
function order_by_hidden_count( $query_args, $views_settings, $view_id) {
  $view_ids = array( 1694 );
  if (in_array($view_id, $view_ids)){
    $args = array(
      'meta_key' => '_worker-count',
      'orderby' => 'meta_value',
      'order' => 'DESC',
    );
    return array_merge($query_args, $args);
  }
  return $query_args;
}

There are updates to the relationship hooks, plus I added a save_post hook that will help fix an issue where Offices with no Workers do not appear in the View of Offices, and I added a wpv_filter_query hook to programmatically modify the sort order of that View of Workers. To prevent confusion, you should edit that View and change the sort order to Post Date.

You can delete the custom field we created before to hold the count.

[ericaH]

Thank you! I think it worked. Is there a way to output the value of that hidden field? I tried [types field='_worker-count' format='FIELD_VALUE'] but didn't work.
Second question, if I would do that same thing in the reverse order (i.e. to get office_count), how do I create a hidden field?

[ericaH]

Found the answer here https://toolset.com/documentation/user-guides/using-third-party-hidden-fields/
in case someone would be looking for the same thing.

[ericaH]

If I also wanted to sort the workers who worked at most offices, is there a way of updating _office_count at the same time when the worker is being added to the office?

[Christian Cox]

Sure, here's the updated code that will allow you to handle the reverse case.

// update the number of workers custom field in the office post after association
// and update the number of offices custom field in the worker post after association
add_action( 'toolset_association_created', 'refresh_counts_after_association_created', 10, 5 );
function refresh_counts_after_association_created( $relationship_slug, $parent_id, $child_id, $intermediary_id, $association_id ) {
  $workers = toolset_get_related_posts($parent_id,
    'office-worker',
    'parent',
    1000000,0,
    array(),
    'post_id',
    'child');
  $count = sizeof($workers);
  update_post_meta($parent_id, '_worker-count', $count);
  $offices = toolset_get_related_posts($child_id,
    'office-worker',
    'child',
    1000000,0,
    array(),
    'post_id',
    'parent');
  $count = sizeof($offices);
  update_post_meta($child_id, '_office-count', $count);
}
 
// update the number of workers custom field in the office post before deleting association
// and update the number of offices custom field in the worker post before deleting association
add_action( 'toolset_before_association_delete', 'refresh_counts_before_association_delete', 10, 5 );
function refresh_counts_before_association_delete( $relationship_slug, $parent_id, $child_id, $intermediary_id, $association_id ) {
  $workers = toolset_get_related_posts($parent_id,
    'office-worker',
    'parent',
    1000000,0,
    array(),
    'post_id',
    'child');
  $count = sizeof($workers) - 1;
  update_post_meta($parent_id, '_worker-count', $count);
 $offices = toolset_get_related_posts($child_id,
    'office-worker',
    'child',
    1000000,0,
    array(),
    'post_id',
    'parent');
  $count = sizeof($offices) - 1;
  update_post_meta($child_id, '_office-count', $count);
}

// automatically update the worker count when saving an office post
add_action( 'save_post', 'autoupdate_office', 15, 3 );
function autoupdate_office( $post_id, $post, $update ) {
  if ( $post->post_status == 'publish' && $post->post_type =='offices' ) {
    $workers = array();
    $workers = toolset_get_related_posts($post_id,
      'office-worker',
      'parent',
      1000000,0,
      array(),
      'post_id',
      'child');
    $count = sizeof($workers);
    update_post_meta($post_id, '_worker-count', $count);
  }
}

// automatically update the office count when saving a worker post
add_action( 'save_post', 'autoupdate_worker', 15, 3 );
function autoupdate_worker( $post_id, $post, $update ) {
  if ( $post->post_status == 'publish' && $post->post_type =='workers' ) {
    $offices = array();
    $offices = toolset_get_related_posts($post_id,
      'office-worker',
      'child',
      1000000,0,
      array(),
      'post_id',
      'parent');
    $count = sizeof($offices);
    update_post_meta($post_id, '_office-count', $count);
  }
}
 
 
add_filter( 'wpv_filter_query', 'order_by_hidden_worker_count',199,3 );
function order_by_hidden_worker_count( $query_args, $views_settings, $view_id) {
  $view_ids = array( 1694 );
  if (in_array($view_id, $view_ids)){
    $args = array(
      'meta_key' => '_worker-count',
      'orderby' => 'meta_value',
      'order' => 'DESC',
    );
    return array_merge($query_args, $args);
  }
  return $query_args;
}
 
 
add_filter( 'wpv_filter_query', 'order_by_hidden_office_count',199,3 );
function order_by_hidden_office_count( $query_args, $views_settings, $view_id) {
  $view_ids = array( 12345 );
  if (in_array($view_id, $view_ids)){
    $args = array(
      'meta_key' => '_office-count',
      'orderby' => 'meta_value',
      'order' => 'DESC',
    );
    return array_merge($query_args, $args);
  }
  return $query_args;
}

Be sure to change 12345 in the order_by_hidden_office_count function.

[ericaH]

Thank you! It works well especially if the site data is not in place. do you have any advice on how to update these count fields with existing data. In case of offices it is more or less manageable, I'd go through each office and add and remove some worker. But in case of _office_count I would have to go through each worker, and it would be pretty much impossible with 600 workers already being in the system. Does this make sense?

[Christian Cox]

Yes it makes sense. You can use the bulk edit feature in WordPress to trigger the automatic count processes. Go to Workers > All items, then check all rows and in the Bulk Actions menu choose "Edit". Without making changes, click "Update". You can also use the "Screen Options" tab at the top of the page to increase the number of posts on one screen. I wouldn't try more than 100 at once, or you might cause a memory failure.

[ericaH]

Brilliant! and you, sir are a rock star! Thank you for all your help!