Всем привет! Столкнулся с проблемой во время создания социальной сети. Уменя есть таблица Постов, Картинок для постов, Комментариев, Лайков и Пользавателей. У таблицы Постов есть поле repostedTo для связи один ко многим на саму себя. Нужно получить ленту, что приводит к вот такому SQL запросу:
SELECT posts.*,
u."nickName",
u."firstName",
u."lastName",
u.photo,
pI.image AS image,
pI."isMain" AS "isMain",
"repostedPost".id AS "rpId",
"repostedPost"."userId" AS "rpUserId",
"repostedPost"."createdAt" AS "rpCreatedAt",
"repostedPost".type AS "rpType",
"repostedPost".text AS "rpText",
rpI.image AS "rpImage",
rpI."isMain" AS "rpIsMainImage",
ur."firstName" AS "urFirstName",
ur."lastName" AS "urLastName",
ur."nickName" AS "urNickName",
ur."photo" AS "urPhoto",
COUNT(DISTINCT rp.id) AS "repostsCount",
COUNT(DISTINCT likes."userId") AS "likesCount",
COUNT(DISTINCT comments.id) AS "commentsCount"
FROM (SELECT id, "userId", "createdAt", "repostTo", type, text
FROM posts
WHERE ${condition}
ORDER BY "createdAt" ASC
LIMIT ? OFFSET ?) AS posts
LEFT JOIN users AS u on u.id = posts."userId"
LEFT JOIN likes AS "likes" on posts.id = likes."postId"
LEFT JOIN comments AS "comments" on posts.id = comments."postId"
LEFT JOIN posts AS rp on posts.id = rp."repostTo"
LEFT JOIN "postImages" pI on posts.id = pI."postId"
LEFT JOIN posts "repostedPost" on "repostedPost".id = posts."repostTo"
LEFT JOIN "postImages" rpI on "repostedPost".id = rpI."postId"
LEFT JOIN users ur on "repostedPost"."userId" = ur.id
GROUP BY pI.image, pI."isMain", posts."repostTo", posts.id, posts."userId", posts."createdAt", posts.type, posts.text,
u."nickName",
u."firstName",
u."lastName",
u.photo, "repostedPost".id,
"repostedPost"."userId",
"repostedPost"."createdAt",
"repostedPost"."repostTo",
"repostedPost".type,
"repostedPost".text, ur."firstName",
ur."lastName",
ur."nickName",
ur."photo", rpI.image,
rpI."isMain";
Нужен совет, как можно это сделать лучше?
