CodeIgniter Forums

Full Version: Query Builder: Multiple Join and Group_by
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
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