Join one resultset with another table zf2

Question

I did following function and it's working fine.

public function getLatestCurrencyRates(){
        $sql = new Sql($this->adapter);

        $subselect2 = $sql->select();
        $subselect2->from(array('r1' =>'currency_rates'))
                    ->columns(array('max_c_rate_id' => new Expression('MAX(c_rate_id)')))
                    ->group("currency_id");


        $statement = $sql->prepareStatementForSqlObject($subselect2);
        $result = $statement->execute();
        $rows = array_values(iterator_to_array($result));
        return $rows;
    }

I'm going to join above select statement with same table. Can anyone suggest how to do that? My current implementation as follows. Actually it's not completed. Because lack of knowledge to implement it.

public function getLatestCurrencyRates(){
        $sql = new Sql($this->adapter);

        $subselect2 = $sql->select();
        $subselect2->from(array('r1' =>'currency_rates'))
                    ->columns(array('max_c_rate_id' => new Expression('MAX(c_rate_id)')))
                    ->group("currency_id");


        $subselect3 = $sql->select();
        $subselect3->from("currency_rates")
                    ->join(array('r2'=>$subselect2), 'r2.max_c_rate_id = currency_rates.c_rate_id', array('c_rate_id', 'currency_id', 'buy_rate', 'sell_rate'));



        $statement = $sql->prepareStatementForSqlObject($subselect3);
        $result = $statement->execute();
        $rows = array_values(iterator_to_array($result));
        return $rows;
    }

SQL query that I'm going to implement here as follows.

 select r1.c_rate_id, r2.currency_id, r2.buy_rate, r2.sell_rate 
 from 
 (select max(c_rate_id)as c_rate_id from currency_rates group by currency_id) as r1
 inner join 
 currency_rates as r2
 on 
 r1.c_rate_id = r2.c_rate_id;

Can anyone give good feedback, will be helpful.

Thanks.

Answer 1

ZF2's Zend\Db\Sql\Select - at the moment i write this - lacks the support of sub-queries in $select->join(). You can only use them in $select->columns()& $select->where() (with Expressions).

Edit:

Only $select->join() lacks the support of sub-queries, $select->from() accepts it. It means the whole sub-query is quoted as an identifier, for now - probably it'll be fixed in some 2.0.* versions.