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!
