Collection getconnection Convert to multilevel query

Question

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?

Answer 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;
}