Tablas Woocommerce y Learndash

##Woocommerce
##Todos los artículos de woocommerce

SELECT product.ID as product_id, product.post_title as product_name, replace(product.post_content, '"', "'") as product_content, product_sku.meta_value as product_sku, product_price.meta_value as product_price, product_weight.meta_value as product_weight
FROM wp_posts as product
LEFT JOIN wp_postmeta as product_sku ON product.ID = product_sku.post_ID
LEFT JOIN wp_postmeta as product_price ON product.ID = product_price.post_ID
LEFT JOIN wp_postmeta as product_weight ON product.ID = product_weight.post_ID
WHERE (product.post_type = 'product' OR product.post_type = 'product_variation') AND product_sku.meta_key = '_sku' AND product_price.meta_key = '_price' AND product_weight.meta_key = '_weight'
ORDER BY product_id ASC

select * from wp_posts where post_type = 'product' and post_status ='publish' and post_title='Yoga en Vivo – Pack 4 Clases';
##Cursos relacionados con el producto
select meta_value from wp_postmeta where post_id in (select ID from wp_posts where post_type = 'product' and post_status ='publish' and post_title='Yoga en Vivo – Pack 4 Clases') AND meta_key ='_related_course';
##consulta rápida de datos del producto
select * from wp_wc_product_meta_lookup where product_id in (select ID from wp_posts where post_type = 'product' and post_status ='publish' and post_title='Yoga en Vivo – Pack 4 Clases');
##Todas las ordenes realizadas por un determinado producto
select * from wp_wc_order_product_lookup where product_id in (select ID from wp_posts where post_type = 'product' and post_status ='publish' and post_title='Yoga en Vivo – Pack 4 Clases') ;
##ordenes realizadas por un cliente
select * from wp_wc_order_product_lookup where customer_id =34;
##Categorias
SELECT wp_terms.* FROM wp_terms LEFT JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id WHERE wp_term_taxonomy.taxonomy = 'product_cat';
#Learndash
##cursos de learndash
select * from wp_posts where post_type = 'sfwd-courses' and post_status ='publish';
# Consultas para un usuario determinado
set @usuario=34;
# cursos de un usuario
SELECT `course_id` FROM `wp_learndash_user_activity` WHERE `user_id` = @usuario group by course_id;
# actividad del usuario en learndash
SELECT `activity_id`,`user_id`,`post_id`,`course_id`,`activity_type`,`activity_status`, from_unixtime(`activity_started`) as iniciada,from_unixtime(`activity_completed`) as completada ,from_unixtime(`activity_updated`) as modificada FROM `wp_learndash_user_activity` WHERE `user_id` =@usuario;
# datos de cada actividad
SELECT * FROM `wp_learndash_user_activity_meta` where activity_id in(SELECT `activity_id` FROM `wp_learndash_user_activity` WHERE `user_id` =@usuario);
# inicio de cada curso en learndash ordenado por usuario
SELECT `activity_id`,`user_id`,`post_id`,`course_id`,`activity_type`,`activity_status`, from_unixtime(`activity_started`) as iniciada,from_unixtime(`activity_completed`) as completada ,from_unixtime(`activity_updated`) as modificada FROM `wp_learndash_user_activity` WHERE activity_type=’access’ ORDER by user_id ASC;

 


# Consultas para un usuario determinado
set @usuario=34;

##ordenes realizadas por un cliente
select * from wp_wc_order_product_lookup where customer_id =@usuario;

##Todos los artículos de woocommerce
select * from wp_posts where post_type = ‘product’ and post_status =’publish’
AND id in(select product_id from wp_wc_order_product_lookup where customer_id =@usuario);

##Cursos relacionados con el producto
select * from wp_postmeta where post_id in (select ID from wp_posts where meta_key=’_related_course’
AND post_type = ‘product’ and post_status =’publish’
AND id in(select product_id from wp_wc_order_product_lookup where customer_id =@usuario));

# cursos de un usuario
SELECT `course_id` FROM `wp_learndash_user_activity` WHERE `user_id` = @usuario group by course_id;
 

set @usuario=98;

SELECT * FROM `wp_learndash_user_activity` WHERE `user_id`=@usuario;

SELECT * FROM `wp_learndash_user_activity_meta` WHERE activity_id in (SELECT activity_id FROM `wp_learndash_user_activity` WHERE `user_id`=@usuario);

Otro sitio más de Los Lucero