2

Right now I have this code

public static $table = 'customer_entity';
public static $leftJoinTable = 'customer_address_entity';

public function afterSearch($intercepter, $collection)
{
    if ($collection->getMainTable() === $collection->getConnection()->getTableName(self::$table)) {
        $leftJoinTableName = $collection->getConnection()->getTableName(self::$leftJoinTable);

        $collection
            ->getSelect()                               
            ->joinLeft(
                ['co'=>$leftJoinTableName],
                "co.entity_id = main_table.default_shipping",
                [
                    'shipping_city' => 'co.city'
                ]
            );

        $where = $collection->getSelect()->getPart(\Magento\Framework\DB\Select::WHERE);
        $collection->getSelect()->setPart(\Magento\Framework\DB\Select::WHERE, $where);
        echo $collection->getSelect()->__toString();die;
    }
    return $collection;
}

And in turn it produces this query when I do a filter

SELECT `main_table`.*, `co`.`city` AS `shipping_city` FROM `customer_entity` AS `main_table`
LEFT JOIN `customer_address_entity` AS `co` ON co.entity_id = main_table.default_shipping
WHERE `shipping_city` LIKE '%HIGHBRIDGE%'

But this produces an error as shipping_city is unknown. But is there a way so that this code will produce an output something like below

select * from (SELECT `main_table`.*, `co`.`city` AS `shipping_city` FROM `customer_entity` AS `main_table`
LEFT JOIN `customer_address_entity` AS `co` ON co.entity_id = main_table.default_shipping ) as a WHERE (`shipping_city` LIKE '%HIGHBRIDGE%')

So basically it just wraps the first query and then do a second query. The top level query is where the WHERE filter be added. If this is achieved it will also solve the unknown column error. Is there a way to do that?

1 Answer 1

1

You can write the sub query like this

if ($collection->getMainTable() === $collection->getConnection()->getTableName(self::$table)) {
      $leftJoinTableName = $collection->getConnection()->getTableName(self::$leftJoinTable);

      $subquery = $collection
            ->getSelect()
            ->joinLeft(
                ['co'=>$leftJoinTableName],
                "co.entity_id = e.default_shipping",
                [
                    'shipping_city' => 'co.city'
                ]
            );
        $con = $collection->getConnection();
        $query = $con->select()->from(
            [
                'main_table' => new \Zend_Db_Expr(
                    '('. $subquery .')'
                )
            ]
        )->where(
            "shipping_city like '%HIGHBRIDGE%'"
        );
      echo $query;die;
}
   
1
  • 1
    This really solves it.. been looking for this code.. Really appreciate it Jul 3, 2020 at 8:01

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.