[Resolved] php script to retrieve intermediary post ID

[Author]

Posts

[fabriceS]

2 CPT's: 'movie' and 'cast-crew-member'. A many too many relationship 'movies-crew-member' with 1 checkbox custom field per function (director, screenwriter, composer). I need to create a view that will be added to the content template of the cpt 'movie'.
I need to list the intermediary posts where the checkbox 'director'=1. then retrieve the values of the custom fields 'first-name' and 'last-name' of the child post.

This is easy. to make with the Views blocks, but I need to create a php script and create a shortcode to add the sorting of the results based on the 'last-name'.

Can you help without accessing the admin as I'm working locally to develop ?

Many thanks.

[Minesh]

Hello. Thank you for contacting the Toolset support.

Well you can query the intermediary post using the post-relationship API function: toolset_get_related_posts
- https://toolset.com/documentation/customizing-sites-using-php/post-relationships-api/#toolset_get_related_posts

Please check the following related ticket that might help you:
- https://toolset.com/forums/topic/is-it-possible-toolset_get_related_posts-orderby-meta_value-in-intermediate-post/#post-1776895

[fabriceS]

unfortunately it is still not finding the results.
Here is my php:

<?php
/**
* New custom code snippet (replace this with snippet description).
*/

toolset_snippet_security_check() or die( 'Direct access is not allowed' );

// Put the code of your snippet below this comment.

function crew_list_directors_debug_shortcode() {
global $post, $wpdb;

if (!isset($post) || empty($post->ID)) {
return 'Film non disponible.';
}

$movie_id = $post->ID;
$relationship_slug = 'movies-crew-member';
$intermediate_post_type = $relationship_slug;
$child_post_type = 'cast-and-crew-member';

// Prepare the SQL query
$sql = $wpdb->prepare("
SELECT pm_child.meta_value AS child_id
FROM {$wpdb->posts} AS intermediate
INNER JOIN {$wpdb->postmeta} AS pm_parent
ON intermediate.ID = pm_parent.post_id AND pm_parent.meta_key = %s AND pm_parent.meta_value = %d
INNER JOIN {$wpdb->postmeta} AS pm_child
ON intermediate.ID = pm_child.post_id AND pm_child.meta_key = %s
INNER JOIN {$wpdb->postmeta} AS pm_director
ON intermediate.ID = pm_director.post_id AND pm_director.meta_key = %s AND pm_director.meta_value = '1'
WHERE intermediate.post_type = %s
AND intermediate.post_status = 'publish'
", "@{$relationship_slug}_parent", $movie_id, "@{$relationship_slug}_child", 'wpcf-crew-director', $intermediate_post_type);

$results = $wpdb->get_results($sql);

$output = "Debug info:<br>

$sql

<br>";

if ($results) {
$output .= '

';
foreach ($results as $result) {
$child_id = $result->child_id;
$child_type = get_post_type($child_id);
$child_title = get_the_title($child_id);
$output .= '
• ID: ' . esc_html($child_id) . ' | Type: ' . esc_html($child_type) . ' | Title: ' . esc_html($child_title) . '

';
}
$output .= '

';
} else {
$output .= '<br>Aucun directeur trouvé pour ce film.';
}

return $output;
}
add_shortcode('crew-list', 'crew_list_directors_debug_shortcode');

[Minesh]

But why you are using custom query.

You should try to use the:
- https://toolset.com/documentation/customizing-sites-using-php/post-relationships-api/#toolset_get_related_posts

For example:

$movie_id = $post->ID;
$relationship_slug = 'movies-crew-member';
$child_post_type = 'cast-and-crew-member';

$result = toolset_get_related_posts($movie_id, 
                                        $relationship_slug, 
                                        array(
        'query_by_role' => 'parent', // origin post role
        'role_to_return' => 'intermediary', // role of posts to return
        'return' => 'post_object', // return array of IDs (post_id) or post objects (post_object)
        'limit' => 999, // max number of results
         'args' => array(
            'meta_key'      => 'wpcf-crew-director',
            'meta_value'    => '1',
          ), // Additional query arguments
        'orderby' => 'meta_value',
        'orderby_role' => 'intermediary',
        'order' => 'ASC',
      ));

I that does not work, You will have to host your site on any server and send me admin access details and problem URL so I can review your current structure and guide you accordingly.

*** Please make a FULL BACKUP of your database and website.***
I would also eventually need to request temporary access (WP-Admin) to your site. Preferably to a test site where the problem has been replicated if possible in order to be of better help and check if some configurations might need to be changed.

I have set the next reply to private which means only you and I have access to it.