0

How to convert this query in magento style

    `SELECT MAX(s.num) FROM (
       SELECT CONVERT(SUBSTRING_INDEX(sku,'-',-1),UNSIGNED INTEGER) AS num 
       FROM catalog_product_entity 
       WHERE (sku REGEXP "^[0-9]+$")
    ) as s` 

If you offer a good article that will help me, then I will take it as an answer.

UPDATE

I write this query :

    $select = $connection->select()
                ->from(
                    $connection->select()->from(
                    'catalog_product_entity',
                    ['num' => new \Zend_Db_Expr('SELECT CONVERT(SUBSTRING_INDEX(sku,\'-\',-1),UNSIGNED INTEGER')]
                )->where(
                    $connection->quoteIdentifier('sku') . ' REGEXP :pattern'
                ),
                    [ new \Zend_Db_Expr('max(t.num)')]

        );
            print_r($select->__toString()); exit();

But select duplicated

SELECT max(t.num) FROM (
   SELECT SELECT CONVERT(SUBSTRING_INDEX(sku,'-',-1),UNSIGNED INTEGER AS `num` 
   FROM `catalog_product_entity` WHERE (`sku` REGEXP :pattern)
) AS `t`
0

Solution :

$select = $connection->select()
                ->from(
                    $connection->select()->from(
                    'catalog_product_entity',
                    ['num' => new \Zend_Db_Expr('CONVERT(SUBSTRING_INDEX(sku,\'-\',-1),UNSIGNED INTEGER)')]
                )->where(
                    $connection->quoteIdentifier('sku') . ' REGEXP :pattern'
                ),
                    [ new \Zend_Db_Expr('max(t.num)')]

        );
0

I simplified your select query which results the same output.

SELECT MAX(CONVERT(SUBSTRING_INDEX(sku,'-',-1),UNSIGNED INTEGER)) AS num 
FROM catalog_product_entity 
WHERE (sku REGEXP "^[0-9]+$")

Here is the equivalent magento code.

$collection = Mage::getModel('catalog/product')->getCollection()
    ->addAttributeToFilter('sku', array("regexp" => "^[0-9]+$")); 
/* Filters catalog_product_entity based on sku matching the required regex pattern */

$collection->getSelect()
    ->reset(Zend_Db_Select::COLUMNS);


$collection->addExpressionAttributeToSelect('num',
        new Zend_Db_Expr('MAX(CONVERT(SUBSTRING_INDEX(sku,"-",-1),UNSIGNED INTEGER))'),
        []);/* Includes the required expression on sku column and results in the field num */


/* To cross check the query that executes behind this $collection,
you may echo $collection->getSelect()->assemble() which outputs the query that executes behind it. */

/* Final Result - num value */
$finalResult =  $collection->getFirstItem()->getNum();
  • Thanks, but Such a collection request will return 18000 results. I can not use it. – Evgeniy Kapelko Jul 7 '18 at 8:38
  • If this is not a one time script/usage and are worried about performance issue, you may do it in single line of code. $finalResult=Mage::getModel('catalog/product')->getCollection()->addAttributeToFilter('sku', array("regexp" => "^[0-9]+$"))->addExpressionAttributeToSelect('num',new Zend_Db_Expr('MAX(CONVERT(SUBSTRING_INDEX(sku,"-",-1),UNSIGNED INTEGER))'),[])->getFirstItem()->getNum(); I can update the answer if this code works for you. – nikin Jul 7 '18 at 13:26
  • This not work in magento 2.2.3 – Evgeniy Kapelko Jul 9 '18 at 7:48

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.