select
*,
sum(ratingTable.rating) as rating
from myTable
left join ratingTable (logic here)
group by myTable.id
order by rating desc

select
*,
sum(ratingTable.rating) as rating
from myTable
left join ratingTable (logic here)
group by myTable.id
order by rating desc

SELECT authorsTable.id, authorsTable.name,
    (
        SELECT json_agg(sub_articles)
        FROM (
            SELECT id, name, content
            FROM articlesTable
            -- Looking for a way to add this clause, referencing the outer table in the subquery we inject like in your example
            WHERE articlesTable.author_id = authorsTable.id
        ) AS sub_articles
    ) AS articles
FROM authorsTable;

SELECT authorsTable.id, authorsTable.name,
    (
        SELECT json_agg(sub_articles)
        FROM (
            SELECT id, name, content
            FROM articlesTable
            -- Looking for a way to add this clause, referencing the outer table in the subquery we inject like in your example
            WHERE articlesTable.author_id = authorsTable.id
        ) AS sub_articles
    ) AS articles
FROM authorsTable;