I am currently refactoring a legacy application and converting piece by piece over to zend framework 1.12.
I am scratching my head as to how to convert this over to zend db, is there a way that this can be done in one query?
Right now I see that it fetches a list of folders first and then runs an additional query per folder... Running this as one query will improve performance, right?
$folders_query = DB::Query("select * from contacts_folders order by sort_order, name");
while($folders = DB::FetchArray($folders_query)){
$counts_total = DB::QueryOne("SELECT count(cm.messages_id) AS total
FROM contacts_basics cb, contacts_messages cm
WHERE cb.contacts_id = cm.contacts_id
AND cm.folders_id = '" . $folders['folders_id'] . "'
AND cm.status = '1'
AND cm.mark = '0'");
if ($counts_total >0){
$folders_name = '<strong>' . $folders['name'] . ' (' . $counts_total . ')</strong>';
} else {
$folders_name = $folders['name'];
}
echo '<li><a href="messages.php?fID=' . $folders['folders_id'] . '">';
echo $folders_name;
echo '</a></li>';
}
Yes, you can do both in the same query
SELECT cf.*, count(cm.messages_id) AS total
FROM contacts_folders cf left outer join
contacts_messages cm
on cf.id = cm.folders_id and
cm.status = '1' AND cm.mark = '0' left outer join
contacts_basics cb
on cb.contacts_id = cm.contacts_id
group by cf.folders_id
order by cf.sort_order, cf.name;
This uses a left outer join
to be sure that you get all folders, even if there are no messages (which is how the original code works). Because of the left outer join
, the conditions need to be moved into on
clauses.
It also fetches all the information from the folders as well as the total. If there are no messages, then it should return 0 for that folder.