Collection Join Query – How to Join a Collection with a Query

collection;exportgridjoin;renderer

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

Best Answer

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()
    );
Related Topic