0

is this possible? I can't restructure the tables over again every time I need to run a certain dependent function.

        $catSQL = "SELECT *
        FROM `catalog_product_entity_int`
        WHERE `attribute_id` =96 //our status attribute id
        AND `value` =2"; //disabled value

This gets all disabled products. However, this table is indexed by Id. I'm passed a number of sku's, how can I merge this table with something like catalog_product_entity and search by an array of skus? Essentially I need to get the Id of all disabled products and then check their quantities in the stock_item table.

Thanks to Marius below i've acomplished the above with the following:

function getDisabledIdsFromSkus($arraySkus){
        $conn = Mage::getSingleton('core/resource')->getConnection('core_write');

        $ComponentSkustring = $this->format($arraySkus);

        $anotherRoute = "SELECT k.`qty`
        FROM `catalog_product_entity` i
        LEFT JOIN catalog_product_entity_int e ON i.entity_id = e.entity_id
        RIGHT JOIN cataloginventory_stock_item k ON i.entity_id = k.item_id
        WHERE e.`attribute_id` =96
        AND e.`value` =2
        AND i.`sku` IN ($string)
        ORDER BY i.`sku` ASC ";

        $componentQuantities = ($conn->fetchAll($anotherRoute));
        Mage::log(print_r($componentQuantities, true));
            return;
}

private function format($array)
{
    // initialize variable
    $formattedArray = array();

    //for each element
    foreach ($array as $string) {
        //append quotes
        $formattedArray[] = "'" . $string . "'";
    }

    //convert formatted elements to string and return
    $formattedString = implode(',', $formattedArray);
    return $formattedString;
}
1

You cannot get the disabled products on frontend area when the flat tables are on.
The indexer that populates the flat tables takes into account only enabled products and associated to the websited being indexed.

[EDIT] Try with this select

SELECT *
FROM `catalog_product_entity_int` i
LEFT JOIN catalog_product_entity e ON e.entity_id = e.entity_id
WHERE i.`attribute_id` =96
AND i.`value` =2
AND e.sku IN ('sku1', 'sku2')
| improve this answer | |
  • I've updated my question, please take a look. – easymoden00b Mar 27 '15 at 12:54
  • Seems like it'll work, along with the sku array filtering, but I got a "Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 88 bytes)..." when I ran the above. – easymoden00b Mar 27 '15 at 13:11
  • well...you are running magento with 128M memory. The minimum required is 256M and the recommended value is 512M. – Marius Mar 27 '15 at 13:15
  • Ah, I see. How would I go about further filtering this by an array of skus? – easymoden00b Mar 27 '15 at 13:21
  • @easymoden00b. I've updated the query – Marius Mar 27 '15 at 13:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.