Magento – How to use Union query in a Magento 2 collection

collection;magento2uicomponentzend-framework

I am working on Magento 2 grid UI component. For that I am generating grid using multiple table which does not have any relation( so I cant join). I am using union to combine the tables. But while trying to use it giving following error.error image

This is my code:

protected function _initSelect()
{
    parent::_initSelect();        
    $this->getSelect()->reset(\Zend_Db_Select::COLUMNS)->columns(['created_at'=>'created_at']);

    $sqlq=clone $this->getSelect();
    $sqlT=$this->getSelect()->reset()->from('procurement_owned', ['created_at'=>'created_at']);                                                      
    $sql2= clone $sqlT;     
    $this->getSelect()->reset()->union([$sqlq,$sql2], \Zend_Db_Select::SQL_UNION_ALL);

    return $this;
}

Then I printed the sql query run that directly into phpmyadmin its giving expected output by combining two tables.

I debugged little and found following line form Zend_Db_Select class throwing the error

public function columns($cols = '*', $correlationName = null)
{
    if ($correlationName === null && count($this->_parts[self::FROM]))  {
        $correlationNameKeys = array_keys($this->_parts[self::FROM]);
        $correlationName = current($correlationNameKeys);
    }

    if (!array_key_exists($correlationName, $this->_parts[self::FROM]){
        /**
        * @see Zend_Db_Select_Exception
        */
        #require_once 'Zend/Db/Select/Exception.php';
        throw new Zend_Db_Select_Exception("No table has been specified for the FROM clause");
    }
    $this->_tableCols($correlationName, $cols);

    return $this;
}

i don't know what cause the error. please help me. Thanks

Best Answer

This is problem with getting count form the union query

i referred the following link and fixed the issue.

https://stackoverflow.com/questions/11731655/how-to-do-a-count-on-a-union-query

https://framework.zend.com/issues/browse/ZF-5956

Then i overridden the getSelectCountSql from Magento\Framework\Data\Collection\AbstractDb to get count from union query

  public function getSelectCountSql()
    {
        $this->_renderFilters();
        $unionSelect = clone $this->getSelect(); 
        $countSelect = clone $this->getSelect(); 
        $countSelect->reset()->from($unionSelect,'count(*)');

      return $countSelect;
    }

Hope someone can use this method.

Related Topic