I'm trying to pull a list of all products that are not in categories via php-myadmin, is this possible? What is the best way to go about doing this?
1 Answer
This SQL query will give you a list of all "uncategorized" skus:
select sku
from catalog_product_entity as e
left join catalog_category_product as cat
on e.entity_id = cat.product_id where category_id is null
EDIT: If you want to fetch more data, you need to pick it up from proper EAV tables, here is a basic sample demonstrating the principle:
set @name_attribute_id = (SELECT attribute_id FROM `eav_attribute` INNER JOIN `eav_entity_type` ON entity_type_code = 'catalog_product' AND eav_entity_type.entity_type_id = `eav_attribute`.entity_type_id where attribute_code in ('name'));
select sku, created_at, updated_at, name_table.value as product_name
from catalog_product_entity as e
left join catalog_category_product as cat
on e.entity_id = cat.product_id
left join catalog_product_entity_varchar as name_table
on e.entity_id = name_table.entity_id and name_table.attribute_id = @name_attribute_id
where category_id is null
-
thanks so much! This indeed worked great! If I also wanted to pull up the products name, date created, and date of last updated what would I have to add? Oct 9, 2013 at 16:48
-
Check out my updated answer. This is real magento-sql-geek stuff :P Oct 9, 2013 at 17:01
-
you are amazing! Thank you! Excuse my "n00bness" but when I entered in your entire snippet nothing was happening. When I entered in the code starting at "select sku" (from the second code snippet you pasted) it brought up a list of everything correctly except for the product names come up "NULL". Am I doing something wrong? Oct 9, 2013 at 17:12
-
Looks like your phpMyAdmin configuration doesn't allow multiple queries per request so we can't make use of variable \@name_attribute_id. A workaround is: 1) run first query without "set \@name_attribute_id =", just select 2) figure what your "name" attribute id 3) run your second query with substituting @name_attribute_id by the real attribute_id Oct 9, 2013 at 17:31
-
Thanks so if my name attribute was 71 I would run something like: set 71 = (SELECT attribute_id FROM
eav_attribute
INNER JOINeav_entity_type
ON entity_type_code = 'catalog_product' AND eav_entity_type.entity_type_id =eav_attribute
.entity_type_id where attribute_code in ('name')); Oct 9, 2013 at 18:00