i'm using wordpress / woocommerce database structure , looking make custom query.
basically need grab products without variation , i'm having trouble joining ids.
a product , product_variation both posts associated postmeta.
example schema:
wp_posts wp_postmeta id post_id post_parent meta_key post_type meta_value post_status an example of wp_posts table this:
id name post_parent meta_key meta_value 2 tshirt null 3 pants null 4 2 variation_type red 5 2 variation_type blue here can see 4 posts; 2 products , 2 variations. variation's post_parent point product. have red , blue tshirt
what need, grab products with blue variation, meaning need join post_parent id.
this have far.
select * wp_posts wp1 inner join wp_postmeta pm1 on (pm1.post_id = wp1.id) inner join wp_postmeta pm2 on (pm2.post_id = wp1.id) join wp_posts wp2 on (wp2.post_parent = wp1.id) , wp1.post_type = 'product_variation' , wp1.post_status = 'publish' , pm1.meta_key = 'groups-groups_read_post' , pm1.meta_value = 'vet_read' , pm2.meta_key = 'variation_type' , pm2.meta_value = 'blue' i believe i'm doing joins wrong. guidance appreciated.
this process pretty complicated there lot of things going on under hood. in project of mine have grab post ids (primary product ids) based on attribute.
select distinct p.post_parent `wp_posts` p left join `wp_postmeta` pm on p.id = pm.post_id left join `wp_postmeta` pm2 on p.id = pm2.post_id p.post_type = 'product_variation' , ( (pm.meta_key = 'attribute_pa_size' , pm.meta_value = "xl") or (pm.meta_key = 'attribute_size' , pm.meta_value = "xl") ) , pm2.meta_key = '_stock' , pm2.meta_value != '0' i had variation size , wanted grab products have variation , of course not out of stock. notice had check meta_key twice because when enter attribute in product editor page woocommerce store attribute_{$attribute_name} , save in attributes section. if select dropdown of available attributes saved have attribute_pa_{$attribute_name}. sure can modify sql based on requirement.
note: if intend use custom query alongside pre_get_posts set post__in parameter regrettably won't work. woocommerce has filter loop_shop_post_in overrides this. let know.
hope might you.
