Cacher is the code snippet organizer for pro developers

Получить ску, картинку и название товара

SELECT cpe.sku, cpevName.value AS name, cpevImg.value As img
FROM `catalog_product_entity` AS cpe
INNER JOIN `catalog_product_entity_varchar` AS cpevName
ON cpevName.entity_id = cpe.entity_id AND cpevName.attribute_id = 71
INNER JOIN `catalog_product_entity_varchar` AS cpevImg
ON cpevImg.entity_id = cpe.entity_id AND cpevImg.attribute_id = 85
INNER JOIN `catalog_category_product` AS ccp 
ON ccp.product_id = cpe.entity_id
WHERE ccp.category_id = 3 AND cpe.type_id = "grouped"
LIMIT 100

Получить заказы и имеилы из магенты

SELECT distinct sfo.increment_id, sfoa.city, sfo.created_at, sfoa.email, sfoa.postcode
FROM `sales_flat_order` AS sfo 
INNER JOIN `sales_flat_order_address` AS sfoa 
ON sfoa.parent_id = sfo.entity_id 
WHERE DATE(sfo.created_at) > '2013-09-01'
LIMIT 2

Price Parser

Получить отчет по количеству распарсенных товаров по конкуренту

CREATE TABLE `parsed_cnt_{competitor}`
SELECT COUNT(url), brand, sizeId
FROM `parsed_products` 
WHERE url LIKE '%{competitor}%'
GROUP BY brand, sizeId 
ORDER BY brand DESC, sizeId ASC

Получить количество по бренду и размеру включенных наших товаров

CREATE TABLE our_products_grouped_by_brand
SELECT COUNT(sku), manufacturer, productSize
FROM `our_products`
WHERE isInStock = 1 AND category = 'Постельное белье'
GROUP BY manufacturer, productSize
ORDER BY manufacturer DESC, productSize ASC

Получить количество совпадений, группируя по размеру и количеству

CREATE TABLE `matches_cnt`
SELECT op.productSize, op.manufacturer, COUNT(op.sku)
FROM `matches` AS m
INNER JOIN `our_products` AS op
ON m.ourProductSku = op.sku
WHERE m.competitorLogin = 'wildberries'
GROUP BY op.manufacturer, op.productSize

PRICE-PARSER

Проверка на то, что есть распарсенные товары, но нет по ним отзывов.

Т.е. отсюда проверим, реально ли нет отзывов по конкуренту.

SELECT pp.* 
FROM `parsed_products` AS pp
WHERE pp.id NOT IN (
SELECT parsedProductId AS id
FROM `parsed_product_reviews`
)   
AND pp.url LIKE '%wildberries%'