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(*)') )
Improve this question

1 Answer 1

Reset to default
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() );
Improve this answer
10
  • 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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.