[SQL-MYSQL] contare il numero dei commenti in una query.

[felixaf21]

Esattamente!!

ho scritto una cosa del genere e pare che funziona...

SELECT 
`articles`.`id` AS id, 
`articles`.`title` AS title, 
`articles`.`abstract` AS abstract, 
`articles`.`data_publication` AS data_publication, 
`articles`.`city_id` AS city_id, 
`articles`.`gallery_id` AS gallery_id, 
`articles`.`user_id` AS user_id, 
`articles`.`image` AS image, 

`users`.`id` AS user_id, 
`users`.`firstname` AS firstname, 
`users`.`lastname` AS lastname, 

(
    SELECT COUNT(comment.id)
    FROM
       comment
   WHERE
      comment.article_id = articles.id
   AND
      comment.status = 1
 ) AS count_comments

FROM (`articles`)
JOIN `users` ON `users`.`id` = `articles`.`user_id`
ORDER BY `articles`.`id` DESC
LIMIT 32

potrebbe andar bene secondo voi??

[bomberdini]

Esattamente!!

ho scritto una cosa del genere e pare che funziona...

SELECT 
`articles`.`id` AS id, 
`articles`.`title` AS title, 
`articles`.`abstract` AS abstract, 
`articles`.`data_publication` AS data_publication, 
`articles`.`city_id` AS city_id, 
`articles`.`gallery_id` AS gallery_id, 
`articles`.`user_id` AS user_id, 
`articles`.`image` AS image, 

`users`.`id` AS user_id, 
`users`.`firstname` AS firstname, 
`users`.`lastname` AS lastname, 

(
    SELECT COUNT(comment.id)
    FROM
       comment
   WHERE
      comment.article_id = articles.id
   AND
      comment.status = 1
 ) AS count_comments

FROM (`articles`)
JOIN `users` ON `users`.`id` = `articles`.`user_id`
ORDER BY `articles`.`id` DESC
LIMIT 32

potrebbe andar bene secondo voi??

se funziona va bene. Anche se mi sembra troppo articolata.

Per fare quello che interessa a te va bene una query tipo questa:

SELECT COUNT(c.id) a.id
FROM comment c JOIN articles a on a.id = c.article_id
GROUP BY a.id