CODEWITHSUNDEEP
mysqlsqlmagento
Max
Max

Reputation: 8836

Uncategorized Products Query

I'm trying to get a list of uncategorized products in Magento. This is not necessarily a Magento question, but a general SQL question. I have a product table, and a category table, with a relation table called category_product. Every Product appears in two default categories (2 and 5). How would I construct a query (I can convert it into a Magento query) that returns all Products who don't have any categories other than 2 and 5?

Thanks!

Upvotes: 1

Views: 647

Answers (4)

Max
Max

Reputation: 8836

For those interested in how to do this in Magento for an Adminhtml grid, here's the _prepareCollection function (tested in 1.6.1.0). The thing I wrote in the question about categories 2 and 5 ended up being a wild goose chase.

protected function _prepareCollection()
{
    $collection = Mage::getModel('catalog/product')->getCollection()
        ->addAttributeToSelect('name')
        ->addAttributeToSelect('status') /* so i can filter by enabled/disabled */
        ->joinField( 'category_product'
                   , 'catalog_category_product'
                   , 'category_id'
                   , 'product_id = entity_id'
                   , null
                   , 'left'
                   )
        ;

    /*ideally this should be an 'addFieldToFilter' but it doesn't work. Oh well.*/
    $collection->getSelect()
        ->where('at_category_product.category_id IS NULL')
        ;

    $this->setCollection($collection);
    return parent::_prepareCollection();
}

Upvotes: 1

user359040
user359040

Reputation:

Try:

select p.product_id, p.name
from category_product cp
join product p on cp.product_id = p.product_id
group by cp.product_id
having sum(case when category_id in (2,5) then 0 else 1 end) = 0

Upvotes: 1

No'am Newman
No'am Newman

Reputation: 6477

Try the following query

select product.name
from product inner join category_product
on product.id = category_product.product
having count (*) < 2

I am assuming that the the only categories are indeed 2 and 5.

Upvotes: 0

Matt Fellows
Matt Fellows

Reputation: 6532

Not sure this is the most efficient, but it should work...

SELECT * from product p
INNER JOIN category_product cp on p.id = cp.product_id
WHERE cp.category_id IN (2, 5) AND cp.category_id NOT IN (SELECT category_id FROM category_product WHERE category_id NOT IN (2,5))

Upvotes: 0

Related Questions