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.

cha · Answer 1 · 2012-10-22T12:54:18.140

Finaly I have done 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(array('r2' => $subselect2))
                    ->join("currency_rates", '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;
    }

I have passed associate array for $select->from() like this;

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

pozs · Accepted Answer · 2012-10-22T12:42:46.683

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.

edited Oct 22 '12 at 12:42

answered Oct 22 '12 at 11:57

pozs

34,608
5
57
63

I think, it supports subqueries. This example(https://github.com/ralphschindler/Zend_Db-Examples/blob/master/example-20.php) gives how to do it. But question was how to join resultset and same table again. I have edited my question with sql statement. Pls have a look.. – cha Oct 22 '12 at 12:12
because it uses in `$select->where`, as i wrote – pozs Oct 22 '12 at 12:17
I'm sorry I didn't get it. But now i have done it. Thanks for replying. If you are ok, can you pls explain little more detail. – cha Oct 22 '12 at 12:25
Good explanation. Got it. Thanks pozs. I have already done it and put a reply. – cha Oct 22 '12 at 12:51

Join one resultset with another table zf2

2 Answers2