A controller problem using a base CRUD model

Question

In CodeIgniter I'm using a base CRUD My_model, but I have this small problem in my browse-controller.. My $data['posts'] gets all posts from the table called "posts". Though the author in that table is just a user_id, which is why I need to use my "getusername" function (gets the username from a ID) to grab the username from the users table.

Though I don't know how to proceed from here, since it is not just one post. Therefore I need the username to either be a part of the $data['posts'] array or some other smart solution.

Anyone who can help me out?

 function index() {
  $this->load->model('browse_model');

  $data['posts'] = $this->browse_model->get_all();
  $data['user'] = $this->browse_model->getusername(XX);

  $this->load->view('header');
  $this->load->view('browse/index', $data);
  $this->load->view('footer');
 }

Answer 1

Your best bet would be to fetch the associated username when fetching the posts by joining the users table on the posts table. This way you are performing just one database query. You could override the get_all function of the base My_model.

function get_all()
{
    $sql = 'SELECT p.*, u.username
            FROM posts p
            JOIN users u ON u.user_id=p.user_id';

    $query = $this->db->query($sql);
    return $query->result();       
}

Each returned post object will then have a username property.

$posts = $this->browse_model->get_all();
foreach ($posts as $post)
{
    echo $post->username;
}

Answer 2

An alternative way to do the same thing:

function get_all()
{
   $this->db
       ->select('posts.*, u.username')
       ->join('users u', 'u.user_id=posts.user_id');

   return parent::get_all();
}