MYSQL Query — Получить последний комментарий, связанный с постом

Я немного растерялся в вашем запросе, но если вы хотите загрузить данные для нескольких постов одновременно, не стоит включать данные комментариев в первый запрос, так как вы включаете все данные о постах и ​​постах пользователя несколько раз , Вы должны выполнить другой запрос, который связал бы сообщения с комментариями. Что-то вроде:

SELECT
A.UUIDPost,
C.username,
C.profileImage,
B.Comment,
B.[DateField]
FROM Posts A JOIN
Activities B ON A.uuid = B.UUIDPost JOIN
Users C ON B.[UserId] = C.id

и использовать эти данные для отображения ваших комментариев с комментариями, идентификатором пользователя, именем, изображением и т. д.

Чтобы получить только 3 комментария к сообщению, вы можете посмотреть в этом сообщении:

Выберите лучшие 3 значения из каждой группы в таблице с SQL

если вы уверены, что в таблице комментариев или этой записи не будет повторяющихся строк:

Как выбрать лучшие 3 значения из каждой группы в таблице с SQL, которые имеют дубликаты

если вы не уверены в этом (хотя из-за DateField в таблице это не должно быть возможно).

UNTESTED: Я бы порекомендовал собрать SQL скрипка с некоторыми примерами данных и существующей структурой таблицы, показывающей проблему; таким образом, мы могли бы поиграться с ответами и обеспечить функциональность с вашей схемой.

Поэтому мы используем переменные для имитации оконной функции (например, row_number)

в этом случае @Row_num и @prev_Value. @Row_number отслеживает текущую строку для каждого сообщения (поскольку в одном сообщении может быть много комментариев), а затем, когда встречается новый идентификатор сообщения (UUIDPOST?), Переменная row_num сбрасывается в 1. Когда текущие записи UUIDPOST соответствуют переменная @prev_Value, мы просто увеличиваем строку на 1.

Этот метод позволяет нам назначать номер строки в порядке убывания даты или идентификатора активности. Поскольку каждое перекрестное объединение приводит только к 1 записи, мы не приводим к появлению повторяющихся записей. Тем не менее, так как мы тогда ограничиваем по row_number < = 2 мы получаем только два последних комментария в нашем недавно добавленном левом соединении.

Это предполагает, что сообщения имеют отношение «много к одному», то есть пост может иметь только 1 пользователя.

Примерно так: хотя я не уверен насчет последнего левого соединения, мне нужно лучше понять структуру таблицы действий, таким образом, комментарий к исходному вопросу.

