PHP CodeIgniter multiple LEFT OUTER join in on query

Question

I'm using PHP CodeIgniter to query from my Database. I need to use multiple LEFT OUTER joins. The join uses multiple conditions (I'm not sure it is supported as of the bug). My query results have the same value for both parameters I want to fetch:

Query:

$this->db->select('anonymous.*,
    count(items_likes.id) as all_likes, count(items_comments.id) as all_comments');
    $this->db->from('anonymous');
    $this->db->where('anonymous.fid', $feed_id);

    $this->db->group_by('anonymous.id');

    $this->db->join('all_users', 'all_users.id = anonymous.uid', 'left outer');
    $this->db->join('images', 'images.ud = anonymous.uid AND images.fid = anonymous.fid', 'left outer');
    $this->db->join('items_likes', 'items_likes.uid = anonymous.uid AND items_likes.fid = anonymous.fid', 'left outer');

    $this->db->join('items_comments', 'items_comments.uid = anonymous.uid AND items_comments.fid = anonymous.fid', 'left outer');

    $list = $this->db->get()->result();

anonymous: is a table with unregistered users

fid: Is the feed id

images: Is a table of all the images that were uploaded to the feed

items_likes: Is all the users' likes per image

items_comments: Is all the users' comments per image

The problem is that all_comments always return the same value as all_likes. I'm not sure if the multiple LEFT-OUTER joins are the problem or maybe the fact that I'm using multiple conditions in every join statement i.e.

items_comments.uid = anonymous.uid AND items_comments.fid = anonymous.fid

The log result:

"all_likes":"12","all_comments":"12"

although I have 12 likes, the comments should be 3

If I write the query as follows the result is ok:

$this->db->select('anonymous.*,
(SELECT count( items_likes.id ) FROM items_likes WHERE items_likes.uid = anonymous.uid AND items_likes.fid = anonymous.fid) as all_likes, 
(SELECT count( items_comments.id ) FROM items_comments WHERE items_comments.uid = anonymous.uid AND items_comments.fid = anonymous.fid) as all_comments');
$this->db->from('anonymous');
$this->db->where('anonymous.fid', $feed_id);

$this->db->group_by('anonymous.id');

$this->db->join('all_users', 'all_users.id = anonymous.uid', 'left outer');
$this->db->join('images', 'images.ud = anonymous.uid AND images.fid = anonymous.fid', 'left outer');
//$this->db->join('items_likes', 'items_likes.uid = anonymous.uid AND items_likes.fid = anonymous.fid', 'left outer');

//$this->db->join('items_comments', 'items_comments.uid = anonymous.uid AND items_comments.fid = anonymous.fid', 'left outer');

$list = $this->db->get()->result();

Vickel · Accepted Answer · 2019-10-28 12:23:48Z

1

Left outer join produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.

You need to add the records which contain NULL

add this where clause to your query:

$this->db->->where('items_comments.uid IS NULL')

more info on joins here

answered Oct 28, 2019 at 12:23

Vickel

7,9806 gold badges40 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

NDM Over a year ago

Did you mean: IS NOT NULL

Vickel Over a year ago

no, if you look at the diagram I linked explaining the left outer join, you'll see table B is the comments which were made on Table A posts. So after joining, you want to exclude all the NULLs (no comments) from table B, to get the list of elements in table A with a comment (B). I don't know your table scheme, you might need to adapt this, but this is the concept...

NDM Over a year ago

Thanks, I’ll check it and advise

Collectives™ on Stack Overflow

PHP CodeIgniter multiple LEFT OUTER join in on query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related