Add another table for querying and counting SQL data

Question

I am trying to select data from 4 tables (the last table should count the data)

The structure of my MySQL tables

of users

id username 

Images

 id user_id image 

user_follow

 id user_id follow_id 

comments

 id user_id image_id text 

I have this SQL query:

  $sql = "SELECT u.username as user, i.image as user_image, p.image, p.date FROM users u LEFT JOIN user_follow f ON u.id = f.follow_id LEFT JOIN images p ON p.user_id = u.id LEFT JOIN images i ON i.id = (SELECT b.id FROM images AS b where p.user_id = b.user_id ORDER BY b.id DESC LIMIT 1) WHERE f.user_id = 3 OR p.user_id = 3 ORDER BY p.date DESC"; 

This line returns the current user image (last image)

  LEFT JOIN images i ON i.id = (SELECT b.id FROM images AS b where p.user_id = b.user_id ORDER BY b.id DESC LIMIT 1) 

it returns all images from me and my friends

 [0] => Array ( [user] => 8888 [user_image] => second.jpg [image] => second.jpg [date] => 2012-01-24 14:42:27 ) [1] => Array ( [user] => 8888 [user_image] => second.jpg [image] => first.jpg [date] => 2012-01-24 14:42:27 ) [2] => Array ( [user] => 3333 [user_image] => ax46l7v7vugnesk10whk_339.jpg [image] => ax46l7v7vugnesk10whk_339.jpg [date] => 2012-01-24 01:54:19 ) [3] => Array ( [user] => 3333 [user_image] => ax46l7v7vugnesk10whk_339.jpg [image] => aaaaaaaa.jpg [date] => 2012-01-24 01:49:57 ) 

I tried to add

  left join commentaries c ON c.user_id = u.id 

and the result was

 [2] => Array ( [user] => 3333 [user_image] => ax46l7v7vugnesk10whk_339.jpg [image] => ax46l7v7vugnesk10whk_339.jpg [date] => 2012-01-24 01:54:19 [id] => 1 ) [3] => Array ( [user] => 3333 [user_image] => ax46l7v7vugnesk10whk_339.jpg [image] => ax46l7v7vugnesk10whk_339.jpg [date] => 2012-01-24 01:54:19 [id] => 2 ) [4] => Array ( [user] => 3333 [user_image] => ax46l7v7vugnesk10whk_339.jpg [image] => aaaaaaaa.jpg [date] => 2012-01-24 01:49:57 [id] => 1 ) [5] => Array ( [user] => 3333 [user_image] => ax46l7v7vugnesk10whk_339.jpg [image] => aaaaaaaa.jpg [date] => 2012-01-24 01:49:57 [id] => 2 ) 

Duplicate the user if he has comments (by the way, [user] => 3333 has 2 comments in the example)

I try to add another table of "comments" and calculate how many comments each image has (from me and my friends) if there are no comments with such $ user_id, and then returns 0

Answer 1

You need to use GROUP BY to count the rows in groups (in your case, comments for each image). This query should do the trick:

 SELECT u.username as user, i.image as user_image, p.image, p.date, COALESCE ( imgcount.cnt, 0 ) as comments FROM users u LEFT JOIN user_follow f ON u.id = f.follow_id LEFT JOIN images p ON p.user_id = u.id LEFT JOIN images i ON i.id = (SELECT b.id FROM images AS b where p.user_id = b.user_id ORDER BY b.id DESC LIMIT 1) LEFT JOIN ( SELECT image_id, COUNT(*) as cnt FROM commentaries GROUP BY image_id ) imgcount ON p.id = imgcount.image_id WHERE f.user_id = 3 OR p.user_id = 3 ORDER BY p.date DESC 

Answer 2

You really know how to ask a confusing question. The problem here is that you do not understand the consequences of your associations.

Using your example. You have a table (users in this case) that has one to many relationships to two other tables (images and comments).

For instance:

  users / \ images commentaries 

When you try to simultaneously connect to both of these linked tables to your base table, the effect should provide the equivalent of a full outer join between two child tables.

It:

 SELECT * FROM users u LEFT JOIN images p ON p.user_id = u.id LEFT JOIN commentaries c ON c.user_id = u.id 

exactly the same:

 SELECT * FROM images p LEFT JOIN commentaries c ON c.user_id = p.user_id 

(in terms of the number of entries made)

It would be nice if one of the child tables had a 1 to 1 relationship with the parent table, but that is not the case. Since they both have several records in them, the effect is FULL EXTERNAL ENTRY, and the result is a multiplication of the number of records created in the output. The output will contain a number or records equal to the number of matching records in one table times the number of matching records in another table. Thus, since you have two records in each table corresponding to this user_id, the result set contains four records.

It's hard to understand the last part of your question, so some clarifications will be pleasant. It seems you are only trying to read records from one table or another, although, to be honest, I'm not sure what.

The next line is extremely confusing.

count how many comments each image has

If you really only want to count records from one table or another, then grouping will solve your problem with multiplication.

 $sql = "SELECT u.username as user, i.image as user_image, p.image, p.date, c.commentcount FROM users u LEFT JOIN user_follow f ON u.id = f.follow_id LEFT JOIN images p ON p.user_id = u.id LEFT JOIN images i ON i.id = (SELECT b.id FROM images AS b where p.user_id = b.user_id ORDER BY b.id DESC LIMIT 1) LEFT JOIN (SELECT x.user_id, COUNT(*) AS commentcount FROM commentaries x GROUP BY x.user_id) c ON c.user_id = u.id WHERE f.user_id = 3 OR p.user_id = 3 ORDER BY p.date DESC"; 

The problem with this, as noted earlier, is that it has little to do with images. There is no noticeable direct connection between images and comments. Between them, there are only many, many relationships through the users table. Therefore, it is very difficult for me to be sure that this helps you in general.

In essence, this only gives you the number of comments the user has. This has nothing to do with the images. You can use very similar code to tell you how many images the user has, but this has nothing to do with the comments.

If what you want to do determines how much one user has, if the user has another at all, then this will answer this question. You would simply add a test to the WHERE clause to determine if the user has the required related record or not.

If you can clarify your intentions, I can help more. Otherwise, I hope this helps.