No ID result with left join

[Botcharov]

I have two tables: news and reactions. My goal is to show all the newsitems on a page with the information how many reactions there are. At this moment it is working when there is one or more reactions. But when there are no reactions I won't get the news_id to link to the newsitem. Beneath my query:

 

function getAllNews($limit) {

$query = "
SELECT
n.news_id, n.head, n.date n.news n.text, COUNT(r.news_reactions_id) AS countReactions, r.name, r.reactionDate, r.message, r.news_id
FROM
news n
LEFT JOIN
news_reactions r
ON
r.news_id = n.news_id
GROUP BY
n.news_id
LIMIT
:limit
";

$stmt = $this->db->prepare($query);
$stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
$stmt->execute();
$array = $stmt->fetchAll();

return $array;
}

 

I hope someone can help me through.

[Mchl]

Try

SELECT
n.news_id, n.head, n.date n.news n.text, r.countReactions , r.name, r.reactionDate, r.message, r.news_id
FROM
news n
LEFT JOIN
(SELECT name, reactionDate, message, news_id, COUNT(news_reactions_id) AS countReactions FROM news_reactions GROUP BY news_ID) AS r
ON
r.news_id = n.news_id
LIMIT
:limit

[Botcharov]

Try

SELECT
n.news_id, n.head, n.date n.news n.text, r.countReactions , r.name, r.reactionDate, r.message, r.news_id
FROM
news n
LEFT JOIN
(SELECT name, reactionDate, message, news_id, COUNT(news_reactions_id) AS countReactions FROM news_reactions GROUP BY news_ID) AS r
ON
r.news_id = n.news_id
LIMIT
:limit

 

Thx Mchi for your reaction but it didn't result with the reaction part. It's not giving the result of the news_reactions table. Any idea how to solve?