Friends, I have simple products and variable products in WooCommerce product tables, now I want to select simple products with a query, so I use the following command:
SELECT * FROM wp_posts WHERE post_type = 'product';
In the wp_posts table, products are registered in two ways: 1- post_type = product 2- post_type = product_variation
To find variable products very easily, you can use the following command:
SELECT * FROM wp_posts WHERE post_type = 'product_variation';
That is, we set the value of post_type equal to "product_variation" and the output will show us the variable products, But for simple products, the value of post_type should be equal to "product", Now the problem that occurs in the output is that some of these products that have post_type equal to "product" are not actually simple products and the parent for the products is variable.
Now, is there a way to have only simple products in the output and ignore the parent products?
I hope you understand what I mean!
I was able to find this solution for my question!
MySQL query to list all Simple Products:
SELECT
posts.ID,
posts.post_title,
posts.post_author,
posts.post_date,
posts.post_date_gmt,
posts.post_content,
posts.post_excerpt,
posts.post_status,
posts.comment_status,
posts.ping_status,
posts.post_password,
posts.post_name,
posts.to_ping,
posts.pinged,
posts.post_modified,
posts.post_modified_gmt,
posts.post_content_filtered,
posts.post_parent,
posts.guid,
posts.menu_order,
posts.post_type,
posts.post_mime_type,
posts.comment_count
FROM
wp_posts AS posts
INNER JOIN wp_term_relationships AS term_relationships
ON
posts.ID = term_relationships.object_id
INNER JOIN wp_term_taxonomy AS term_taxonomy
ON
term_relationships.term_taxonomy_id = term_taxonomy.term_taxonomy_id
INNER JOIN wp_terms AS terms
ON
term_taxonomy.term_id = terms.term_id
WHERE
term_taxonomy.taxonomy = 'product_type' AND terms.slug = 'simple'
AND posts.post_type = 'product';
Just replace
simple
from
AND terms.slug = 'simple'
line with
variable
to get all Variable Products;