SELECT Posts.id,
Posts.uuid,
Posts.caption,
Posts.path,
Posts.date,
USERS.id,
USERS.username,
USERS.fullname,
USERS.profileImage,
coalesce(A.LikeCNT,0)
com.comment
FROM Posts
INNER JOIN USERS
ON Posts.id = 145
AND USERS.id = 145
LEFT JOIN (SELECT COUNT(A.uuidPost) LikeCNT, A.UUIDPost
FROM Activity A
WHERE type =  'like'
GROUP BY A.UUIDPOST) A
on A.UUIDPost=Posts.uuid--This join simulates row_Number() over (partition by PostID, order by activityID desc)  (Nice article [here](http://preilly.me/2011/11/11/mysql-row_number/) several other examples exist on SO already.
--Meaning.... Generate a row number for each activity from 1-X restarting at 1 for each new post but start numbering at the newest activityID)

LEFT JOIN (SELECT comment, UUIDPOST, @row_num := IF(@prev_value=UUIDPOST,@row_num+1,1) as row_number,@prev_value := UUIDPOST

FROM ACTIVITY
CROSS JOIN (SELECT @row_num := 1) x
CROSS JOIN (SELECT @prev_value := '') y
WHERE type = 'comment'
ORDER BY UUIDPOST, --Some date or ID desc) Com
on Com.UUIIDPOSt = Posts.UUID
and row_number < = 2-- Now since we have a row_number restarting at 1 for each new post, simply return only the 1st two rows.

ORDER BY date DESC
LIMIT 0, 5

мы должны были поставить и row_number < = 2 на само соединение. Если бы он был помещен в пункт where, вы бы потеряли эти посты без каких-либо комментариев, которые, я думаю, вам все еще нужны.

Кроме того, нам, вероятно, следует взглянуть на поле «Комментарий», чтобы убедиться, что оно не пустое и не пустое, но сначала нужно убедиться, что это работает.

Этот тип комментариев был опубликован много раз, и попытка получить «последний для каждого» всегда кажется камнем преткновения и кошмаром для присоединения / подзапроса для большинства.

Специально для веб-интерфейса вам может быть лучше привязать столбец (или 2 или 3) к одной таблице, которая является вашей активной таблицей «сообщений», например «Последние1», «Последние2», «Последние3».

Затем с помощью вставки в таблицу комментариев добавьте триггер вставки в вашу таблицу, чтобы обновить основной пост новым идентификатором. Тогда у вас всегда есть этот идентификатор на столе без каких-либо вложенных соединений. Теперь, как вы упомянули, вы, возможно, захотите иметь последние 2 или 3 идентификатора, затем добавьте 3 примера столбцов и сделайте так, чтобы ваш триггер вставки к деталям комментария к записи обновлял основную таблицу сообщений примерно так:

update PrimaryPostTable
set Latest3 = Latest2,
Latest2 = Latest1,
Latest1 = NewDetailCommentID
where PostID = PostIDFromTheInsertedDetail

Это должно быть оформлено в правильный триггер под MySQL, но должно быть достаточно простым для реализации. Вы можете заполнить список последним 1, затем, когда появятся новые сообщения, он автоматически свернет самые последние в их 1, 2, 3 места. Наконец ваш запрос может быть упрощен до чего-то вроде

Select
P.PostID,
P.TopicDescription,
PD1.WhateverDetail as LatestDetail1,
PD2.WhateverDetail as LatestDetail2,
PD3.WhateverDetail as LatestDetail3
from
Posts P
LEFT JOIN PostDetail PD1
on P.Latest1 = PD1.PostDetailID
LEFT JOIN PostDetail PD2
on P.Latest2 = PD2.PostDetailID
LEFT JOIN PostDetail PD3
on P.Latest3 = PD3.PostDetailID
where
whateverCondition

Денормализация данных обычно НЕ желательна. Тем не менее, в таких случаях, это отличный способ получения этих «последних» записей в запросе типа «для каждого». Удачи.

Вот полностью рабочий пример в MySQL, поэтому вы можете увидеть таблицы и результаты sql-вставок и автоматическую печать через триггер для обновления основной таблицы сообщений. Затем, выполнив запрос к таблице записей, вы увидите, как самые последние автоматически переходят в первую, вторую и третью позиции. И, наконец, объединение, показывающее, как получить все данные из каждой «публикации активности»

CREATE TABLE Posts
(   id int,
uuid varchar(7),
imageLink varchar(9),
`date` datetime,
ActivityID1 int null,
ActivityID2 int null,
ActivityID3 int null,
PRIMARY KEY (id)
);

CREATE TABLE Activity
(   id int,
postid int,
`type` varchar(40) collate utf8_unicode_ci,
commentText varchar(20) collate utf8_unicode_ci,
`date` datetime,
PRIMARY KEY (id)
);

DELIMITER //

CREATE TRIGGER ActivityRecAdded
AFTER INSERT ON Activity FOR EACH ROW
BEGIN
Update Posts
set ActivityID3 = ActivityID2,
ActivityID2 = ActivityID1,
ActivityID1 = NEW.ID
where
ID = NEW.POSTID;

END; //

DELIMITER ;INSERT INTO Posts
(id, uuid, imageLink, `date`)
VALUES
(123, 'test1', 'blah', '2016-10-26 00:00:00');

INSERT INTO Posts
(id, uuid, imageLink, `date`)
VALUES
(125, 'test2', 'blah 2', '2016-10-26 00:00:00');INSERT INTO Activity
(id, postid, `type`, `commentText`, `date`)
VALUES
(789, 123, 'type1', 'any comment', '2016-10-26 00:00:00'),
(821, 125, 'type2', 'another comment', '2016-10-26 00:00:00'),
(824, 125, 'type3', 'third comment', '2016-10-27 00:00:00'),
(912, 123, 'typeAB', 'comment', '2016-10-27 00:00:00');

-- See the results after the insert and the triggers.
-- you will see that the post table has been updated with the
-- most recent
-- activity post ID=912 in position Posts.Activity1
-- activity post ID=789 in position Posts.Activity2
-- no value in position Posts.Activity3
select * from Posts;

-- NOW, insert two more records for post ID = 123.
-- you will see the shift of ActivityIDs adjusted
INSERT INTO Activity
(id, postid, `type`, `commentText`, `date`)
VALUES
(931, 123, 'type1', 'any comment', '2016-10-28 00:00:00'),
(948, 123, 'newest', 'blah', '2016-10-29 00:00:00');

-- See the results after the insert and the triggers.
-- you will see that the post table has been updated with the
-- most recent
-- activity post ID=948 in position Posts.Activity1
-- activity post ID=931 in position Posts.Activity2
-- activity post ID=912 in position Posts.Activity3
-- notice the FIRST activity post 789 is not there as
-- anything AFTER the 4th entry, it got pushed away.
select * from Posts;

-- Finally, query the data to get the most recent 3 items for each post.
select
p.id,
p.uuid,
p.imageLink,
p.`date`,
A1.id NewestActivityPostID,
A1.`type` NewestType,
A1.`date` NewestDate,
A2.id SecondActivityPostID,
A2.`type` SecondType,
A2.`date` SecondDate,
A3.id ThirdActivityPostID,
A3.`type` ThirdType,
A3.`date` ThirdDate
from
Posts p
left join Activity A1
on p.ActivityID1 = A1.ID
left join Activity A2
on p.ActivityID2 = A2.ID
left join Activity A3
on p.ActivityID3 = A3.ID;

Вы можете создать тестовую базу данных, чтобы не повредить свою, чтобы увидеть этот пример.

Это, вероятно, избавит от недопустимого сочетания параметров сортировки … Просто после установления соединения выполните этот запрос:

SET NAMES utf8 COLLATE utf8_unicode_ci;

Для вопроса о «последних 2», пожалуйста, используйте инструмент командной строки mysql и запустите SHOW CREATE TABLE Posts и предоставить вывод. (То же самое относится и к другим соответствующим таблицам.) Phpmyadmin (и другие пользовательские интерфейсы) могут выполнять запрос, не обращаясь к командной строке.

Вы можете получить довольно простой запрос, используя подзапросы. Сначала я указываю пользователя в предложении where и присоединяюсь к сообщениям, потому что это кажется мне более логичным. Затем я получаю все лайки за пост с подзапросом.

Теперь вместо того, чтобы группировать и ограничивать размер группы, мы объединяем только те значения, которые нам нужны, ограничивая количество дат после даты, которую мы в данный момент просматриваем.

INNER JOIN Активность, если вы хотите показывать сообщения хотя бы с одним комментарием.

SELECT
u.id,
u.username,
u.fullname,
u.profileImage,
p.uuid,
p.caption,
p.path,
p.date,
(SELECT COUNT(*) FROM Activity v WHERE v.uuidPost = p.uuidPost AND v.type = 'like') likes,
a.commentText,
a.date
FROM
Users u INNER JOIN
Posts p ON p.id = u.id LEFT JOIN
Activity a ON a.uuid = p.uuid AND a.type = 'comment' AND 2 > (
SELECT COUNT(*) FROM Activity v
WHERE v.uuid = p.uuid AND v.type = 'comment' AND v.date > a.date)
WHERE
u.id = 145

Тем не менее, реструктуризация, вероятно, будет лучшей, в том числе и с точки зрения производительности (Activity скоро будет содержать много записей, и их всегда нужно будет фильтровать для нужного типа). Пользовательская таблица в порядке с идентификатором, автоматически увеличивающимся и в качестве первичного ключа. Для постов я бы также добавил автоматически увеличенный идентификатор в качестве первичного ключа и user_id в качестве внешнего ключа (вы также можете решить, что делать при удалении, например, с помощью каскада, все его сообщения также будут удаляться автоматически).

Для комментариев и лайков вы можете создавать отдельные таблицы с двумя внешними ключами user_id и post_id (простой пример, например, вы можете любить только посты и ничего больше, но если не так много разных лайков, все равно было бы хорошо создать a post_likes и несколько других …_ таблиц лайков, вы должны подумать о том, как обычно запрашиваются эти данные, если эти лайки в основном независимы друг от друга, это, вероятно, хороший выбор).