Magento 2 SQL Queries Bible

magento2-get-all-products-with-sku-and-url.sql

SELECT e.entity_id,
       e.sku,
       v.value as url_key
FROM catalog_product_entity e
INNER JOIN catalog_product_entity_varchar v
    ON e.entity_id = v.entity_id
    AND attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'url_key' AND entity_type_id = 4);

magento2-get-all-reviews-with-product-image.sql

# GET ALL PRODUCTS REVIEWS With Product Image
# ------------------------------------------------------
SELECT
	`p`.sku,
	`title`.value AS product_name,
	`img`.value AS product_image,
	`r`.review_id,
	`r`.created_at,
	`rd`.title,
	`rd`.detail,
	`rd`.nickname,
	`rd`.customer_id,
	`c`.firstname,
	`c`.lastname,
	`c`.email,
	`url`.value as url_key,
	(SELECT AVG(`value`) AS `ranting` FROM rating_option_vote WHERE review_id = `r`.review_id) AS ranting
FROM `review` AS `r`
INNER JOIN `review_detail` AS `rd`
	ON `r`.review_id = `rd`.review_id
	AND `rd`.store_id = 2
	AND `rd`.customer_id IS NOT NULL
INNER JOIN `catalog_product_entity` AS `p`
	ON `r`.entity_pk_value = `p`.entity_id
INNER JOIN `customer_entity` AS `c`
	ON `rd`.customer_id = `c`.entity_id
INNER JOIN `catalog_product_entity_varchar` AS `url`
    ON `r`.entity_pk_value = `url`.entity_id
    AND `url`.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'url_key' AND entity_type_id = 4)
INNER JOIN `catalog_product_entity_varchar` AS `title`
    ON `r`.entity_pk_value = `title`.entity_id
    AND `title`.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = 4)
INNER JOIN `catalog_product_entity_varchar` AS `img`
    ON `r`.entity_pk_value = `img`.entity_id
    AND `img`.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'image' AND entity_type_id = 4)
WHERE `r`.status_id = 1
AND `r`.entity_id = 1
GROUP BY `r`.review_id;

magento2-get-all-reviews.sql

# GET ALL PRODUCTS REVIEWS
# ------------------------------------------------------
SELECT 
	`p`.sku,
	`title`.value as product_name,
	`r`.review_id,
	`r`.created_at,
	`rd`.title,
	`rd`.detail,
	`rd`.nickname,
	`rd`.customer_id,
	`c`.firstname,
	`c`.lastname,
	`c`.email,
	`url`.value as url_key,
	(SELECT AVG(`value`) AS `ranting` FROM rating_option_vote WHERE review_id = `r`.review_id) AS ranting
FROM `review` AS `r`
INNER JOIN `review_detail` AS `rd` 
	ON `r`.review_id = `rd`.review_id 
	AND `rd`.store_id = 2
	AND `rd`.customer_id IS NOT NULL # Only reviews with customers logged
INNER JOIN `catalog_product_entity` AS `p`
	ON `r`.entity_pk_value = `p`.entity_id
INNER JOIN `customer_entity` AS `c`
	ON `rd`.customer_id = `c`.entity_id
INNER JOIN `catalog_product_entity_varchar` AS `url`
    ON `r`.entity_pk_value = `url`.entity_id
    AND `url`.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'url_key' AND entity_type_id = 4)
INNER JOIN `catalog_product_entity_varchar` AS `title`
    ON `r`.entity_pk_value = `title`.entity_id
    AND `title`.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = 4)
WHERE `r`.status_id = 1 # Published Reviews
AND `r`.entity_id = 1 # Product Reviews
GROUP BY `r`.review_id;

													    			    

magento2-get-customer-info-from-order.sql

# Get Customer Data from orders
SELECT `increment_id`, `customer_id`, `customer_firstname`, `customer_email`, `customer_taxvat` FROM `sales_flat_order`;

magento2-get-customer-taxvat.sql

# Get Customers taxvat
SELECT ce.*, ea.attribute_code, cev.value
  FROM customer_entity AS ce 
  LEFT JOIN eav_attribute AS ea ON ce.entity_type_id = ea.entity_type_id AND ea.backend_type = 'varchar'
  LEFT JOIN customer_entity_varchar AS cev ON ce.entity_id = cev.entity_id AND ea.attribute_id = cev.attribute_id
  WHERE ea.attribute_code = 'taxvat';