SQL | The Magento Expert

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

8. Januar 2015 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…

View solution

Fixing Invalid backend model specified: catalog/attribute_backend_customlayoutupdate (Magento 1)

Mex 8. Januar 2015 Magento Solutions

First, run this 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%'

);

Write down the attribute_ids returned by the query, then run the following query:

DELETE
  FROM eav_attribute
WHERE attribute_id IN (attribute_ids aus erster Abfrage);

DELETE

FROM eav_attribute

WHERE attribute_id IN (attribute_ids aus erster Abfrage);

Fertig!

View solution

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

Mex 30. September 2014 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

View solution

Calling a stored procedure from Magento

Mex 16. Juni 2014 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);

View solution

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

Mex 3. März 2014 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

)

View solution

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

Mex 26. Februar 2014 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;

View solution

Getting the SQL of a collection (Magento 1)

Mex 16. Mai 2013 Magento Solutions

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

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

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

View solution

Deleting upsell products directly in the database (Magento 1)

Mex 11. April 2013 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;

View solution

Changing customer email addresses in Magento

Mex 29. März 2013 Magento Solutions

When testing new features locally on your development machine, it is advisable to change the customers’ email addresses. You can use the following script for this purpose: Warning: Never execute these statements on a production system!

update sales_flat_quote set customer_email = (concat(substring(md5(rand()) from 1 for 20), '@example.com'));
update sales_flat_order set customer_email = (concat(substring(md5(rand()) from 1 for 20), '@example.com'));
update sales_flat_quote_address set email = (concat(substring(md5(rand()) from 1 for 20), '@example.com'));
update customer_entity set email = (concat(substring(md5(rand()) from 1 for 20), '@example.com'));

update sales_flat_quote set customer_email = (concat(substring(md5(rand()) from 1 for 20), '@example.com'));

update sales_flat_order set customer_email = (concat(substring(md5(rand()) from 1 for 20), '@example.com'));

update sales_flat_quote_address set email = (concat(substring(md5(rand()) from 1 for 20), '@example.com'));

update customer_entity set email = (concat(substring(md5(rand()) from 1 for 20), '@example.com'));

Now open an…

View solution

Changing Magento store email addresses in a local installation

Mex 25. März 2013 Magento Solutions

Warning: Never execute these statements on a production system! The following query generates an update script according to a pattern you define. In this example the pattern is “your-domain.com”. Replace it with your own domain.

select concat('update core_config_data set value = \'test@example.com\' where path = \'', path, '\';')
  from core_config_data where value like '%@ihre-domain.de%'

1 2	select concat('update core_config_data set value = \'test@example.com\' where path = \'', path, '\';') from core_config_data where value like '%@ihre-domain.de%'

The resulting script might…

View solution