[Resolved] toolset_get_related_posts: sorting and querying by meta_key not working

[Author]

Posts

[Valeriia]

Hi,

I'm trying to get the last created child post with this code:

$logs = toolset_get_related_posts($partner, 'partner-log', 'parent', 1, 0, array(), 'post_object', 'child', 'date', 'DESC');

But it returns the first post, not the last one.

While troubleshooting, I tried this:

$logs = toolset_get_related_posts($partner, 'partner-log', 'parent', 10, 0, array(), 'post_object', 'child', 'date', 'DESC');

and then changed DESC to ASC. The order of the returned posts did not change.

Besides, I tried this:

$logs = toolset_get_related_posts($partner, 'partner-log', 'parent', 10, 0, array('meta_key' => 'wpcf-log-result', 'meta_value' => $result), 'post_object', 'child', 'date', 'DESC');

And it returned an error:
WordPress database error: [Unknown column 'wp_postmeta_1' in 'where clause']
SELECT DISTINCT associations.child_id AS child_id FROM wp_toolset_associations AS associations JOIN wp_toolset_relationships AS relationships ON ( associations.relationship_id = relationships.id ) JOIN wp_posts AS wp_posts_1 ON (wp_posts_1.ID = parent_id) JOIN wp_posts AS wp_posts_2 ON (wp_posts_2.ID = child_id) LEFT JOIN wp_postmeta AS wp_postmeta_1 ON (wp_postmeta_1.post_id = child_id AND wp_postmeta_1.meta_key = 'wpcf-log-result') WHERE ( associations.relationship_id = 1 ) AND ( parent_id = 842 ) AND ( wp_postmeta_1 = 'No reply' ) AND ( ( ( wp_posts_1.post_status IN ( 'publish', 'draft', 'pending', 'private' ) ) AND ( wp_posts_2.post_status IN ( 'publish', 'draft', 'pending', 'private' ) ) ) ) AND ( relationships.is_active = 1 ) LIMIT 10 OFFSET 0

Please advise.

Btw, it would be nice to have more comprehensive examples in the documentation. The only one that's there related to toolset_get_related_posts does not even include sorting options.

Thanks.

[Christian Cox]

Hi, sorry for the inconvenience here. We just published an erratum post about this recently:
https://toolset.com/errata/toolset-api-toolset_get_related_posts-meta_key-query-is-producing-a-wordpress-database-error/

Please install the patch and let me know if the issue is not fully resolved. I'll add a note for our Documentation team requesting some additional examples for this method. Thanks for the report.

[Valeriia]

Hi Christian,

Thank you, unfortunately it only solved the issue with the last of my examples. Sorting by date DESC/ASC still returns the same results. I'm unable to get the last published child.

[Christian Cox]

As it turns out, I checked the documentation and 'date' is not an accepted value for orderby yet:

$orderby – null|string Determine how the results will be ordered. Accepted values: null, 'title', 'meta_value', 'meta_value_num'. If the latter two are used, there also needs to be a 'meta_key' argument in $args. Passing null means no ordering.

If you use a null value and set the maximum number of results to be something high like 1000000, you can use an array sorting function to compare the post dates of each post object. Something like this:

$logs = toolset_get_related_posts(
  $partner, 
  'partner-log', 
  'parent', 
  1000000, 0, 
  array(), 
  'post_object', 
  'child',
);
usort($logs, "log_date_sort_cmp");

function log_date_sort_cmp($a, $b)
{
  $log_a_date = get_the_date('U', $a->ID);
  $log_b_date = get_the_date('U', $b->ID);
  $res = 0;
  if ( $log_a_date < $log_b_date) {
     $res = 1;
  }
  return $res;
}

Then you can rely on the sort order of the array of results.

[Valeriia]

Oh, I see... I was so sure that you would do such a basic thing so didn't even read the description properly.

I guess I have no choice for now, although I really would not want to retrieve all the posts for the sake of getting only one of them.

Are you planning to add the sorting by date or at least by ID? I need that option quite often.

Maybe I could use a MySQL query instead? Could you suggest it?

[Christian Cox]

I don't have any MySQL query templates available for post relationship queries, but check this document for more information about using the standard WP_Query function with Toolset relationships:
https://toolset.com/documentation/customizing-sites-using-php/post-relationships-api/how-to-migrate-your-site-to-new-post-relationships/
Be sure to pay close attention to the "Remarks" section at the bottom. There is important information here about when this query feature is possible in the WordPress request lifecycle.

[Valeriia]

Ok, thank you.