2

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

3
  • Is there any specific table from the first $this->getSelect()->reset? Dec 6, 2016 at 16:21
  • Yes. but am resetting the columns alone. Dec 6, 2016 at 18:33
  • Can you share me the full code please, even I am having the same problem.
    – Amy
    Jan 22, 2019 at 9:42

2 Answers 2

3

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.

1
  • I was implementing a custom filter in the new (2.3.x) order grid to optionally include archived orders. I decided to utilize a UNION and ran into this same issue - thank you very much 🙏🏼
    – Logan
    Jan 17, 2020 at 4:42
0

I debug and got the solution, use union select to collection.

$collection = $this->_initCollection();
$unionSelect = $this->_initCollection();
$unionSelect->setMainTable('magento_sales_order_grid_archive');

$newest = clone $collection->getSelect()->union([(string)$collection->getSelect(), (string)$unionSelect->getSelect()]);
//Remove from part fixing the issue sql systax FROM.
$newest->reset('from');

//Reset the select object for updating the query.
$collection->getSelect()->reset();
// Set the union query to main select object as main table.
$collection->getSelect()->from(['main_table' => new \Zend_Db_Expr('(' . (string)$newest . ')')]);

return $collection;

The result

SELECT `main_table`.* 
FROM (
      SELECT `main_table`.`entity_id`, `main_table`.`increment_id`, `main_table`.`customer_id`, `main_table`.`created_at` FROM `sales_order_grid` AS `main_table` LEFT JOIN `sales_order` ON main_table.entity_id = sales_order.entity_id LEFT JOIN `forix_syspro_syspro` AS `a` ON main_table.entity_id = a.magento_order_id LEFT JOIN `shipperhq_order_detail_grid` AS `shipper_order_join` ON main_table.entity_id = shipper_order_join.order_id WHERE (`main_table`.`customer_id` = '534700') 
UNION 
     SELECT `main_table`.`entity_id`, `main_table`.`increment_id`, `main_table`.`customer_id`, `main_table`.`created_at` FROM `magento_sales_order_grid_archive` AS `main_table` LEFT JOIN `sales_order` ON main_table.entity_id = sales_order.entity_id LEFT JOIN `forix_syspro_syspro` AS `a` ON main_table.entity_id = a.magento_order_id LEFT JOIN `shipperhq_order_detail_grid` AS `shipper_order_join` ON main_table.entity_id = shipper_order_join.order_id WHERE (`main_table`.`customer_id` = '534700')
) 
AS `main_table` 
9
  • Have you faced any issues related to Full-text search when using this? any solution for that? Oct 19, 2023 at 6:33
  • This union for sales order grid, Full-text search is a different concept.
    – HoangHieu
    Oct 19, 2023 at 9:37
  • Actually I used sales order grid and other table which does not have any related and used your method to join them and everything well, but problem is when I try tosearch from fulltext search it's giving error like "The used table type doesn't support FULLTEXT indexes" tinyurl.com/2x9hcqtt Oct 19, 2023 at 10:23
  • Look like you have to debug the SQL query, exec the query on MySQL let see what is error. How to fix with that query?
    – HoangHieu
    Oct 19, 2023 at 10:27
  • when you search using fulltext, default Magento uses fulltext search based on sales_order_grid table but on the other hand in the second table I don't have those fulltext indexes nor those fields that's why it's throwing the error.. I am searching for the solution but no luck till now.. Oct 19, 2023 at 11:00

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.