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`

2 Answers 2

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();
3
  • Thanks, but Such a collection request will return 18000 results. I can not use it. Jul 7, 2018 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, 2018 at 13:26
  • This not work in magento 2.2.3 Jul 9, 2018 at 7:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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