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
        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:

    array('art' => 'mage_art_works'),
    'main_table.user_id = art.artist_id',
    array('artworks_uploaded' => 'COUNT(*)')

1 Answer 1


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.

        $select = $ReadAdapter()->select()
            ->from('mage_art_works', array())
    'artworks_uploaded' => new Zend_Db_Expr('COUNT(*)'))

Alias table using above result:

then add the below ZEND SQL to the alias table

        array('art' => new Zend_Db_Expr(" ( $select ) ")),
        'main_table.user_id = art.artist_id',
  • 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'); Commented Apr 6, 2015 at 12:06
  • $collection->getSelect() ->joinLeft( array('art' => $select),//new Zend_Db_Expr($ReadAdapter)), 'main_table.user_id = art.artist_id', array('artworks_uploaded') ); Commented Apr 6, 2015 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 Commented Apr 6, 2015 at 12:08
  • 1
    It would be fair for everyone to get a bug free solution Commented Apr 6, 2015 at 12:09
  • 1
    it will work only when you add parentheses for the $select : new Zend_Db_Expr(" ( $select ) ") Commented May 13, 2017 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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