How to use Join in Zend Framework?

Question

i have these two tables ,

s(id,firstname,lastname,mydate,mount,sh)

users(uid,email,password,nameoffice)

and this query

$sql="SELECT s.id,s.firstname,s.lastname,s.mydate,s.mount,users.nameOffice FROM sayer LEFT JOIN users ON s.sh=users.sh order by date(s.mydate) DESC,s.mount DESC";

how could I write this query on zend framework?

Answer 1

One way would be as follows:

    $db = Zend_Db_Table::getDefaultAdapter();
    $select = $db->select();

    $select->from(array('s' => 'sayer'), array('id', 'firstname', 'lastname', 'mydate', 'mount'))
           ->joinLeft(array('u' => 'users'), 's.sh = u.sh', array('nameOffice'))
            ->order(array('date(s.mydate) DESC', 's.mount DESC'));

    print($select->assemble());

This results in:

    SELECT `s`.`id`, `s`.`firstname`, `s`.`lastname`, `s`.`mydate`, `s`.`mount`, 
   `u`.`nameOffice` FROM `sayer` AS `s` LEFT JOIN `users` AS `u` ON s.sh = u.sh 
    ORDER BY date(s.mydate) DESC, `s`.`mount` DESC

For more have a look at Zend_Db_Select.