join subquery return multiple rows

I think, share_count and vote,user_voted,user_vote should be calculated with subquery.
Then, no need for group by in $comments_table, and rows can be selected with all needed columns.

Try this

set @d:=5;
set @s:='foo';
  
SELECT mt.media_id, mt.title, sht.share_count, ct.comment -- ct.*
 FROM $grouped_media_table mt   
LEFT JOIN (
  SELECT media_id,title
     ,SUM(DISTINCT sht.share) as share_count 
  FROM $share_table sht
  WHERE sht.media_id = @d AND sht.title = @s
  GROUP BY media_id,title
  ) sht ON sht.media_id = mt.media_id AND sht.title = mt.title
LEFT JOIN (
  SELECT ct.id, ct.media_id, ct.title, comment 
   ,ct.comment_parent_id, ct.reported, ct.user_id, ct.user_display_name, ct.avatar
   ,ct.c_date
   ,vote,user_voted,user_vote
  FROM $comments_table as ct
  LEFT JOIN lateral (
      SELECT vt.comment_id,SUM(vt.vote) AS vote, 
           MAX(vt.user_id = @d) AS user_voted,
           MAX(CASE WHEN vt.user_id = @d THEN vote END) AS user_vote
      FROM $votes_table vt
      WHERE ct.id = vt.comment_id
      group by vt.comment_id
    ) vt on ct.id = vt.comment_id 
  WHERE ct.media_id = @d AND ct.title=@s 
) as ct ON ct.media_id = mt.media_id AND ct.title = mt.title
 WHERE mt.media_id = @d AND mt.title = @s 
 ORDER BY ct.c_date DESC

for this example used %d -> @d, %s -> @s.

fiddle
or else

Theory

As a general (but admittedly simplistic, sometimes exception will prevail) rule, do never use more than one 1..n relation in a select.

1. As you correctly noticed, SQL will duplicate your values. This is by design, SQL ought to be deterministic so to get understandable the queries can be decomposed in steps, each step executing in order. The joins, then the wheres, then the window functions, then the group bys, and so on.

The interesting property of this law is that you can decompose the RDBMS results' building by hand, omitting the later steps if you're trying to diagnose the previous ones.
So if we skip the grouping, staying at the join step, you can imagine what happens when you join a media with 2 shares (let's call them A and B) and 3 comments (dated 1st day, 2nd day, 3rd day): this makes two 1..n relations, and SQL will simply join each possible row of share to each possible row of comment, as long as they're related to the same media:

• Some aggregate functions have no problems with duplicates (max(x, x) is always x),
• some can use distinct to avoid duplicates (count(share) returns 6 but count(distinct share) will find 2),
• but some can nothing (if each comment comes with one vote, sum(vote) returns 6 due to the duplicates, but sum(distinct vote) on the contrary will return 1: you'll never get the intended 3 after SQL having merged too much, considering that all votes at 1 should be counted as one unique vote).

2. From a performance point of view, even if you can mitigate some side effects (distinct), you're still asking your RDBMS to have each c_date stored twice and each share three times in its internal, intermediate resultset between the where and the group by.

Resolving it

So you have to get the maximum of 1..1 relations in each query, and keep only 1..n relation for the final query.

Good news: that's exactly what you intend, with (at the end) each 1 comment with its n votes.

There just has to get the intermediate tables on this "one 1..n relation per step" model.

Note that if we consider counting aggregates of things, we fall back in the (reinsuring) 1..1 case:
although we have n shares per media, we have only 1 count of shares per media.

The tool that eases writing (and mostly reading!) those step-by-step queries (first generating a media_shares pseudo-table which is 1..1 against media_id, and so on), apart from subqueries, is Common Table Expression, or "with expressions":

with
  -- 1 media_id .. n shares → 1 media_id .. 1 count of shares
  media_shares as (select media_id, … group by media_id),
  [others 1..n → 1..1]
select … from media_shares ms join … on ms.media_id = …);

Let's practice

WITH
  -- 1 media_id .. n shares → 1 media_id .. 1 count of shares
  media_shares AS (SELECT media_id, title, SUM(share) share_count FROM $share_table GROUP BY media_id, title)
  -- Here we could have another CTE doing:
  -- 1 comment.id .. n votes → 1 comment.id .. 1 number of votes
  -- However, we now have sufficiently reduced our relations, because our final query will be centered on comments, not on media, and from each comment we have:
  -- - 1 comment .. n votes
  -- - 1 comment .. 1 media with its count of shares
  -- which make one 1..n + one 1..1, which means WE HAVE REACHED OUR GOAL OF ONLY 1..n RELATION:
  -- Green light for one query!
SELECT
  -- 1..1 columns from mt
  mt.media_id, mt.title, share_count,
  -- 1..1 columns from ct (the GROUP BY ct.id is sufficient as id is its primary key)
  ct.id, ct.media_id, ct.title, comment, ct.comment_parent_id, ct.reported, ct.user_id, ct.user_display_name, ct.avatar, ct.c_date,
  -- 1..n columns from vt
  SUM(vt.vote) AS vote,
  MAX(vt.user_id = 1) AS user_voted,
  MAX(CASE WHEN vt.user_id = 1 THEN vote END) AS user_vote
FROM $comments_table as ct
JOIN media_shares mt ON ct.media_id = mt.media_id AND ct.title = mt.title
LEFT JOIN $votes_table vt on ct.id = vt.comment_id
WHERE ct.media_id = 5 AND ct.title='foo'
GROUP BY ct.id
ORDER BY ct.c_date DESC;

(you will find the full example running in a fiddle)

By the way

I find it strange that you have to replicate the media's title over the comments, the shares and so on. Perhaps you should renormalize your database, to have it stored in one place, and all other tables having only media_id as a foreign key.

SELECT
    mt.media_id,
    mt.title,
    (SELECT SUM(sht.share) FROM $share_table sht WHERE sht.media_id = mt.media_id AND sht.title = mt.title) AS share_count,
    COALESCE(
      CONCAT('[',
        GROUP_CONCAT(
            JSON_OBJECT(
                'id', cwv.id,
                'comment', cwv.comment,
                'comment_parent_id', cwv.comment_parent_id,
                'reported', cwv.reported,
                'user_id', cwv.user_id,
                'user_display_name', cwv.user_display_name,
                'avatar', cwv.avatar,
                'c_date', cwv.c_date,
                'vote', cwv.vote,
                'user_voted', cwv.user_voted,
                'user_vote', cwv.user_vote
            )
            ORDER BY cwv.c_date DESC SEPARATOR ','
        )
      ,']')
    , '[]') AS comments
FROM
    $grouped_media_table mt
LEFT JOIN (
    SELECT
        ct.media_id,
        ct.title,
        ct.id,
        ct.comment,
        ct.comment_parent_id,
        ct.reported,
        ct.user_id,
        ct.user_display_name,
        ct.avatar,
        ct.c_date,
        IFNULL(SUM(vt.vote), 0) AS vote,
        MAX(CASE WHEN vt.user_id = %id THEN 1 ELSE 0 END) AS user_voted,
        MAX(CASE WHEN vt.user_id = %id THEN vt.vote END) AS user_vote
    FROM
        $comments_table ct
    LEFT JOIN
        $votes_table vt ON ct.id = vt.comment_id
    GROUP BY
        ct.id
) AS cwv ON mt.media_id = cwv.media_id AND mt.title = cwv.title
WHERE
    mt.media_id = %d AND mt.title = %s
GROUP BY
    mt.media_id, mt.title;