子查询成为Magento中Mysql查询中的列

Question

I have a query in Magento ($Collection) in which I joined a table to have the result I wanted.

This is my $Collection

protected function _prepareCollection()
{
    $collection = Mage::getResourceModel($this->_getCollectionClass());
    $stmp = "(SELECT GROUP_CONCAT(comment SEPARATOR '----' ) FROM mgmx_sales_flat_invoice_comment a WHERE a.parent_id = `main_table`.`entity_id` group by parent_id)";
    $collection->getSelect()->join( array('a'=> mgmx_sales_flat_invoice_comment), 'a.parent_id = main_table.entity_id', $stmp);


    $this->setCollection($collection);
    echo $collection->getSelect();die();
    return parent::_prepareCollection();
}

This $Collection, if you notice I echoed it. It will print this Query.

SELECT 
     `main_table`.*, 
           (SELECT 
                  GROUP_CONCAT
                  (comment SEPARATOR '----' ) 
            FROM 
                  mgmx_sales_flat_invoice_comment a 
            WHERE 
                  a.parent_id = main_table`.`entity_id 
            GROUP BY 
                  parent_id)
  FROM 
      `mgmx_sales_flat_invoice_grid` 
   AS 
      `main_table` 
   INNER JOIN 
       `mgmx_sales_flat_invoice_comment` 
   AS 
       `a` 
    ON 
         a.parent_id = main_table.entity_id

This Query, on the other hand is working if I can remove some of the "things" in it like '`' so it will function normally.

Here's what will happen Step by Step.

1. Upon running the query in Mysql it will return an error

1054 - Unknown column '(SELECT GROUP_CONCAT(comment SEPARATOR '----' ) FROM mgmx_sales_flat_invoice_comment a WHERE a.parent_id = main_table.entity_id group by parent_id)' in 'field list'

if I remove the `` before and after the (SELECT statement) like the one below

SELECT `main_table`.*, 
    (SELECT 
            GROUP_CONCAT(comment SEPARATOR '----' ) 
     FROM 
            mgmx_sales_flat_invoice_comment a
     WHERE a.parent_id = main_table`.`entity_id 
     group by parent_id) 
FROM `mgmx_sales_flat_invoice_grid` 
AS `main_table`
INNER JOIN `mgmx_sales_flat_invoice_comment` 
AS `a` ON a.parent_id = main_table.entity_id

It will show this error instead

right syntax to use near '.entity_id group by parent_id) FROM mgmx_sales_flat_invoice_grid AS `main_ta' at line 1

because of this

WHERE a.parent_id = main_table`.`entity_id 

how do I remove that `` and how do I group by the final $collection Query.

Answer 1

Here the separator ---- in GROUP_CONCAT function is creating an issue.

If you use a different separator like ==== then the issue will be resolved.

Like,

$stmp = "(SELECT GROUP_CONCAT(comment SEPARATOR '====' ) FROM mgmx_sales_flat_invoice_comment a WHERE a.parent_id = `main_table`.`entity_id` group by parent_id)";

Hope this helps!