Welcome Guest, Not a member yet? Register   Sign In
Query Builder: Multiple Join and Group_by
#1

(This post was last modified: 12-17-2019, 03:37 AM by Simtsit.)

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
Reply




Theme © iAndrew 2016 - Forum software by © MyBB