I have been trying to select the latest sent/received messages for a specific user in a Zend database table.
I've got the query working fine in MySQL but when I try to do it with
joinLeft() it gives an error.
Code for the query in Zend database table:
$select=$this->select() ->setIntegrityCheck(false) ->from(array('m1'=>'messages')) ->joinLeft(array('m2'=>'messages'), 'm1.from_id=m2.from_id AND m1.date_created < m2.date_created') ->where('m2.date_created IS NULL') ->where('m1.from_id=? OR m1.to_id=?', $to) ->order('m1.date_created DESC');
The problem is in the additional condition in joinLeft(). If I replace '<' with '=', I get no error but that is not what serves the purpose.