I started a new project with codeigniter and I create three tables for users and posts and likes like this
user table has these records
post table has these records
that user_id field save creator user_id
and like table
I want to join this three tables and I can do it but I need join two result like this and I don't know what can I do
$this->db->select('*');
$this->db->from('Post');
$this->db->join('User', 'User.User_ID = Post.User_ID', 'left');
$query = $this->db->get();
return $query->result();
and this
$this->db->select('*');
$this->db->from('Like');
$this->db->where('User_ID' = $id);
$query = $this->db->get();
return $query->result();
and then join these results with together
Please help me
If I understand your question correctly, you would like to determine which posts the current user likes. The sql statement would look like sg like the following:
select p.*, u.*, l.user_id as like_user_id
from post p
inner join user u on u.user_id=p.user_id -- I do not think you need a left join here
left join `like` l on p.post_id=l.post_id and l.user_id=$id
I the like
table is left joined to the post
table using the common post_id
fields. In case l.user_id is null, then the current user does not like the post. Based on the above query and your 1st codeigniter code, you should be able to create the codeigniter version of this query.