dreamwind1985 2017-11-05 07:59
浏览 64
已采纳

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

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.

  • 写回答

1条回答 默认 最新

  • dsaaqdz6223 2017-11-05 10:51
    关注

    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!

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题