4

I have a table represented in backend with two rendered fields, but when I try to make a CSV export from this grid, those two fields are not included.

So I tried to make a join from the initial collection but I would have to make a join with a query / select like this:

SELECT main_table.*, art.artworks_uploaded
FROM mage_artist_details AS main_table
    LEFT JOIN (
        SELECT artist_id, COUNT(*) AS artworks_uploaded
        FROM mage_art_works
        GROUP BY artist_id
    ) AS art
    ON main_table.user_id = art.artist_id

Any ideas of how can I obtain this?

If I try and use directly this query for my collection, I lose pagination in grid.

If I try and replace a table with a query in the next line I get errors:

$collection->getSelect()->join(
    array('art' => 'mage_art_works'),
    'main_table.user_id = art.artist_id',
    array('artworks_uploaded' => 'COUNT(*)')
)
4

Create Virtual Query

Create ZEND SQL query which is give you artworks_uploaded basic of artist_id,which give an virtual result query with count and artist id.

$ReadAdapter=Mage::getSingleton('core/resource')->getConnection('core_read');
        $select = $ReadAdapter()->select()
            ->from('mage_art_works', array())
            ->columns(array('artist_id',
    'artworks_uploaded' => new Zend_Db_Expr('COUNT(*)'))
    )->group('mage_art_works.artist_id');

Alias table using above result:

then add the below ZEND SQL to the alias table

$collection->getSelect()
    ->joinLeft(
        array('art' => new Zend_Db_Expr(" ( $select ) ")),
        'main_table.user_id = art.artist_id',
        array()
    );
  • 1
    $ReadAdapter = Mage::getSingleton('core/resource')->getConnection('core_write'); $select = $ReadAdapter->select() ->from('mage_art_works', array()) ->columns(array( 'artist_id', 'artworks_uploaded' => new Zend_Db_Expr('COUNT(*)') )) ->group('mage_art_works.artist_id'); – Dan Ovidiu Boncut Apr 6 '15 at 12:06
  • $collection->getSelect() ->joinLeft( array('art' => $select),//new Zend_Db_Expr($ReadAdapter)), 'main_table.user_id = art.artist_id', array('artworks_uploaded') ); – Dan Ovidiu Boncut Apr 6 '15 at 12:06
  • there were some errors and in my comments is the final solution. if you edit your answer, I will gladly accept it – Dan Ovidiu Boncut Apr 6 '15 at 12:08
  • 1
    It would be fair for everyone to get a bug free solution – Dan Ovidiu Boncut Apr 6 '15 at 12:09
  • 1
    it will work only when you add parentheses for the $select : new Zend_Db_Expr(" ( $select ) ") – Mohamed23gharbi May 13 '17 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.