How to write a request to the wordpress database?

Question

SQL

For element 1 (id1 of this element) of taxonomy1 (id_t1), get a list of posts (id2) in which it is included (or vice versa, it is included, I don’t know how to say it correctly), for this list of posts, get a list (id3) of taxonomy2 (id_t2)

Answer 1

1. Get the ID of posts in a specific category:

$args = array(
    'post_type' => 'post', // изолируем нужный post type
    'post_status' => 'publish', // изолируем только опубликованные записи
    'category__in' => array( 15 ), // где 15 - ID вашей категории
    'posts_per_page' => -1, // забираем все посты, отвечающие требованиям
    'fields' => 'ids', // возвращаем только массив ID найденных записей
    'cache_results' => true, // кешируем полученные результаты
    'no_found_rows' => true, // не считаем общее количество найденных записей
    'update_post_meta_cache' => false, // не забираем и не кешируем метаданные для этих записей
    'update_post_term_cache' => false, // не забираем и не кешируем термины для этих записей
);
$posts_in_category = new WP_Query( $args );

Half of $args is query optimization and speedup. We receive only the necessary data, we do not request, process or cache what we do not need at all. The goal is to get and cache only the array of post IDs that have the desired category. All this is in the documentation .
2. Get the tags that are used by these posts (obtained above):

$args = array(
    'taxonomy' => 'post_tag', // изолируем нужную таксономию
    'object_ids' => $posts_in_category->posts, // получаем только теги, которые присвоеные объектам с этими ID
    'update_term_meta_cache' => false, // true|false - получать ли (и кешировать ли) метаданные терминов
);
$tags_in_posts = get_terms( $args );

At the output, you will have an array of tags. You can see more options here . Actually, instead of get_terms() you can use WP_Term_Query .
I made a mistake in the code, the line 'object_ids' => $posts_in_category,should be 'object_ids' => $posts_in_category->posts,- because the variable will contain the WP_Query object, and the posts property will already contain the IDs of the posts found.
Another important nuance and another mistake - if the category is included using the 'cat' parameter, then the request will be executed by default with the 'include_children' => true parameter, which is not necessary for us, because it will distort the result and include unnecessary posts. Rewrote this snippet as well - instead of using the standard 'cat' parameter, we now use 'category__in' - this will include only posts that are assigned this particular category.
WP_Query will only execute 1 query:

SELECT wp_posts.ID
FROM wp_posts 
LEFT JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE 1=1 
AND ( wp_term_relationships.term_taxonomy_id IN (2) )
AND wp_posts.post_type = 'post'
AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC

In this code, 2 = category ID on my test site. The output is X posts that are assigned this particular category.
Next, get_terms will also execute only 1 request:

SELECT t.*, tt.*
FROM wp_terms AS t 
INNER JOIN wp_term_taxonomy AS tt
ON t.term_id = tt.term_id
INNER JOIN wp_term_relationships AS tr
ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE tt.taxonomy IN ('post_tag')
AND tr.object_id IN (19, 15, 13, 1)
ORDER BY t.name ASC

In this code, 19, 15, 13, 1 are the post IDs that we received with the first request. This way, only the post_tag taxonomy terms that are assigned to those specific posts will be retrieved.