CODEWITHSUNDEEP
phpactiverecordcodeigniter
Sergio
Sergio

Reputation: 9917

How to work with join data in codeigniter?

I am having a play around with codeigniter and trying to get my head around the active record system and such like. I have set up a couple of tables and am attempting to run a join on them, as such:

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();
}

In my database i have one organisation with two related buildings. The above query returns two objects (one for each building) - however, the organisation is duplicated.

I suppose I was expecting something along the lines of one return object with a series of nested objects for related buildings. Is this possible? If not, is their a recommend way of arranging the return data so I can easily loop through it in the view? (foreach org, foreach building etc etc).

Apologies if I'm being a little dense here. Im coming from .net and (linq to SQL in particular) where this stuff is a little different)

Upvotes: 1

Views: 1966

Answers (1)

Matteo Riva
Matteo Riva

Reputation: 25060

The query will inevitably return duplicate data as you say, you have to organize them after you get the result like this

$buildings = array();

foreach ( $result_object as $organization ) {
    $building_data = array(
        'building_name' => $organization->building_name,
        'address'       => $organization->address,
    );
    $buildings[$organization->organization_name][] = $building_data;
}

this way organizations will be "compacted" in the first key of the multidimensional array, and one level deeper you will have info about the buildings. Hope this helps.

Upvotes: 1

Related Questions