writing a subquery using Zend_Db_Table_Select

Question

I am not able to convert the below query into Zend_Db_Table_Select query.

SELECT GROUP_CONCAT(wallet_transaction_id) as wallet_transaction_ids,transaction_type,source,status
FROM(
    SELECT wallet_transaction_id, transaction_type, source, status
    FROM ubiw_transactions_wallet
    WHERE (game_id = '1292') AND (player_id = 1538) 
    ORDER BY date DESC LIMIT 100
) a 
GROUP BY a.transaction_type,a.status, a.transaction_type;

any help is appreciated.

thanks, shiv

Answer 1

// Maybe need some changes

$table = new Zend_Db_Table('ubiw_transactions_wallet');

$subSelect = $table->select()
->from('ubiw_transactions_wallet', array('wallet_transaction_id', 'transaction_type', 'source', 'status'))
->where($table->getAdapter()->quoteInto('game_id = ?', 1292))
->where($table->getAdapter()->quoteInto('player_id = ?', 1538))
->order('`date` DESC')
->limit(100);

$mainSelect = $table->select()
->setIntegrityCheck(false)
->from(array('a' => $subSelect), array('wallet_transaction_ids' => new Zend_Db_Expr('GROUP_CONCAT(wallet_transaction_id)'), 'transaction_type', 'source', 'status'))
->group(array('a.transaction_type', 'a.status', 'a.transaction_type'));

$result = $table->fetchAll($select);