MVC - best way to get results between mulitple models/db tables

[eluser]cruzanmo[/eluser]
Hi, I have a general question relating to making a query across multiple DB tables using the MVC framework. My database is set up like this:

Table 'locations' has columns: 'id','gallery_id', ...
Table 'shows' has columns: 'id','gallery','location_id', ...
Table 'events' has columns: 'id','show_id', ...

On a gallery view page, I want to show all locations for that gallery, which I can easily get using a get_where query with the gallery id from the locations table.

For each of the gallery's locations, I then want to display all of the events for each location. This means for each location, I need to get all events where the events.show_id = shows.id and shows.location_id = the current location's id.

Right now I pretty much have one model file for each database table. Since this query goes across multiple database tables I'm not sure in which model to put the query and how to exactly return the data to the view when there are multiple locations with each location having multiple events.

I've read all the documentation once, but if there is a section I should review for this, please let me know.

Thanks!

PS. I've been able to create what I want but putting a query within a foreach statement in the view file, but I think it would be better if I could figure out how to do all queries in the model and then perhaps use a multidimensional array to display it in the view...

[eluser]jalalski[/eluser]
I'm doing something similar (programs and events spread over a few years, no locations though).

There is more than one way to do what you want I guess, here are some ideas...

To show all locations for a gallery:
Create a gallery Model, with the usual functions for CRUD and a function get_locations($gid) which will return all the locations for that gallery.

For events at a location, you could either use a Location Model and then have a function get_events($locid) or use an Events Model and pull the events for a particular location that way. It mainly depends on whether you application will be 'Events centered' or 'Location centered'. i.e. what is the focus of the application.

It's quite common to have a Model for each table in the DB, although in my experience there are sometimes tables that won't need a Model. One of the first steps I do when I start an application is to work out what models/tables are needed and the interaction between them and the rest of the system. This then gives me an API between the model layer and the controller layer.

Try and keep all queries in the Model layer and have the Models return either arrays of data or objects (I like objects myself). Your Controllers are then just dealing with objects and don't need to know how they are created from the database. The controllers then pass these objects on to the Views which simply display the information to the user.

HTH

[eluser]cruzanmo[/eluser]
Thanks jalalski - i see what you're saying and i think it makes sense.

I'm not sure how to return the DB results to the controller/view, though, since there are many locations and each location has many events.

Right now, I'm doing something like this:

//$locations is an array sent from the model/controller
foreach ($locations as $location){
echo $location['location_name'];
//get events for the location
$Q = $this->db->query('SELECT * FROM events, shows
WHERE events.event_show=shows.id
AND shows.show_located_at='.$location['id'].'
ORDER BY events.event_date DESC');
if ($Q->num_rows() > 0){
foreach ($Q->result_array() as $row){
echo $row['event_title']
}
}
}

Basically it requires a foreach within a foreach (at least the way I'm doing it right now). And the part I want to change is that the query within the foreach is done in the view.

Do you have any code example of how to return/display this when both queries are in the model? I'm fine with using objects rather than arrays if it makes it easier...

[eluser]The Wizard[/eluser]
i allways seperate the logic so instead of 1 model 1 table approach,
ive a model_user and i use it for ALL things releated to the user in
first place, and so on.

[eluser]darkhouse[/eluser]
[quote author="herrkaleun" date="1232866120"]i allways seperate the logic so instead of 1 model 1 table approach,
ive a model_user and i use it for ALL things releated to the user in
first place, and so on.[/quote]

I agree, this is a more logical approach, and is how I work also. Now there's a much easier way of joining data from multiple tables in a database. Use a JOIN. It sounds like you just need to do something like this in your locations model:

Code:

function get_gallery_locations($gallery_id){
   $this->db->select('events.event_title');
   $this->db->join('shows', 'shows.show_located_at = locations.id');
   $this->db->join('events', 'events.show_id = shows.id');
   $this->db->where('locations.gallery_id', $gallery_id);
   $query = $this->db->get('locations');
   return $query->result();
}

I think that will give you what you want based on your description and your current code.

[eluser]obiron2[/eluser]
I have had the same problem with a very complicated data set for a fantasy f1 site.

The player team model consisted of two drivers, a car,engine and tyres.

Points were scores for finishing positions for each element.

the 'real life' data was based on racing teams with their car,engine and two drivers.

To get the current cumulative score for each player team:-

get driver 1, get their result for the race, get the points for the result, add to driver 1 cumulative score.
repeat for driver 2

get engine, get racing team, get racing team driver 1, get race result, get points, add to cumulative. Repeat for racing team driver 2.

repeat engine search for car.

repeat for 17 races in the season

repeat for 40 player teams.

What could be done in one HUGE SQL query with a whole bunch (about 12 from memory) of inner joins and sub-selects, when moved into proper MVC required 7200 trips to the database with the subsequent overhead on performance.


I think you need to go with whatever feels right. If it is getting too complicated to support, move to full MVC. If the performance hit is too large then you may want to look at saving cached results in the database, rather than cacluating them each time, or move the complicated logic into the database server using stored procedures - which is outside MVC and CI.

Obiron