SQL

Deleting all products using SQL (Magento 1)

Mex 2016/03/10 Magento Solutions

To delete all product related data, use the following query:

DELETE FROM catalog_product_entity;

1	DELETE FROM catalog_product_entity;

Due to the constraints it will delete all related data from other tables as well.

Show solution

Getting products with or without custom options using SQL (Magento 1)

Mex 2016/01/15 Magento Solutions

To get all products that do not have custom options, simply run the following SQL query:

SELECT
  *
FROM catalog_product_entity
WHERE entity_id NOT IN (SELECT DISTINCT
    product_id
  FROM catalog_product_option);

SELECT

FROM catalog_product_entity

WHERE entity_id NOT IN (SELECT DISTINCT

product_id

FROM catalog_product_option);

To get all products that do have custom options, remove the “NOT” from the above query.

Show solution

Solving the “Invalid backend model specified: catalog/attribute_backend_customlayoutupdate” problem (Magento 1)

Mex 2015/01/08 Magento Solutions

Run the following query:

SELECT
  *
FROM eav_attribute
WHERE (
`attribute_id` LIKE '%stomlayoutupdate%'
OR `entity_type_id` LIKE '%stomlayoutupdate%'
OR `attribute_code` LIKE '%stomlayoutupdate%'
OR `attribute_model` LIKE '%stomlayoutupdate%'
OR `backend_model` LIKE '%stomlayoutupdate%'
OR `backend_type` LIKE '%stomlayoutupdate%'
OR `backend_table` LIKE '%stomlayoutupdate%'
OR `frontend_model` LIKE '%stomlayoutupdate%'
OR `frontend_input` LIKE '%stomlayoutupdate%'
OR `frontend_label` LIKE '%stomlayoutupdate%'
OR `frontend_class` LIKE '%stomlayoutupdate%'
OR `source_model` LIKE '%stomlayoutupdate%'
OR `is_required` LIKE '%stomlayoutupdate%'
OR `is_user_defined` LIKE '%stomlayoutupdate%'
OR `default_value` LIKE '%stomlayoutupdate%'
OR `is_unique` LIKE '%stomlayoutupdate%'
OR `note` LIKE '%stomlayoutupdate%'
);

SELECT

FROM eav_attribute

WHERE (

`attribute_id` LIKE '%stomlayoutupdate%'

OR `entity_type_id` LIKE '%stomlayoutupdate%'

OR `attribute_code` LIKE '%stomlayoutupdate%'

OR `attribute_model` LIKE '%stomlayoutupdate%'

OR `backend_model` LIKE '%stomlayoutupdate%'

OR `backend_type` LIKE '%stomlayoutupdate%'

OR `backend_table` LIKE '%stomlayoutupdate%'

OR `frontend_model` LIKE '%stomlayoutupdate%'

OR `frontend_input` LIKE '%stomlayoutupdate%'

OR `frontend_label` LIKE '%stomlayoutupdate%'

OR `frontend_class` LIKE '%stomlayoutupdate%'

OR `source_model` LIKE '%stomlayoutupdate%'

OR `is_required` LIKE '%stomlayoutupdate%'

OR `is_user_defined` LIKE '%stomlayoutupdate%'

OR `default_value` LIKE '%stomlayoutupdate%'

OR `is_unique` LIKE '%stomlayoutupdate%'

OR `note` LIKE '%stomlayoutupdate%'

);

Remember the attribute_ids from that query and run the following query:

DELETE
  FROM eav_attribute
WHERE attribute_id IN (attribute_ids from first query);

DELETE

FROM eav_attribute

WHERE attribute_id IN (attribute_ids from first query);

Done!

Show solution

Getting the status of every product in the database using MySQL (Magento 1)

Mex 2014/09/30 Magento Solutions

The following MySQL query returns the status of every product in the Magento database.

SELECT
  cpe.sku,
  q.product_status
FROM catalog_product_entity cpe
  JOIN (SELECT
    entity_id AS product_id,
    value AS product_status
  FROM catalog_product_entity_int
  WHERE entity_type_id = (SELECT
    entity_type_id
  FROM eav_entity_type
  WHERE entity_type_code = 'catalog_product')
  AND attribute_id = (SELECT
    attribute_id
  FROM eav_attribute
  WHERE attribute_code = 'status'
  AND entity_type_id = (SELECT
    entity_type_id
  FROM eav_entity_type
  WHERE entity_type_code = 'catalog_product'))) q
    ON cpe.entity_id = q.product_id;

