In my magento i have "Display out of stock products" -> No. (i needed like this).
I want to create a page with all out of stock products.
I have everything working, but cannot find the correct collection.
The products that must be included are: simple products that are out of stock AND configurable products with all of their simple out of stock.
Update Edit 1 There is one more important note. The solution must use query and not a new collection.
e.g this is the current code for filtering on "bestseller"
$select->joinLeft(array('stats' => new Zend_Db_Expr("(SELECT stats.product_id AS product_id, SUM(stats.qty_ordered - IFNULL(stats.qty_canceled, 0)) AS qty_ordered".
" FROM {$res->getTableName('sales/order_item')} AS stats".
" INNER JOIN {$res->getTableName('sales/order')} AS o ON".
" (o.entity_id = stats.order_id AND".
" (o.created_at BETWEEN '{$from}' AND '{$to}')) AND o.state NOT IN ('pending_payment', 'new', 'canceled') AND".
$db->quoteInto(" o.store_id = ?", Mage::app()->getStore()->getId()).
" GROUP BY stats.product_id)")),
"stats.product_id = e.entity_id", array('cat_index_position' => new Zend_Db_Expr('-stats.qty_ordered')));
$this->_condition = 'stats.qty_ordered > 0';