+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: SOLVED: Query builder join count results (/showthread.php?tid=68545)
SOLVED: Query builder join count results - Wouter60 - 07-24-2017
I have a table called 'posts' with id, title, author, date and post content.
I also have 2 tables called 'comments' and 'likes'. Both "sub tables" hold the post_id, user_id and datetime.
Can anyone help me with a query that gives me the author, title, date, number of comments and number of likes for all posts, in one list?
I'm trying to solve it with arrays that are created by 3 different queries, but something tells me I can do it a lot easier with joins.
I just can't figure out how.
RE: Query builder join count results - Martin7483 - 07-25-2017
This could work.
Have not tested it, but found a similar question on Stack Overflow
PHP Code:
SELECT
post.*,
COUNT(distinct com.id) AS num_comments,
COUNT(distinct like.id) AS num_likes
FROM posts AS post
LEFT JOIN comments AS com ON com.post_id = post.id
LEFT JOIN likes AS like ON like.post_id = post.id
SOLVED: Query builder join count results - Wouter60 - 07-25-2017
Thanks! First, I got only one post title and the total number of comments and likes.
I just needed to add "GROUP BY post.id" to get that fixed.
RE: SOLVED: Query builder join count results - Martin7483 - 07-25-2017
Glad I could help & Graag gedaan
RE: SOLVED: Query builder join count results - anirudh88a - 07-26-2017
Thanks guys..was getting stuck in a similar issue myself. Your little conversation helped me out
