How to get products and categories from the WP database that have a parent category?


Warning: count(): Parameter must be an array or an object that implements Countable in /home/styllloz/public_html/qa-theme/donut-theme/qa-donut-layer.php on line 274
0 like 0 dislike
23 views
Hello. Faced with the problem, fails to reach the desired products from the database. Do a search on the site
in the database the data is stored:
`wp_posts` - information about the products
Eg. 'ID' => '8719' ,' post_title' => 'Sneakers 1', 'post_type' = 'product'

`wp_term_relationships` - matching products and categories
Eg. 'object_id' => '8719','term_taxonomy_id' => '273'

`wp_term_taxonomy` - more detailed information about the category
Eg. 'term_taxonomy_id' => '273','term_id' => '273','taxonomy' => 'product_cat','parent' => '272'

Here nuance, to the product with id = 8719 Tied to only one category with id = 273. But this category has a parent category (they are stored in the `wp_term_taxonomy` ), and to the product, they are not bound.
'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'

You want to search for the name of the parent categories, as shown this product.
In table `wp_terms` is the name of the category.
'term_id' => '273','name' => 'Category1','slug' => 'kategory1' 'term_id' => '272','name' => 'Категория2','slug' => 'kategory2' 'term_id' => '160','name' => 'Категория3','slug' => 'kategory3'

I wrote a function that gets a list of parent categories of product:
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 ;

by | 23 views

1 Answer

0 like 0 dislike
Here is the solution:
Firstly, the function returns a string, and the string is not a list (more on Why the function does not correctly return the value in the WHERE clause? )
Secondly, rewrite the query:
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%'
by
110,608 questions
257,186 answers
0 comments
36,635 users