get_posts_related_by_common_terms.php

get_posts_related_by_common_terms.php

<?php
function get_posts_related_by_common_terms(int $post_id, int $limit = 2, int $offset=0)
{
	global $wpdb;
	// $tax = 'category';
	// $post_types = get_taxonomy($tax)->object_type;
	$post_types = ['post'];
	$in_post_types = implode(',', array_map(function ($post_type) {
		return "'{$post_type}'";
	}, $post_types));

	__get_post_primary_term('category', $post_id);
	$sql = $wpdb->prepare("
		SELECT tr.object_id, COUNT(*) AS count
			FROM {$wpdb->term_relationships} tr
			JOIN {$wpdb->term_taxonomy} tt
				ON tt.term_taxonomy_id = tr.term_taxonomy_id
			JOIN {$wpdb->posts} p
				ON p.ID = tr.object_id
			WHERE
				tr.term_taxonomy_id IN (
					SELECT tr2.term_taxonomy_id
						FROM {$wpdb->term_relationships} tr2
						JOIN {$wpdb->term_taxonomy} tt2
							ON tt2.term_taxonomy_id = tr2.term_taxonomy_id
						WHERE tr2.object_id = %d
				)
				AND tr.object_id != %d
				AND p.post_status = 'publish'
				AND p.post_type IN ({$in_post_types})
			GROUP BY tr.object_id
			ORDER BY count DESC
			LIMIT {$offset}, {$limit}
	", $post_id, $post_id);
	
	$rows = $wpdb->get_results($sql);
	$ids = array_map(function ($row) {
		return $row->object_id;
	}, $rows);
	$args = [
		'post_type' => $post_types,
		'post__in'  => $ids,
		'orderby'   => 'date',
		'order'	 => 'DESC'
	];
	if (count($ids) === 0) {
		$args['post__in'] = [-1];
	}
	$query = new WP_Query($args);
	return $query;
}