CodeIgniter Active Record For Joining The Tables

Question

I've following tables.

       apartments
       id name slug created modified

       apartment_amenities
       id name slug apartment_id created modified

       apartment_activities
       id name slug apartment_id created modified

In the view I wanted something like this.

       no apartment_name    amenities       activities

       1  shobha_comnplex   party hall      pamplets
                            swimming pool   banners
                            play area       boards

       2  navami_comnplex   party hall      boards
                            swimming pool   banners
                            club house      pamplets

In the model I tried like this.

        $this->db->select('apartments.id, apartments.slug, apartments.name, apartment_amenities.name as amenity_name, apartment_activities.name as activity_name');
        $this->db->from($this->_table);
        $this->db->join('apartment_amenities', 'apartment_amenities.apartment_id = apartments.id', 'left');
        $this->db->join('apartment_activities', 'apartment_activities.apartment_id = apartments.id', 'left'); 
        return $this->db->get();

But I'm getting only single amenity and activity even the apartment has many amenities and activities. The result is as follows.

Array
(
    [0] => 
        (
            [id] => 1
            [slug] => shobha_complex
            [name] => shobha complex
            [amenity_name] => party hall
            [activity_name] => pamplets
        ),

    [1] => 
        (
            [id] => 1
            [slug] => navami_complex
            [name] => navami complex
            [amenity_name] => party hall
            [activity_name] => boards
        )
)

I want the result something like as follows.

    Array
    (
        [0] => 
            (
                [id] => 1
                [slug] => shobha_complex
                [name] => shobha complex
                [amenities] => Array(
                    [0] => 
                         (
                            [name] => party hall
                         ),
                    [1] => 
                         (
                            [name] => swimming pool
                         ),
                    [2] => 
                         (
                            [name] => play area
                         )
                ),
                [activities] => Array(
                    [0] => 
                         (
                            [name] => pamplets
                         ),
                    [1] => 
                         (
                            [name] => banners
                         ),
                    [2] => 
                         (
                            [name] => boards
                         )
                )
            ),

        [1] => 
            (
                [id] => 1
                [slug] => navami_complex
                [name] => Navami complex
                [amenities] => Array(
                    [0] => 
                         (
                            [name] => party hall
                         ),
                    [1] => 
                         (
                            [name] => swimming pool
                         ),
                    [2] => 
                         (
                            [name] => club house
                         )
                ),
                [activities] => Array(
                    [0] => 
                         (
                            [name] => boards
                         ),
                    [1] => 
                         (
                            [name] => banners
                         ),
                    [2] => 
                         (
                            [name] => pamplets
                         )
                )
            ),
    )

Please suggest me how would I get the solution. The work would be more appreciated.

score 1 · Answer 1 · answered Feb 13 '14 at 21:23

You can use group concat on your select and use as separator | so you will get only two row and on your view split the column with the separator

$this->db->select("GROUP_CONCAT(partment_amenities.name SEPARATOR '|') as amenity_name, ...... ", FALSE);

Second parameter FALSE for not to protect identifier See the link for more about group concate: https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

score 0 · Answer 2 · answered Feb 04 '14 at 09:46

Remove the $this->db->group_by method call. This would only return one result for each apartment. If you remove this, you should get all the amenities you want etc.

The only problem would be that you get results like this

   no apartment_name    amenities       activities

   1  shobha_comnplex   party hall      pamplets
   1  shobha_comnplex   swimming pool   banners
   1  shobha_comnplex   play area       boards

   2  navami_comnplex   party hall      boards
   2  navami_comnplex   swimming pool   banners
   2  navami_comnplex   club house      pamplets

score 0 · Answer 3 · answered Feb 04 '15 at 16:25

To setup a proper array, in the format your looking for i would advise having a few models in place.

Lets setup a scenerio

Controller:

$apartments = $this->apartment_model->getApartments(); // Get All Apartments
foreach($apartments as &$apartment)
{
  $apartment->amenities = $this->apartment_model->getAmenities($apartment->id);
  $apartment->activities= $this->apartment_model->getActivities($apartment->id);

  // Add / Modify any addition properties
}

// Do something with the $apartments array

Model:

class Apartment_model extends model
{
  function getApartments()
  {
    $this->db->select('id, slug, name');
    $this->db->from('apartments');
    $query = $this->db->get();
    return $query->result();
  }
  function getAmenities($apartment_id)
  {
    $this->db->select('name');
    $this->db->from('apartment_amenities');
    $this->db->where('id', $apartment_id);
    $query = $this->db->get();
    return $query->result();
  }
  function getActivities($apartment_id)
  {
    $this->db->select('name');
    $this->db->from('apartment_activities');
    $this->db->where('id', $apartment_id);
    $query = $this->db->get();
    return $query->result();
  }
}

Of course keep in mind this doesnt load the view yet or move your data anywhere past the controller, but it will at least setup the array.

I hope this helps get you started.

CodeIgniter Active Record For Joining The Tables

3 Answers3

Linked