Codeigniter populate nested associations

Question

this is the results i need from the database

{comment: 'hello there', user: [{name: 'sahan', id: 1}], id: 2}

function used to get comments

public function get_comments(){
    $query = $this->db->get('comments');
    return $query->result_array();
}

I have a comments table and a users table, When a user comments on something the

comment is saved as follows
comment > Comment text
user: userid

So when the data is shown I need codeigniter to populate the user field with the user data found from the users table

Does anyone know how to do this ? I used this functionality in SailsJS but dont know how to do it here in CodeIG Sails.js populate nested associations

Can we see the code you are using to pull the comments from the db? — nivix zixer, May 23 '15 at 02:33
Just curious, but why do you want the user data inside an array? Only one user per comment, right? — nivix zixer, May 23 '15 at 12:20
So that I could easily render my views, you know who made the comment and so on — Sahan, May 24 '15 at 06:04

nivix zixer · Answer 1 · 2015-05-26T15:42:45.610

1

Codeigniter 's active record is not as advanced as SailJS active record, but you can achieve what you are asking for with two queries within the same method.

I'm assuming the comments table has a foreign key to the users table through a user_id field.

public function get_comments() {

    /* Get all the comments */
    $query = $this->db->get('comments');
    $comments = $query->result_array();

    /* Loop through each comment, pulling the associated user from the db */
    foreach $comments as &$comment {
        $query = $this->db->get_where('users', array('id' => $comment['user_id']));
        $user = $query->result_array();

        /* Put the user's data in a key called 'user' within the  comment array */
        $comment['user'] = $user;

        /* Remove the unnecessary user_id and $user variable */
        unset($comment['user_id']);
        unset($user);
    }
    return $comments;
}

edited May 26 '15 at 15:42

answered May 23 '15 at 12:39

nivix zixer

1,611
1
13
19

What if I do a SQL Join ? Like this example here ? http://www.tutorialspoint.com/sql/sql-using-joins.htm – Sahan May 30 '15 at 02:11
You could, but the code would get complex to store the user's information in an array. – nivix zixer May 30 '15 at 02:26
So In your opinion your solution if much more efficient ? – Sahan May 30 '15 at 02:35
Perhaps not efficient, but simpler. You could make it more efficient with raw SQL and joins, if you like. – nivix zixer May 30 '15 at 02:54
So like what the link I added right ? Thats what you meant raw sql ? – Sahan May 30 '15 at 04:53
Could you help me understand as explained here the JOIN method using Codegintier to do the same thing http://www.codeigniter.com/user_guide/database/query_builder.html?highlight=get_where#CI_DB_query_builder::get_where – Sahan May 30 '15 at 05:22

Codeigniter populate nested associations

1 Answers1