How to get products and categories from WP DB that have parent categories?

Question

MySQL

Hello. Faced a problem, it is impossible to get the necessary goods from a DB. I do a site search,
the data is stored in the database:
`wp_posts` - information about products

Напр., 'ID' => '8719' ,' post_title' => 'Кеды 1', 'post_type' = 'product'

`wp_term_relationships` - product and category correspondences

Напр.,  'object_id' => '8719','term_taxonomy_id' => '273'

`wp_term_taxonomy` - more detailed information about the category

Напр.,  'term_taxonomy_id' => '273','term_id' => '273','taxonomy' => 'product_cat','parent' => '272'

Here is a nuance, only one category with id = 273 is linked to the product with id = 8719. But this category has parent categories (they are also stored in `wp_term_taxonomy` ), but they are not linked to the product.

'term_taxonomy_id' => '273','term_id' => '273','taxonomy' => 'product_cat','parent' => '272' 
'term_taxonomy_id' => '272','term_id' => '272','taxonomy' => 'product_cat','parent' => '160'
'term_taxonomy_id' => '160','term_id' => '160','taxonomy' => 'product_cat','parent' => '0'

It is necessary that when searching by the name of the parent categories, this product is also shown.
The `wp_terms` table holds the category name.

'term_id' => '273','name' => 'Категория1','slug' => 'kategory1'
'term_id' => '272','name' => 'Категория2','slug' => 'kategory2'
'term_id' => '160','name' => 'Категория3','slug' => 'kategory3'

I wrote a function that receives a list of parent product categories:

DELIMITER //  
  
CREATE FUNCTION br_get_parent_caterory_proc(var1 INT) RETURNS CHAR(100)
 
DETERMINISTIC  
SQL SECURITY DEFINER  
COMMENT 'Get parent category procedure'
BEGIN
  DECLARE parent_id INT;  
    DECLARE result, result_while, parent_id_vchar  CHAR(100);
    SET parent_id = 0;
    SET parent_id_vchar = '';
    SET result_while = '';
    SET result = var1;     
    
    SET parent_id = (SELECT `parent` FROM wp_term_taxonomy  WHERE `term_taxonomy_id` = var1);  
  SET result = concat(result, ", ", parent_id);
      
  	WHILE parent_id <> 0 DO  
       SET parent_id = ( SELECT `parent` FROM wp_term_taxonomy  WHERE `parent` <> 0 AND `term_taxonomy_id` = parent_id );      
       IF (parent_id IS NOT NULL) THEN 
        	 SET parent_id_vchar = CONVERT(parent_id, CHAR(100));   
             SET result_while = concat(result_while, ", ", parent_id_vchar);
    	END IF; 
    END WHILE;  
    
    SET result = concat(result, result_while);
    RETURN result;
    
END//  

DELIMITER ;

SELECT br_get_parent_caterory_proc(273);
Выводит:  272, 160  (или можно сделать полный список 273, 272, 160)

How can a query be formed so that a product (duplicate) is formed in the combined tables, but it has all parent categories? those. the output is something like, i.e. Looking for Category3 and should find item 8719
'ID' => '8719' ,' post_title' => 'Sneakers 1', 'term_id' => '273', 'name' => 'Category1
'ID' => '8719' ,' post_title' => 'Sneakers 1', 'term_id' => '272', name' => 'Category2',
'ID' => '8719' ,' post_title' => 'Sneakers 1', 'term_id' => '160', 'name' => 'Category3'
So far I've come to this, but what's wrong? I've been fighting for several days, nid help) Thank you.

SELECT *
FROM (((`wp_posts` p 
INNER JOIN `wp_term_relationships` tr ON p.ID = tr.object_id)
INNER JOIN `wp_term_taxonomy` tt ON tt.term_taxonomy_id = tr.term_taxonomy_id)
INNER JOIN `wp_terms` t ON t.term_id IN (SELECT br_get_parent_caterory_proc(t.term_id))) -- = tt.term_id)
WHERE post_type = 'product' AND taxonomy = 'product_cat' AND name LIKE '%Категория3%'

Answer 1

Here is the solution:
Firstly, the function returns a string, and the string is not a list (more Why does the function return the value incorrectly in the WHERE condition? )
Secondly, I rewrote the query like this:

SELECT *
FROM (((`wp_posts` p 
INNER JOIN `wp_term_relationships` tr ON p.ID = tr.object_id)
INNER JOIN `wp_term_taxonomy` tt ON tr.term_taxonomy_id = tt.term_taxonomy_id)
INNER JOIN `wp_terms` t ON FIND_IN_SET(t.term_id, br_get_parent_cat(tt.term_id)))
WHERE post_type = 'product' AND taxonomy = 'product_cat' AND t.name LIKE '%Категория3%'