phpmysqlcodeigniterjoinactiverecord

Join two result in codeigniter


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

  1. User_ID
  2. FullName
  3. UserName

post table has these records

  1. Post_ID
  2. Title
  3. User_ID

that user_id field save creator user_id

and like table

  1. Like_ID
  2. User_ID
  3. Post_ID

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


Solution

  • 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.