Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
What sort of error do you get? And how does the query 'working fine in MySQL' look like? – dezso Aug 9 '12 at 14:24
It isn't exactly working to be honest in phpMyAdmin. I'm not getting the messages grouped but I'm getting the unique from ids. When I run it with PDO in Zend, I'm not getting anything. – user10738 Aug 9 '12 at 14:50
If you want the last message of a specific user, I don't think you need to do a self-join. ORDER BY date_created DESC with LIMIT 1 will be ok. – ypercube Aug 9 '12 at 15:16
Actually if you read my question, what I wish to do is to get a the latest message from the list of messages sent/received to a user from different users. – user10738 Aug 9 '12 at 17:59
Perhaps you mean, for a specific user, get the latest message received from/sent to every other user. – ypercube Aug 10 '12 at 20:24
show 1 more comment

closed as too localized by Jack Douglas♦ Oct 13 '12 at 10:39

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, see the FAQ.

1 Answer

up vote 0 down vote

Can you try this one?:

$select=$this->select()
->setIntegrityCheck(false)
->from(array('m1'=>'messages'))
->joinLeft( array('m2'=>'messages') 
          , ' ( m1.from_id=m2.from_id AND m1.to_id = m2.to_id
             OR m1.from_id=m2.to_id AND m1.to_id = m2.from_id) 
            AND m1.date_created < m2.date_created'
          , array() )
->where('m2.date_created IS NULL')
->where('m1.from_id=? OR m1.to_id=?', $to)
->order('m1.date_created DESC');
share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.