Magento Question posted in
The official documentation can be found here.

Select Query with Multiple Rows in Join Table in collection in Magento

How Do I do Select Statement in _preparecollection in Magento if the table I joined in the Main table has 2 rows with 1 parent ID.

Tables I have now.

Table 1(Main Table)
enter image description here

Table 2(sales_flat_invoice_comment)
enter image description here

My Current Prepare Collection

$collection = Mage::getResourceModel($this->_getCollectionClass());
$collection->getSelect()->join( array('a'=> mgmx_sales_flat_invoice_comment), 'a.parent_id = main_table.entity_id', array('a.comment'));
$this->setCollection($collection);
return parent::_prepareCollection();

This query, in echoed will be like this

SELECT main_table.*, a.comment
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

But it will return an error if this query finds more than 1 row in table 2.

What I want is for something like the one below

enter image description here

With | as a delimiter.

How Can I achieved this in the _prepareCollection of Magento.

Tags:

2

Answers


  1. 0

    you have to group by the entity_id and then use group_concat to create your comment column. You can define a separator in the group by.

    The concatenated column has a limit in length. So depending on the length of your single comments and the number of comments it might happen that you do not get all of them in the result.

    I hope this helps with solving your problem.

    Login or Signup to reply.
  2. 0

    To get the group_concat loading in the zend framework you can define it using the Zend_Db_Expr object

    Something like

    $collection->getSelect()->join(
    array('a'=> new Zend_Db_Expr('GROUP_CONCAT(mgmx_sales_flat_invoice_comment)')), 
    'a.parent_id = main_table.entity_id', 
    array('a.comment')
);

    Which is handy to know about whenever you need to do custom database functions inside the zend framework.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search