magento-select-product-mysql

magento-select-product-mysql

-- Product entity type ID
SET @etype = (SELECT 
                    entity_type_id
                FROM
                    eav_entity_type
                WHERE
                    entity_type_code = 'catalog_product');
-- Product name attribute ID
SET @name  = (SELECT 
            attribute_id
        FROM
            eav_attribute
        WHERE
            attribute_code = 'name'
                AND entity_type_id = @etype);
-- Product image attribute ID
SET @image  = (SELECT 
            attribute_id
        FROM
            eav_attribute
        WHERE
            attribute_code = 'image'
                AND entity_type_id = @etype);
-- Product price attribute ID
SET @price  = (SELECT 
            attribute_id
        FROM
            eav_attribute
        WHERE
            attribute_code = 'price'
                AND entity_type_id = @etype);
-- Admin store ID
SET @store = 0;

-- Query
SELECT 
    e.entity_id AS 'id',
    e.sku,
    v1.value AS 'name',
    v2.value AS 'image',
    si.qty AS 'stock qty',
    d1.value AS 'price'
FROM
    catalog_product_entity e
        LEFT JOIN
    cataloginventory_stock_item si ON e.entity_id = si.product_id
        LEFT JOIN
    catalog_product_entity_varchar v1 ON e.entity_id = v1.entity_id
        AND v1.store_id = @store
        AND v1.attribute_id = @name
        LEFT JOIN
    catalog_product_entity_varchar v2 ON e.entity_id = v2.entity_id
        AND v2.store_id = @store
        AND v2.attribute_id = @image
        LEFT JOIN
    catalog_product_entity_decimal d1 ON e.entity_id = d1.entity_id
        AND d1.store_id = @store
        AND d1.attribute_id = @price;
        
        
// Mysql select product
use xxyyy;
set @ptype = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product');
set @name = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = @ptype);
set @image = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'image' AND entity_type_id = @ptype);
set @price = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'price' AND entity_type_id = @ptype);
SET @store = 4;

SELECT 	increment_id, entity_id FROM sales_order where increment_id='xxxyyy';
SELECT order_id, sku FROM sales_order_item WHERE order_id='xxyy';

SELECT 
    e.entity_id AS 'id',
    e.sku,
    si.qty AS 'stock qty',
    si.website_id

FROM
    catalog_product_entity e
        LEFT JOIN
    cataloginventory_stock_item si ON e.entity_id = si.product_id 
		AND si.website_id = 5
		WHERE e.sku = 'xxyy';


SELECT 
    e.entity_id AS 'id',
    e.sku,
    si.qty AS 'stock qty',
    si.website_id

FROM
    catalog_product_entity e
        LEFT JOIN
    cataloginventory_stock_status si ON e.entity_id = si.product_id 
		AND si.website_id = 5
		WHERE e.sku = 'xxyy';