[Resolved] SQL equivalent of toolset_get_related_posts

[Author]

Posts

[collectiveU]

Hi,

I have the following query in PHP which I want to create via SQL.

Have tried to work backwards but unable to get it working. Any assistance would be great.

Cheers

$relationship_slug = 'event';
$child_posts = toolset_get_related_posts(
$id, // get posts connected to this one
$relationship_slug, // in this relationship
[
'query_by_role' => 'child', // origin post role
'role_to_return' => 'parent', // role of posts to return
'return' => 'post_id', // return array of IDs (post_id) or post objects (post_object)
'limit' => 999, // max number of results
'offset' => 0, // starting from
'orderby' => 'title',
'order' => 'ASC',
'need_found_rows' => false, // also return count of results
'args' => null, // for adding meta queries etc.
]
);

[Waqar]

Hi,

Thank you for contacting us and I'd be happy to assist.

Can you please update your code for the "toolset_get_related_posts" function like this:

// get related posts
$query_by_element = $id; // ID of post to get relationship from
$relationship = 'event'; // relationship slug
$query_by_role_name = 'child'; // $query_by_element is a child in this relation 
$limit = 999; // max number of results
$offset = 0; // starting from
$args = array(); //nothing needed
$return = 'post_id'; // We want Post IDs
$role_name_to_return = 'parent'; // We want parent.

$get_results = toolset_get_related_posts( $query_by_element, $relationship, $query_by_role_name, $limit, $offset, $args, $return, $role_name_to_return );

I hope this helps and please let me know if you need any further assistance around this.

regards,
Waqar

[collectiveU]

Hi Waqar,

Apologies but that is not my question.

My question is how can I achieve the same result but via SQL?

Cheers

[Waqar]

Thanks for writing back and for clearing this further.

While a third-party plugin like 'Query Monitor' ( https://wordpress.org/plugins/query-monitor/ ), can be used to examine the exact SQL query that is taking place in the background of the "toolset_get_related_posts" function, using the SQL query directly, is not usually recommended.

Is there any special reason, you'd like to use a direct SQL query?

[collectiveU]

We're building REST API endpoints and our DB is huge.
As a result, our API performance is quite slow and over certain number of users, our endpoints and servers are failing.

As a result, we've decided to develop an ExpressJS API solution which will directly talk to the backend DB and as a result, we want to get all the required data.

I'll review Query Monitor - thanks.

Cheesr

[Waqar]

Thank you for sharing this and it makes sense.

Our main concern over recommending against the direct SQL queries is future-proofing. Structural changes involving database structure are very rare and are introduced after careful consideration, with backward compatibility in mind. However, even in a very less likely event, if something is changed in the future, you'll need to update your SQL queries accordingly too.

[collectiveU]

My issue is resolved now by using Query Monitor to get the SQL. Thank you!