[Resolved] How to build a WP query string to pull posts based on relationship

[Author]

Posts

[chrisC-25]

Hi,

I am trying to build a query that I can use in Themeco Cornerstone builder. It should pull Albums (CPT) that are related to an Artist CPT. The CPT and relationship is built in Toolset. In another site using ACF and CPT UI I built a query like this:
post_type=releases&meta_key=artist&orderby=DATE&order=DESC&meta_query%5B0%5D%5Bkey%5D=artist&meta_query%5B0%5D%5Bvalue%5D{{dc:post:id}}&meta_query%5B0%5D%5Bcompare%5D=%3D%3D&meta_query%5B0%5D%5Btype%5D=NUMERIC

The CPT for the albums is:
concord-albums
The CPT for the Artist is:
artist

The Toolset relationship slug is:
artist_concord-albums

Any help is appreciated.

Thanks

[Minesh]

Hello. Thank you for contacting the Toolset support.

There are two ways you can fetch the related posts:
Either you can use the post relationship API function: toolset_get_related_posts()
- https://toolset.com/documentation/customizing-sites-using-php/post-relationships-api/#toolset_get_related_posts
Or
You can go for native WP_Query() way:
- https://toolset.com/documentation/customizing-sites-using-php/post-relationships-api/how-to-migrate-your-site-to-new-post-relationships/#new-approach

More info:
- https://toolset.com/documentation/customizing-sites-using-php/displaying-child-posts/#displaying-child-post-contents
- https://toolset.com/documentation/customizing-sites-using-php/displaying-parent-posts/

[chrisC-25]

Thanks, how do I build this into a string?
The CPT for the albums is:
concord-albums
The CPT for the Artist is:
artist

The Toolset relationship slug is:
artist_concord-albums

I am trying this:

$query = new WP_Query( 
    array(
        'post_type' => 'artist',
        'posts_per_page' => -1,
        'toolset_relationships' => array(
            array(
                'role' => 'child',
                'related_to' => get_the_ID(),
                'relationship' => array( 'concord-albums' )
            ),
            array(
                'role' => 'parent',
                'related_to' => $selected_post,
                'relationship' => 'artist_concord-albums'
            )
        ),
        'meta_key' => 'concord-albums',
        'orderby' => 'meta_value',
        'order' => 'DESC',
    )
);
$posts = $query->posts;

But I am getting an error when trying to create the string via http_build_query() function.

I also am not sure what the 'meta_key' should be? I am trying the CPT slug for the child.

Thanks

[Minesh]

If you do not want meta_key argument, you can remove it.

Here is the article that might help you:
- hidden link

What if you try to use the following code:

$query = new WP_Query( 
    array(
        'post_type' => 'artist',
        'posts_per_page' => -1,
        'toolset_relationships' => array(
            array(
                'role' => 'child',
                'related_to' => get_the_ID(),
                'relationship' => array( 'concord-albums' )
            ),
            array(
                'role' => 'parent',
                'related_to' => $selected_post,
                'relationship' => 'artist_concord-albums'
            )
        ),
        'order' => 'DESC',
    )
);
$posts = $query->posts;

echo $query->request;   // this should print the SQL query

[chrisC-25]

I am still having issues creating the string. I have tried this online tool:
hidden link

and when I enter

array(
        'post_type' => 'artist',
        'posts_per_page' => -1,
        'toolset_relationships' => array(
            array(
                'role' => 'child',
                'related_to' => '{{dc:post:id}}',
                'relationship' => array( 'concord-albums' )
            ),
            array(
                'role' => 'parent',
                'related_to' => $selected_post,
                'relationship' => 'artist_concord-albums'
            )
        ),
        'order' => 'DESC',
    );

I get the following error:
Fatal error: Uncaught TypeError: http_build_query(): Argument #1 ($data) must be of type array, string given in /home/user/scripts/code.php:18
Stack trace:
#0 /home/user/scripts/code.php(18): http_build_query('array(\r\n ...', '', NULL, 1)
#1 {main}
thrown in /home/user/scripts/code.php on line 18

More help is appreciated.
Thanks

[Minesh]

Actually this is a custom work but let me check if I can help you but to help you further I will require access details.

Can you please share details where you added the code to what file?

*** Please make a FULL BACKUP of your database and website.***
I would also eventually need to request temporary access (WP-Admin and FTP) 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.

[Minesh]

I see you have migrated post relationship so instead of using post relationship slug, you will have to use the post type singular names.

Please check:
https://toolset.com/documentation/customizing-sites-using-php/post-relationships-api/how-to-migrate-your-site-to-new-post-relationships/#new-approach

Please keep in mind that code 'relationship' => array( 'writer', 'book' ) will work only with relationships that have existed before the migration.

In addition to that, you are not using the normal way and I'm not sure even you pass the SQL string to your cornerstone layout it will work or not.

I see you have one to many post relationship between "Artists" (one) to "Concord Albums" (many) albums.

Could you please tell me for what single artist you want to display related "Concord Albums"?
Or
You want to display all "Concord Albums" posts group by its parent artist?

[chrisC-25]

Thanks, yes I did migrate, will I be able to display all relationships before and after migration?

