+- 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: Query Builder: Multiple Join and Group_by (/showthread.php?tid=75073)
Query Builder: Multiple Join and Group_by - Simtsit - 12-16-2019
Working with 3.1x on a typical social media project where users can upload, comment or like photos.
My database looks like this
Table users
id, username
Table photos
id, filename, uploader_id
Table photo_comments
id, photo_id, text, user_id (id of the user who comment on that photo)
Table photo_likes
id, photo_id, user_id (id of the user who likes that photo)
Now, I try a view where each photo has a caption with uploader username, total comments and total likes.
So far, I managed to show username and count the comments with 2 join and 1 group_by. Trying the same steps for counting likes makes the numbers go wild.
I now thing of breaking the query into 3 queries (isn't that a waist of resources?)
Any ideas how to build a query via query builder?
If not possible, an SQL query will be also appriciated!
Thank you