I've been programming procedural code for quite some time, and recently I've been learning MVC. I have tried a couple of frameworks and decided I wanted to write my own from scratch to learn it inside and out. So far I have a working MVC framework (and it works great!) but I'm trying to determine the best way to query related tables.
As of now, in my member model, I use the following to get a list of the members and their rank_name
SELECT m.*, r.`name` AS rank_name FROM `{$this->table}` AS m LEFT JOIN `ranks` AS r ON r.`id` = m.`rank_id` This works fine, but it does not seem like the way it's supposed to be done in MVC. After working with frameworks like cakephp, I know that member hasOne rank (or is it rank hasMany member?)
The only alternative I thought of was to query the members table by itself in the member model, and then call a method in the rank model to get the rank name for each row, like this:
// member model SELECT * FROM `{this->table}` while($row = $result->fetch_assoc()) { $data []= $row; $data['rank_name'] = $this->Rank->get_name($row['rank_id']); } But this can't be very efficient, having to run a separate query for each member. The only other concept I thought of was using MySQL's IN(x, y, z) function to get the rank names and then merging the arrays somehow.
What is the best practice for this in MVC?
