Logical error of joining tables

Question

MySQL

Good afternoon!

There are two tables:

objects
id | title | active | rating

comments
id | obj_id | text

We need to extract all objects and the number of comments for each.

I form a request:

SELECT `objects`.*, count(comments.obj_id) AS `comments`
FROM `objects`
LEFT JOIN `comments` ON objects.id = comments.obj_id 
WHERE objects.active = 1 
GROUP BY `comments`.`obj_id` 
ORDER BY `objects`.`rating` DESC 
LIMIT 1000

But not all records are returned. Returns 16 rows

A query:

SELECT `objects`.* AS `comments`
FROM `objects`
WHERE objects.active = 1 
ORDER BY `objects`.`rating` DESC 
LIMIT 1000

27 lines

What is my mistake here?

Answer 1

need GROUP BY `objects`.`id`

Answer 2

What if it's straight forward?

SELECT `objects`.*, COUNT(SELECT `id` FROM `comments` WHERE `objects`.`id` = `comments`.`obj_id`) AS `comments`
FROM `objects`
WHERE `objects`.`active` = 1 
ORDER BY `objects`.`rating` DESC 
LIMIT 1000