SELECT

cpe.sku,

q.product_status

FROM catalog_product_entity cpe

JOIN (SELECT

entity_id AS product_id,

value AS product_status

FROM catalog_product_entity_int

WHERE entity_type_id = (SELECT

entity_type_id

FROM eav_entity_type

WHERE entity_type_code = 'catalog_product')

AND attribute_id = (SELECT

attribute_id

FROM eav_attribute

WHERE attribute_code = 'status'

AND entity_type_id = (SELECT

entity_type_id

FROM eav_entity_type

WHERE entity_type_code = 'catalog_product'))) q

ON cpe.entity_id = q.product_id;

Status 1: active Status 2: not active

Show solution

Calling a stored procedure from Magento

Mex 2014/06/16 Magento Solutions

Using the code below you can call a stored procedure directly from Magento.

$resource = Mage::getSingleton('core/resource');
$readConnection = $resource->getConnection('core_read');
$query = 'CALL stored_procedure()';
$results = $readConnection->exec($query);

$resource = Mage::getSingleton('core/resource');

$readConnection = $resource->getConnection('core_read');

$query = 'CALL stored_procedure()';

$results = $readConnection->exec($query);

Show solution

Getting orders with items that have a certain attribute set (Magento 1)

Mex 2014/03/03 Magento Solutions

The following MySQL query returns all Magento orders that have at least one item with attribute set id 9.

/* 9 = attribute set Id */
select
	increment_id,
	created_at,
	entity_id 
from
	sales_flat_order 
where
	entity_id not in (
		SELECT
			o.entity_id      
		FROM
			sales_flat_order o      
		LEFT JOIN
			sales_flat_order_item i        
				ON o.entity_id = i.order_id      
		LEFT JOIN
			catalog_product_entity p        
				ON i.product_id = p.entity_id      
		WHERE
			p.attribute_set_id = 9
	)

/* 9 = attribute set Id */

select

increment_id,

created_at,

entity_id

from

sales_flat_order

where

entity_id not in (

SELECT

o.entity_id

FROM

sales_flat_order o

LEFT JOIN

sales_flat_order_item i

ON o.entity_id = i.order_id

LEFT JOIN

catalog_product_entity p

ON i.product_id = p.entity_id

WHERE

p.attribute_set_id = 9

)

Show solution

Deleting all items from the compare list in the database (Magento 1)

Mex 2014/02/26 Magento Solutions

The following MySQL query deletes all rows from the Magento compare list table.

delete from catalog_compare_item;

1	delete from catalog_compare_item;

Show solution

Getting the SQL of a collection (Magento 1)

Mex 2013/05/16 Magento Solutions

Use the following code to get the underlying SQL of a Magento collection:

$sql = (string)$collection->getSelect();

1	$sql = (string)$collection->getSelect();

Show solution

Deleting upsell products directly in the database (Magento 1)

Mex 2013/04/11 Magento Solutions

Once saved, upsell products can not be removed again easily. Using the following MySQL script, you can delete Magento upsell products from the database.

DELETE link.* FROM catalog_product_link AS link
LEFT JOIN catalog_product_link_type AS linktype ON (linktype.link_type_id = link.link_type_id)
WHERE linktype.code = "up_sell" and product_id = 123;

DELETE link.* FROM catalog_product_link AS link

LEFT JOIN catalog_product_link_type AS linktype ON (linktype.link_type_id = link.link_type_id)

WHERE linktype.code = "up_sell" and product_id = 123;

Show solution

★★★ The Magento Expert ★★★

Magento Professional with 27080 hours project experience

Deleting all products using SQL (Magento 1)

Getting products with or without custom options using SQL (Magento 1)

Solving the “Invalid backend model specified: catalog/attribute_backend_customlayoutupdate” problem (Magento 1)

Getting the status of every product in the database using MySQL (Magento 1)

Calling a stored procedure from Magento

Getting orders with items that have a certain attribute set (Magento 1)

Deleting all items from the compare list in the database (Magento 1)

Getting the SQL of a collection (Magento 1)

Deleting upsell products directly in the database (Magento 1)