0

I am writing a cron job to update products, I cannot use SKU as that is magento only. For every product in magento I have an attribute auto_cool_number I need to update it by that value (which is varchar).

I found this script on magepsycho which is awesome, it works great and updates my price and qty based on SKU, how can I modify the query to inner jOIN attribute values and update all products which share that attribute value.

     function _updateStocks($data){
         $connection     = _getConnection('core_write');
         $sku            = $data[0];
         $newQty         = $data[1];
         $productId      = _getIdFromSku($sku);
         $attributeId    = _getAttributeId();

         $sql            = "UPDATE " . _getTableName('cataloginventory_stock_item') . " csi,
                            " . _getTableName('cataloginventory_stock_status') . " css
                            SET
                            csi.qty = ?,
                            csi.is_in_stock = ?,
                            css.qty = ?,
                            css.stock_status = ?
                            WHERE
                            csi.product_id = ?
                            AND csi.product_id = css.product_id";
         $isInStock      = $newQty > 0 ? 1 : 0;
         $stockStatus    = $newQty > 0 ? 1 : 0;
         $connection->query($sql, array($newQty, $isInStock, $newQty, $stockStatus, $productId)); }

     function _updatePrices($data){
         $connection     = _getConnection('core_write');
         $sku            = $data[0];
         $newPrice       = $data[2];
         $productId      = _getIdFromSku($sku);
         $attributeId    = _getAttributeId();

         $sql = "UPDATE " . _getTableName('catalog_product_entity_decimal') . " cped
                     SET  cped.value = ?
                 WHERE  cped.attribute_id = ?
                 AND cped.entity_id = ?";
         $connection->query($sql, array($newPrice, $attributeId, $productId)); }

~

  • So your input does not have sku, but another code, which you have stored in a Magento attribute "auto_cool_number"? – iphigenie Jul 15 '16 at 8:05
0

(note that the script you are using looks like it will only work for single store setups so I won't worry about storeid etc.)

If I have understood correctly, what you need is not really a join. (A multi table join would be a tad messy, though if your attribute is in the flat table then it would be a reasonable join)

Best to just get the ID correctly before the SQL.

You need to replace this line

$productId      = _getIdFromSku($sku);

so that you get the ID from a search based on the attribute. To make it clearer I'd change all three lines.

     $auto_cool_number = $data[0];
     $newQty           = $data[1];
     $productId        = getIdFromCool($auto_cool_number);

and again later

     $auto_cool_number = $data[0];
     $newPrice         = $data[2];
     $productId        = getIdFromCool($auto_cool_number);

Next we need to write the getIdFromCool - there is the completely clean general approach, building a collection and fetching one product, but depending on your setup there are performance shortcuts you can use (is it in flat table?) and there might be some pitfalls (are you using configurable? options?). I am assuming that auto_cool_number is unique which means that searching for a product by auto_cool_number will always return just one.

function getIdFromCool($my_auto_cool_number) {

      $collection = Mage::getModel('catalog/product')->getCollection();
      $collection->addAttributeToFilter('auto_cool_number', array('eq' => $my_auto_cool_number));       
      return array_pop($collection->getAllIds());
     // if multiple: return implode(',',$collection->getAllIds());

}

This ought to be given a little bit of error handling just in case, but hopefully this gets you started

EDIT: if multiple results with the same cool_number, return the full list from the subroutine

function getIdFromCool($my_auto_cool_number) {

      $collection = Mage::getModel('catalog/product')->getCollection();
      $collection->addAttributeToFilter('auto_cool_number', array('eq' => $my_auto_cool_number));       
      return implode(',',$collection->getAllIds());

}

and change your sql to be "where csi.product_id in ( $ ) "

  • Thank for for responding, I almost got it working myself by changing these lines $sql = "SELECT COUNT(*) AS count_no FROM " . _getTableName('catalog_product_entity_varchar') . " WHERE value = ?"; $sql = "SELECT entity_id FROM " . _getTableName('catalog_product_entity_varchar') . " WHERE value = ?"; in the _getIdFromSku and _checkIfSkuExists functions – Justin Blayney Jul 18 '16 at 19:02
  • the problem is that i have multiple entries of these auto_cool_numbers (otherwise I would have made them my Magento SKU and been done with it), So my modifications work but only update 1 product with that auto_cool_number attribute value, I could have 1 or 100 products with that value, they all need to be updated – Justin Blayney Jul 18 '16 at 19:04
  • function _checkIfSkuExists($sku){ $connection = _getConnection('core_read'); $sql = "SELECT COUNT(*) AS count_no FROM " . _getTableName('catalog_product_entity_varchar') . " WHERE value = ?"; $count = $connection->fetchOne($sql, array($sku)); if($count > 0){ return true; }else{ return false; } } – Justin Blayney Jul 18 '16 at 19:08
  • function _getIdFromSku($sku){ $connection = _getConnection('core_read'); $sql = "SELECT entity_id FROM " . _getTableName('catalog_product_entity_varchar') . " WHERE value = ?"; return $connection->fetchOne($sql, array($sku)); } – Justin Blayney Jul 18 '16 at 19:08
  • you should be able to tweak this for multiple with the same id @JustinBlayney 1) return the full contents of getAllIds 2) change your SQL condition to be csi.product_id in (the list of IDs returned) I can update the answer, but if you found another way I'm not sure it adds any value – iphigenie Jul 19 '16 at 16:02
-1

ok, i figured it out...

I disabled the _getIdFromSku function and added this loop within both the update price and update stock function (if someone could find a way to write it into a function i would be open to it)

function _updateStocks($data){ $connection = _getConnection('core_write'); $sku = $data[0]; $newQty = $data[1]; //$productId = _getIdFromSku($sku); $attributeId = _getAttributeId();

$sql_prod        = "SELECT entity_id FROM " . _getTableName('catalog_product_entity_varchar') . " WHERE value = $sku";
$sqlQuery = $connection->query($sql_prod);
while ($row = $sqlQuery->fetch() ) {
$entity_id = $row['entity_id'];

$sql            = "UPDATE " . _getTableName('cataloginventory_stock_item') . " csi,
                   " . _getTableName('cataloginventory_stock_status') . " css
                   SET
                   csi.qty = ?,
                   csi.is_in_stock = ?,
                   css.qty = ?,
                   css.stock_status = ?
                   WHERE
                   csi.product_id = ?
                   AND csi.product_id = css.product_id";
$isInStock      = $newQty > 0 ? 1 : 0;
$stockStatus    = $newQty > 0 ? 1 : 0;
$connection->query($sql, array($newQty, $isInStock, $newQty, $stockStatus, $entity_id));


}

}

function _updatePrices($data){ $connection = _getConnection('core_write'); $sku = $data[0]; $newPrice = $data[2]; //$productId = _getIdFromSku($sku); $attributeId = _getAttributeId();

$sql_prod        = "SELECT entity_id FROM " . _getTableName('catalog_product_entity_varchar') . " WHERE value = $sku";
$sqlQuery = $connection->query($sql_prod);
while ($row = $sqlQuery->fetch() ) {
$entity_id = $row['entity_id'];

$sql = "UPDATE " . _getTableName('catalog_product_entity_decimal') . " cped
            SET  cped.value = ?
        WHERE  cped.attribute_id = ?
        AND cped.entity_id = ?";
$connection->query($sql, array($newPrice, $attributeId, $entity_id));


}

}

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.