Zend Framwork select() gives unexpected SQL

Question

For my application I have a method in my Messages model which returns all the messages sent by the current user ($id). It returns information from 3 tables using 3 joins. As both the sender and the recipient come from the same 'users' table, I had to alias a these columns.

Method in my Messages model:

public function getSentMessages($id) 
{
    $messageModel = new self();
    $select = $messageModel->select()
                 ->from(array('m' => 'messages'),
                        array('m.id', 'm.title', 'm.send',
                              's.id AS s_id', 's.firstname AS s_fn', 's.lastname AS s_ln', 's.email AS s_e', 
                              'r.id AS r_id', 'r.firstname AS r_fn', 'r.lastname AS r_ln', 'r.email AS r_e'))
                 ->join(array('s' => 'users'),
                        'm.from = s.id')
                 ->join(array('mu' => 'messages_users'),
                        'm.id = mu.message_id')
                 ->join(array('r' => 'users'),
                        'r.id = mu.user_id')
                 ->where('m.from = ?', $id);


    $select->setIntegrityCheck(false); 
    $messages = $messageModel->fetchAll($select);

    return $messages;
}

Strange things where happening: the resultrow was made out of all the columns I'd requested... but somehow all the columns (includuding the ones I didn't request) where added after. Like this:

id  title   send    s_id    s_firstname     s_lastname  s_email     r_id    r_firstname     r_lastname  r_email     id  username    password    firstname   lastname    email   role    id  message_id  user_id     status  id  username    password    firstname   lastname    email   role

I found out the SQL query returned by the Zend Framework is not how I expected it to be.. Here it is (cleaned up for better readability):

SELECT m.id, m.title, m.send, 
s.id AS s_id, s.firstname AS s_fn, s.lastname AS s_ln, s.email AS s_e, 
r.id AS r_id, r.firstname AS r_fn, r.lastname AS r_ln, r.email AS r_e, 
s. * , mu. * , r. *
FROM messages AS m
INNER JOIN users AS s ON m.from = s.id
INNER JOIN messages_users AS mu ON m.id = mu.message_id
INNER JOIN users AS r ON r.id = mu.user_id
WHERE (
m.from = '12345'
)

Check these asterixes (*) at the end of my SELECT statement.. how did these get there? The Zend documentations tells me an asterix is only used when no columns are given..

Thanks for you help!

Answer 1

Join has three parameters:

Add a blank value @ end of each Join clause..

join(array('s' => 'users'),'m.from = s.id','')