Codeigniter active record join with 3 tables

Question

I have 3 tables,

user - contains user_id | username | fullname | email etcc
user_profile - contains entry_id |user_id| profile_image_id| user_location etcc
user_profile_images - contains image_id| user_id | file_path | file_thumb | date etcc

When a user sign's up, all the details go to the users table, the user_id is also added to the user_profile table with the rest set default as NULL till the user add's some profile info.

I have a query in model_users to get all user's data from these three table and it goes like this;

$this->db->where('users.user_id', $user_id)->select('*')->from('users');
$this->db->join('user_profile', 'user_profile.user_id = users.user_id', 'left');
$this->db->join('user_profile_images','user_profile_images.image_id = user_profile.profile_image_id','left');

It work's fine only when the profile_image_id field in user_profile is not null, that is when the user has uploaded a pic. In the case where the field is null, the user user_id is not returned even when all the other data are returned.

I can see why this is the case, as my join query requires the field profile_image_id but currently my way around it was to set profile_image_id in user_profile as 1(default), which is the default image and has image_id as 1 and user_id as 0 as it's the general default image. But i still can't get over the fact that i need to update that query to make it less of a hack. Do you guys have any ideas?

Answer 1

Try

$this->db->select('*')->select('users.user_id')->from('users')
                ->join('user_profile', 'user_profile.user_id = users.user_id', 'LEFT')
                ->join('user_profile_images', 'user_profile_images.image_id = user_profile.profile_image_id', 'LEFT')
                ->group_by('users.user_id')
                ->where('users.user_id', $user_id);
                $query = $this->db->get();