• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Query Builder: Multiple Join and Group_by

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

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  

  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.