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

user_7423779 0
Viewed 1820

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.

magento zend-framework join mysql php
2 Answers

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.

user_39374740

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.

user_12752250
Related