I want to be able to display all albums related to its parent artist in dynamic layout.

Example like this which was built with Toolset layout:
hidden link

But I now want to use the Cornerstone layout to build new layout while still using Toolset CPT and the relationship.

So how can I get a working string from http-build-query?

I was able to build same idea with ACF using a string in Cornerstone here:
hidden link

So should query look like this?

$query = new WP_Query( 
    array(
        'post_type' => 'concord-albums',
        'posts_per_page' => -1,
        'toolset_relationships' => array(
            array(
                'role' => 'child',
                'related_to' => get_the_ID(),
                'relationship' => array( 'artist', 'concord-albums' )
            ),
            array(
                'role' => 'parent',
                'related_to' => $selected_post,
                'relationship' => 'artist_concord-albums'
            )
        ),
        'meta_key' => 'wpcf-genre',
        'orderby' => 'meta_value',
        'order' => 'ASC',
    )
);
$posts = $query->posts;

Then how do I get it into a string?

Also if helpful here is the code I used with ACF to get query string made on other site.

$data=array(
			'post_type' 		=> 'releases',
			'meta_key'			=> 'artist',
			'orderby'			=> 'meta_value',
			'order'	 			=> 'DESC',
			'meta_query'		=> array(
				array(
					'key'		=> 'artist',
					'value'		=> '{{dc:post:id}}',
					'compare' => '==',
					'type'		=> 'NUMERIC'
				)
			),
		);

Thanks

[Minesh]

I've created the following shortcode and added it to "Custom Code" section offered by Toolset:
=> hidden link

add_shortcode('display_custom_query', 'func_display_custom_query');
function func_display_custom_query($atts) {
  
global $post;
  
  $query = new WP_Query( 
    array(
        'post_type' => 'concord-albums',
        'posts_per_page' => -1,
        'toolset_relationships' => array(
            array(
                'role' => 'child',
                'related_to' => $post->ID,
                'relationship' => array( 'artist', 'concord-albums' )
            ),
            
        ),
   )
);
$posts = $query->posts;
  echo $query->request; 
  exit;
}

I've added the shortcode to post body of field of the following post: [display_custom_query]
=> hidden link

When you check on frontend, it does display the query at buttom:
=> hidden link

You can adjust the parent ID 13738 in the query with your dynamic value or placeholder:
wp_toolset_associations_1.parent_id = 13738

[chrisC-25]

Not sure I understand? So this is the WP String then?
SELECT wp_posts.* FROM wp_posts JOIN wp_toolset_associations AS wp_toolset_associations_1 ON ( wp_posts.ID = wp_toolset_associations_1.child_id AND wp_toolset_associations_1.relationship_id = 1 AND wp_toolset_associations_1.parent_id = 13738 ) WHERE 1=1 AND ((wp_posts.post_type = 'concord-albums' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private'))) AND wp_toolset_associations_1.parent_id = {{dc:post:id}} ORDER BY wp_posts.post_date DESC

[Minesh]

Yes, that is we called the SQL string.

I replaced the ID 13738 with {{dc:post:id}} with the following SQL string.

SELECT wp_posts.* FROM wp_posts JOIN wp_toolset_associations AS wp_toolset_associations_1 ON ( wp_posts.ID = wp_toolset_associations_1.child_id AND wp_toolset_associations_1.relationship_id = 1 AND wp_toolset_associations_1.parent_id = {{dc:post:id}} ) WHERE 1=1 AND ((wp_posts.post_type = 'concord-albums' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private'))) AND wp_toolset_associations_1.parent_id={{dc:post:id}} ORDER BY wp_posts.post_date DESC

[chrisC-25]

I need a WP query string, this looks like MySQL?

[Minesh]

WP_Query I already shared with the following reply:
=> https://toolset.com/forums/topic/how-to-build-a-wp-query-string-to-pull-posts-based-on-relationship/#post-2419733

Here is the argument to WP_Query:

array(
        'post_type' => 'concord-albums',
        'posts_per_page' => -1,
        'toolset_relationships' => array(
            array(
                'role' => 'child',
                'related_to' => $post->ID,
                'relationship' => array( 'artist', 'concord-albums' )
            ),
             
        ),
   )

You should try to change the following line of code from:

    'related_to' => $post->ID,

To:

   'related_to' =>{{dc:post:id}},

[chrisC-25]

Thank you however when I try to convert that into a string I recieve the following error:
Fatal error: Uncaught TypeError: http_build_query(): Argument #1 ($data) must be of type array, string given in /home/user/scripts/code.php:13
Stack trace:
#0 /home/user/scripts/code.php(13): http_build_query('array(\r\n ...', '', NULL, 1)
#1 {main}
thrown in /home/user/scripts/code.php on line 13

[Minesh]

Well, Toolset works as expected and it returns the correct results.

I see the Doc and there are different loopers available as a source:
=> hidden link

I suggest you should get in touch with your theme's author or support and ask them how you can hook the results you get with either the SQL query or even the posts that is returns using the custom query I mentioned with the following reply:
=> https://toolset.com/forums/topic/how-to-build-a-wp-query-string-to-pull-posts-based-on-relationship/#post-2419733