SQL Query to List All Disabled Products in Magento

MySQLproductquery

I need to list all disabled products by using a MySQL query.

I have too many products in my database to be using something like this (creating a collection, loading it and looping over it): http://www.srikanth.me/get-all-disabled-products-on-magento/

Best Answer

As magento product folow the EAV structure that

You need to write a Query between eav_attribute and catalog_product_entity_int table

Magento save product status on table catalog_product_entity_int table. Save it as 1 and 2.

1 for enable
2 for disable.

You need to get status attribute id using attribute code status, basically it is 96.

Query:

SELECT entity_id FROM `catalog_product_entity_int`
WHERE attribute_id = (
    SELECT attribute_id FROM `eav_attribute`
    WHERE `attribute_code` LIKE 'status'
) AND `catalog_product_entity_int`.value = 2

Related Solutions

Magento – Magento product collection ignores custom sort order (order by FIELD)

The problem appears because of appendSummary call.
If you take a look inside the method you will see this:

    $entityIds = array();
    foreach ($collection->getItems() as $_itemId => $_item) {
        $entityIds[] = $_item->getEntityId();
    }

and when calling $collection->getItems() the collections is loaded from the db (sql is executed).

Everything you change in the collection object's sql after loading the collection is just like like my college degree....useless.
it has no effect on the collection itself.
even if the sql changed the items are already loaded and won't be loaded again.

You can try first to add your sorting and after that the appendSummary

Something like this:

...
$_productCollection->addAttributeToFilter('sku', array('in' => $this->_productskus));
$_productCollection->getSelect()->order(new Zend_Db_Expr('FIELD(e.sku, ' . "'" . implode("','", $this->_productskus) . "'".')'));
Mage::getModel('review/review')->appendSummary($_productCollection);

Magento – Magento aggregate collections

Doesn't seem that hard. Basically you would have to instantiate the customer collection, join it with the sales collections and apply some conditions. The sales table has most of the information you'll need except for the dob.

$collection = Mage::getResourceModel('customer/customer_collection')
        ->joinAttribute('dob','customer/dob', 'entity_id')
        ->join(array('orders'=>'sales_flat_order',
                     'orders.customer_id=main_table.entity_id', 
                     array('total_item_count','base_grand_total', 'customer_firstname', 'customer_lastname')))
        ->getSelect()
        ->reset(Zend_DB_Select::COLUMNS)
        ->columns(array(
                        'SUM(orders.total_item_count)'=>'qty',
                        'SUM(orders.base_grand_total)'=>'grand_total',
                        'orders.customer_firstname',
                        'orders.customer_lastname',
                        'dob'))
        ->group('main_table.entity_id')
        ->where('dob = SOMETHING');

I haven't tested it but this is how I would write it. Be sure to enable SQL logging in your Magento and adjust the collection according to the SQL result.

To format the dob as you wanted, I suggest using a renderer.

If you have any questions, feel free to ask.

Best Answer

Query:

Related Solutions

Magento – Magento product collection ignores custom sort order (order by FIELD)

Magento – Magento aggregate collections

Related Topic