Wednesday, November 26, 2014

MySQL query for Baisc Product Infomration in Magento

Product List:

SELECT
  core_website.name AS siteName
, core_website.code AS siteCode
, core_store_group.name AS storeName
, core_store.code AS storeCode
, catalog_product_entity.entity_id
, catalog_product_entity.entity_type_id
, catalog_product_entity.attribute_set_id
, catalog_product_entity.type_id
, catalog_product_entity.sku
, catalog_product_entity.updated_at
, prodName.value AS prodName
, prodPrice.value AS prodPrice
, prodWeight.value AS prodWeight
, cataloginventory_stock_status.qty
FROM core_website
INNER JOIN core_store_group ON core_website.website_id = core_store_group.website_id
INNER JOIN core_store ON core_website.website_id = core_store.website_id
INNER JOIN catalog_product_website ON core_website.website_id = catalog_product_website.website_id
INNER JOIN catalog_product_entity ON catalog_product_website.product_id = catalog_product_entity.entity_id
INNER JOIN catalog_product_entity_varchar AS prodName ON catalog_product_entity.entity_id = prodName.entity_id
INNER JOIN catalog_product_entity_decimal AS prodPrice ON catalog_product_entity.entity_id = prodPrice.entity_id
INNER JOIN catalog_product_entity_decimal AS prodWeight ON catalog_product_entity.entity_id = prodWeight.entity_id
INNER JOIN cataloginventory_stock_status ON core_website.website_id = cataloginventory_stock_status.website_id AND catalog_product_entity.entity_id = cataloginventory_stock_status.product_id
WHERE
1 = 1
AND core_website.website_id = 1 # US Site
AND core_store_group.group_id = 1 # US Store
AND core_store.store_id = 1 # English

AND catalog_product_entity.entity_type_id = 4 # Product
AND catalog_product_entity.type_id = 'simple' # Configurable product has no "weight"

### go to eav_attribute table for other attributes
AND prodName.attribute_id = 71 # Product Name
AND prodName.store_id = 0 # admin store
AND prodPrice.attribute_id = 75 # Product Price
AND prodPrice.store_id = 0 # admin store
AND prodWeight.attribute_id = 80 # Product Weight
AND prodWeight.store_id = 0 # admin store

AND cataloginventory_stock_status.stock_id = 1 # Default Stock (Warehouse ?)

ORDER BY catalog_product_entity.sku

With extra attributes:

SELECT
  core_website.name AS siteName
#, core_website.code AS siteCode
#, core_store_group.name AS storeName
#, core_store.code AS storeCode
, catalog_product_entity.entity_id
#, catalog_product_entity.entity_type_id
#, catalog_product_entity.attribute_set_id
#, catalog_product_entity.type_id
, SUBSTR(catalog_product_entity.sku, 1, 2) AS itemNum2Char
, SUBSTR(catalog_product_entity.sku, 1, 3) AS itemNum3Char
, catalog_product_entity.sku
, catalog_product_entity.updated_at
, prodSizeOptionValue.value AS prodSize
, prodColorOptionValue.value AS prodColor
, prodName.value AS prodName
, prodURLKey.value AS prodURLKey
, prodPrice.value AS prodPrice
, prodWeight.value AS prodWeight
, cataloginventory_stock_status.qty
FROM core_website
INNER JOIN core_store_group ON core_website.website_id = core_store_group.website_id
INNER JOIN core_store ON core_website.website_id = core_store.website_id
INNER JOIN catalog_product_website ON core_website.website_id = catalog_product_website.website_id
INNER JOIN catalog_product_entity ON catalog_product_website.product_id = catalog_product_entity.entity_id
# Product Size
INNER JOIN catalog_product_entity_int AS prodSize ON catalog_product_entity.entity_id = prodSize.entity_id
INNER JOIN eav_attribute_option_value AS prodSizeOptionValue ON prodSize.value = prodSizeOptionValue.option_id
# Product Color
INNER JOIN catalog_product_entity_int AS prodColor ON catalog_product_entity.entity_id = prodColor.entity_id
INNER JOIN eav_attribute_option_value AS prodColorOptionValue ON prodColor.value = prodColorOptionValue.option_id
# Product Name
INNER JOIN catalog_product_entity_varchar AS prodName ON catalog_product_entity.entity_id = prodName.entity_id
# Product URL Key
INNER JOIN catalog_product_entity_varchar AS prodURLKey ON catalog_product_entity.entity_id = prodURLKey.entity_id
# Product Price
INNER JOIN catalog_product_entity_decimal AS prodPrice ON catalog_product_entity.entity_id = prodPrice.entity_id
# Product Weight
INNER JOIN catalog_product_entity_decimal AS prodWeight ON catalog_product_entity.entity_id = prodWeight.entity_id
# Product Quantity
INNER JOIN cataloginventory_stock_status ON core_website.website_id = cataloginventory_stock_status.website_id AND catalog_product_entity.entity_id = cataloginventory_stock_status.product_id
WHERE
1 = 1
AND core_website.website_id = 1 # US Site
AND core_store_group.group_id = 1 # US Store
AND core_store.store_id = 1 # English

AND catalog_product_entity.entity_type_id = 4 # Product
AND catalog_product_entity.type_id = 'simple' # Configurable product has no "weight"

### go to eav_attribute table for other attributes
AND prodSize.attribute_id = 133 # Walker Size
AND prodColor.attribute_id = 134 # Walker Color

AND prodName.attribute_id = 71 # Product Name
AND prodName.store_id = 0 # admin store

AND prodURLKey.attribute_id = 97 # Product URL KEY
AND prodURLKey.store_id = 0 # admin store

AND prodPrice.attribute_id = 75 # Product Price
AND prodPrice.store_id = 0 # admin store

AND prodWeight.attribute_id = 80 # Product Weight
AND prodWeight.store_id = 0 # admin store

AND cataloginventory_stock_status.stock_id = 1 # Default Stock (Warehouse ?)

ORDER BY catalog_product_entity.sku

No comments: