Models, foreign keys and table associations - whats best practise in codeigniter?

[eluser]warpkid[/eluser]
Hi There,
I am dipping my toe into php and codeigniter (coming from a .net background) and am begnning to attempt to build my first site.

Off the bat I am wondering the best way of handling model data in codeigniter. Let me expand...

In my test database I have two simple tables, organisations and buildings. A one to many relationship exists between them. Ideally, I would like my organisation model to be able to return an object containing organisation data, along with all related building rows.
Am i right in my understanding that there is no 'automagic' way for codeigniter to pick up on these relationships and return object graphs?

My model so far looks like this...

Code:

function GetOrganisationsAndBuildingDetails()
{
        $this->db->select('organisations.organisation_name,
                                           organisations.organisation_id,
                                           buildings.building_name,
                           buildings.address1');
        $this->db->from('organisations')->join('buildings', 'buildings.organisation_id = organisations.organisation_id');
        $query = $this->db->get();
        return $query->result();
}

This returns to my controller two organisation objects, each with separate building details, although the organisation details are the same (my database has one organisation in it with two related buildings).

Now, I can use a little code to build an array to pass to my view that contains the one org and its two buildings but id rather avoid this type of code as its going to get bulky if say, Im pulling data from 4 - 5 related tables (rare but it happens).

I suppose in short Im asking if there is a better way to think of model classes?
Should I avoid joining data in this way and attempting to work with nested data. Should I keep it simple and have a separate building model that I can pull buildings from when I pass in an organisation id?
Am i over thinking this?

Thanks in advance codeigniter community - and go easy on me, im a n00b!

[eluser]Colin Williams[/eluser]
Selecting related data with joins is typically a two-step process. 1.) Run the query. 2.) Group multiple values in the result. Step 2 is what you're a missing. It's not a bulky process, it's necessary

[eluser]sophistry[/eluser]
sounds like you are asking about object-relational mapping (ORM). there are a few libs around. search ORM. two that come to mind are "rapiddatamapper" and "datamapper overzealous edition"

welcome to CI, you're going to like it around here!

[eluser]Colin Williams[/eluser]
Automated ORM classes not necessary, but it will handle the process for you. If that's your cup-o-tea, go for it.

[eluser]warpkid[/eluser]
Thanks for the feedback guys.
I shall look into the ORM's mentioned.

I'm also interested in the alternative, which I presume is to stitch the data together from the model into a array that is nicely useable in the view?

I've been using the entity framework and LINQ to SQL for a couple of years so going back to 'doing it by hand' is proving a little tricky.

[eluser]Colin Williams[/eluser]

I presume is to stitch the data together from the model into a array that is nicely useable in the view?

Exactly. This is what the automated ORM libraries do based on a set of conventions you must follow. There's no difference in the implementation behind it: Iterate over the result, group rows by their primary keys.

Also, this should be done in the model. Controllers shouldn't be bothered with modeling the data.

[eluser]sophistry[/eluser]
just found an old thread from a year ago where colin provided some code: http://ellislab.com/forums/viewthread/100383/

funny how we tread the same ground...

[eluser]warpkid[/eluser]
Thanks for the link, its proved useful!

Think Im starting to get my head around the way I need to approach this stuff