Запрос MYSQL - Получить последний комментарий, связанный с сообщением
Я пытаюсь получить последние 1 или 2 комментария, связанные с каждым загружаемым сообщением, немного похоже на то, как это делает instagram, поскольку они показывают последние 3 комментария для каждого сообщения, пока я получаю сообщения и количество лайков.
Теперь все, что мне нужно сделать, это выяснить, как получить последние комментарии, не слишком уверен, как к этому подойти, и именно поэтому я надеюсь, что кто-то с гораздо большим опытом сможет мне помочь!
Это мой текущий запрос:
(SELECT
P.uuid,
P.caption,
P.imageHeight,
P.path,
P.date,
U.id,
U.fullname,
U.coverImage,
U.bio,
U.username,
U.profileImage,
coalesce(Activity.LikeCNT,0),
Activity.CurrentUserLiked
FROM USERS AS U
INNER JOIN Posts AS P
ON P.id = U.id
LEFT JOIN (SELECT COUNT(DISTINCT Activity.uuidPost) LikeCNT, Activity.uuidPost, Activity.id, sum(CASE WHEN Activity.id = $id then 1 else 0 end) as CurrentUserLiked
FROM Activity Activity
WHERE type = 'like'
GROUP BY Activity.uuidPost) Activity
ON Activity.uuidPost = P.uuid
AND Activity.id = U.id
WHERE U.id = $id)
UNION
(SELECT
P.uuid,
P.caption,
P.imageHeight,
P.path,
P.date,
U.id,
U.fullname,
U.coverImage,
U.bio,
U.username,
U.profileImage,
coalesce(Activity.LikeCNT,0),
Activity.CurrentUserLiked
FROM Activity AS A
INNER JOIN USERS AS U
ON A.IdOtherUser=U.id
INNER JOIN Posts AS P
ON P.id = U.id
LEFT JOIN (SELECT COUNT(DISTINCT Activity.uuidPost) LikeCNT, Activity.uuidPost, Activity.id, sum(CASE WHEN Activity.id = $id then 1 else 0 end) as CurrentUserLiked
FROM Activity Activity
WHERE type = 'like'
GROUP BY Activity.uuidPost) Activity
ON Activity.uuidPost = P.uuid
AND Activity.id = U.id
WHERE A.id = $id)
ORDER BY date DESC
LIMIT 0, 5
В основном комментарии хранятся в за тем же столом, что и подобные.
Итак, таблица Activity
, затем у меня есть столбец comment
, в котором хранится текст комментария, а затем "тип" равен "комментарий".
Возможно, не очень хорошо объяснено, но я готов попытаться дать как можно больше подробностей!
Если кто-то может помочь, мы будем очень признательны!!
ОБНОВЛЕНИЕ
По этому запросу, заданному https://stackoverflow.com/users/1016435/xqbert В настоящее время я получаю эту ошибку:
Незаконное сочетание параметров сортировки (utf8_general_ci, НЕЯВНЫЙ) и (utf8_unicode_ci, НЕЯВНЫЙ) для операции '='
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
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, date DESC) Com
ON Com.UUIIDPOSt = Posts.UUID
AND row_number <= 2
ORDER BY date DESC
LIMIT 0, 5
Последнее редактирование
Структуры таблиц:
Сообщения
----------------------------------------------------------
| id | int(11) | | not null |
| uuid | varchar(100) | utf8_unicode_ci | not null |
| imageLink | varchar(500) | utf8_unicode_ci | not null |
| date | timestamp | | not null |
----------------------------------------------------------
ПОЛЬЗОВАТЕЛИ
-------------------------------------------------------------
| id | int(11) | | not null |
| username | varchar(100) | utf8_unicode_ci | not null |
| profileImage | varchar(500) | utf8_unicode_ci | not null |
| date | timestamp | | not null |
-------------------------------------------------------------
Деятельность
----------------------------------------------------------
| id | int(11) | | not null |
| uuid | varchar(100) | utf8_unicode_ci | not null |
| uuidPost | varchar(100) | utf8_unicode_ci | not null |
| type | varchar(50) | utf8_unicode_ci | not null |
| commentText | varchar(500) | utf8_unicode_ci | not null |
| date | timestamp | | not null |
----------------------------------------------------------
Вот некоторые примеры, в таблице "Активность" в этом случае "тип" всегда будет равен "комментарию".
Подведение итогов всего и результат желания:
Когда я запрашиваю пользователей сообщения, я хотел бы иметь возможность зайти в таблицу "Активность" и получить последние 2 комментария для каждого сообщения, которое у него есть. Возможно, комментариев не будет, поэтому очевидно, что он вернет 0, возможно, для этого поста может быть 100 комментариев. Но я хочу получить только последние/самые последние 2 комментария.
Примером может быть рассмотрение того, как это делает Instagram. Для каждого сообщения отображаются самые последние комментарии 1, 2 или 3....
Надеюсь, это поможет!
6 answers
Это сообщение об ошибке
Незаконное сочетание параметров сортировки (utf8_general_ci, НЕЯВНЫЙ) и (utf8_unicode_ci, НЕЯВНЫЙ) для операции '='
Обычно связано с определением ваших столбцов и таблиц. Обычно это означает, что по обе стороны знака равенства находятся разные параметры сортировки. Что вам нужно сделать, так это выбрать одно из них и включить это решение в свой запрос.
Проблема сопоставления здесь заключалась в ПЕРЕКРЕСТНОМ СОЕДИНЕНИИ @prev_value, для которого требовалось явные параметры сортировки, которые будут использоваться.
Я также немного изменил логику "номер строки" на одно перекрестное соединение и переместил логику if в крайние точки списка выбора.
Ниже приведены некоторые примеры данных. Образцы данных необходимы для тестирования запросов. Любому, кто попытается ответить на ваш вопрос с помощью рабочих примеров, понадобятся данные. Причина, по которой я включаю его сюда, двоякая.
- чтобы вы поняли любой результат, который я представлю
- так что в будущем когда вы задаете другой вопрос, связанный с SQL, вы понимаете важность предоставления данных. Для нас не только удобнее, чтобы вы это делали. Если спрашивающий предоставит образец данных, то спрашивающий уже поймет это - это не будет изобретением какого-то незнакомца, который посвятил часть своего времени тому, чтобы помочь.
Примеры Данных
Пожалуйста, обратите внимание, что в таблицах отсутствуют некоторые столбцы, только столбцы, указанные в деталях таблицы, были включенный.
В этом примере данных содержится 5 комментариев к одному сообщению (лайки не регистрируются)
CREATE TABLE Posts
(
`id` int,
`uuid` varchar(7) collate utf8_unicode_ci,
`imageLink` varchar(9) collate utf8_unicode_ci,
`date` datetime
);
INSERT INTO Posts(`id`, `uuid`, `imageLink`, `date`)
VALUES
(145, 'abcdefg', 'blah blah', '2016-10-10 00:00:00') ;
CREATE TABLE USERS
(
`id` int,
`username` varchar(15) collate utf8_unicode_ci,
`profileImage` varchar(12) collate utf8_unicode_ci,
`date` datetime
) ;
INSERT INTO USERS(`id`, `username`, `profileImage`, `date`)
VALUES
(145, 'used_by_already', 'blah de blah', '2014-01-03 00:00:00') ;
CREATE TABLE Activity
(
`id` int,
`uuid` varchar(4) collate utf8_unicode_ci,
`uuidPost` varchar(7) collate utf8_unicode_ci,
`type` varchar(40) collate utf8_unicode_ci,
`commentText` varchar(11) collate utf8_unicode_ci, `date` datetime
) ;
INSERT INTO Activity (`id`, `uuid`, `uuidPost`, `type`, `commentText`, `date`)
VALUES
(345, 'a100', 'abcdefg', 'comment', 'lah lha ha', '2016-07-05 00:00:00'),
(456, 'a101', 'abcdefg', 'comment', 'lah lah lah', '2016-07-06 00:00:00'),
(567, 'a102', 'abcdefg', 'comment', 'lha lha ha', '2016-07-07 00:00:00'),
(678, 'a103', 'abcdefg', 'comment', 'ha lah lah', '2016-07-08 00:00:00'),
(789, 'a104', 'abcdefg', 'comment', 'hla lah lah', '2016-07-09 00:00:00') ;
[ Стандартное поведение SQL: 2 строки на запрос Post]
Это был мой первоначальный запрос с некоторыми исправлениями. Я изменил порядок столбцов в списке выбора, чтобы вы могли легко видеть некоторые данные, связанные с комментариями, когда я представлю результаты. Пожалуйста, изучите предоставленные результаты, чтобы вы могли понять, что будет делать запрос. Столбцы, которым предшествует #, не существуют в примеры данных, с которыми я работаю по причинам, которые я уже отмечал.
SELECT
Posts.id
, Posts.uuid
, rcom.uuidPost
, rcom.commentText
, rcom.`date` commentDate
#, Posts.caption
#, Posts.path
, Posts.`date`
, USERS.id
, USERS.username
#, USERS.fullname
, USERS.profileImage
, COALESCE(A.LikeCNT, 0) num_likes
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
LEFT JOIN (
SELECT
@row_num := IF(@prev_value=UUIDPOST,@row_num+1,1) as row_number
, commentText
, uuidPost
, `date`
, @prev_value := UUIDPOST
FROM Activity
CROSS JOIN ( SELECT @row_num := 1, @prev_value := '' collate utf8_unicode_ci ) xy
WHERE type = 'comment'
ORDER BY
uuidPost
, `date` DESC
) rcom ON rcom.uuidPost = Posts.UUID
AND rcom.row_number <= 2
ORDER BY
posts.`date` DESC
;
Смотрите рабочую демонстрацию этого запроса в SQLFiddle
| id | uuid | uuidPost | commentText | date | date | id | username | profileImage | num_likes |
|-----|---------|----------|-------------|------------------------|---------------------------|-----|-----------------|--------------|-----------|
| 145 | abcdefg | abcdefg | hla lah lah | July, 09 2016 00:00:00 | October, 10 2016 00:00:00 | 145 | used_by_already | blah de blah | 0 |
| 145 | abcdefg | abcdefg | ha lah lah | July, 08 2016 00:00:00 | October, 10 2016 00:00:00 | 145 | used_by_already | blah de blah | 0 |
Есть 2 СТРОКИ - как и ожидалось. Одна строка для самого последнего комментария, а другая строка для следующего самого последнего комментария. Это нормальное поведение для SQL, и до тех пор, пока под этим ответом не будет добавлен комментарий, читатели вопроса будут предполагать, что это нормальное поведение будет приемлемым.
В вопросе отсутствует четко сформулированный "ожидаемый результат".
[ Вариант 1: Одна строка на запрос публикации, ДО 2 комментариев, добавлены столбцы]
В комментарии ниже было показано, что вам не нужно 2 строки на пост, и это было бы легко исправить. Ну, это вроде как просто, НО есть варианты, и варианты диктуются пользователем в виде требований. ЕСЛИ бы вопрос имел "ожидаемый результат", то мы бы знали, какой вариант выбрать. Тем не менее, вот один из вариантов
SELECT
Posts.id
, Posts.uuid
, max(case when rcom.row_number = 1 then rcom.commentText end) Comment_one
, max(case when rcom.row_number = 2 then rcom.commentText end) Comment_two
#, Posts.caption
#, Posts.path
, Posts.`date`
, USERS.id
, USERS.username
#, USERS.fullname
, USERS.profileImage
, COALESCE(A.LikeCNT, 0) num_likes
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
LEFT JOIN (
SELECT
@row_num := IF(@prev_value=UUIDPOST,@row_num+1,1) as row_number
, commentText
, uuidPost
, `date`
, @prev_value := UUIDPOST
FROM Activity
CROSS JOIN ( SELECT @row_num := 1, @prev_value := '' collate utf8_unicode_ci ) xy
WHERE type = 'comment'
ORDER BY
uuidPost
, `date` DESC
) rcom ON rcom.uuidPost = Posts.UUID
AND rcom.row_number <= 2
GROUP BY
Posts.id
, Posts.uuid
#, Posts.caption
#, Posts.path
, Posts.`date`
, USERS.id
, USERS.username
#, USERS.fullname
, USERS.profileImage
, COALESCE(A.LikeCNT, 0)
ORDER BY
posts.`date` DESC
;
Смотрите второй запрос, работающий в SQLFiddle
| id | uuid | Comment_one | Comment_two | date | id | username | profileImage | num_likes |
|-----|---------|-------------|-------------|---------------------------|-----|-----------------|--------------|-----------|
| 145 | abcdefg | hla lah lah | ha lah lah | October, 10 2016 00:00:00 | 145 | used_by_already | blah de blah | 0 |
** Вариант 2, объединить самые последние комментарии в один список, разделенный запятыми**
SELECT
Posts.id
, Posts.uuid
, group_concat(rcom.commentText) Comments_two_concatenated
#, Posts.caption
#, Posts.path
, Posts.`date`
, USERS.id
, USERS.username
#, USERS.fullname
, USERS.profileImage
, COALESCE(A.LikeCNT, 0) num_likes
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
LEFT JOIN (
SELECT
@row_num := IF(@prev_value=UUIDPOST,@row_num+1,1) as row_number
, commentText
, uuidPost
, `date`
, @prev_value := UUIDPOST
FROM Activity
CROSS JOIN ( SELECT @row_num := 1, @prev_value := '' collate utf8_unicode_ci ) xy
WHERE type = 'comment'
ORDER BY
uuidPost
, `date` DESC
) rcom ON rcom.uuidPost = Posts.UUID
AND rcom.row_number <= 2
GROUP BY
Posts.id
, Posts.uuid
#, Posts.caption
#, Posts.path
, Posts.`date`
, USERS.id
, USERS.username
#, USERS.fullname
, USERS.profileImage
, COALESCE(A.LikeCNT, 0)
ORDER BY
posts.`date` DESC
Смотрите этот третий запрос, работающий в SQLFiddle
| id | uuid | Comments_two_concatenated | date | id | username | profileImage | num_likes |
|-----|---------|---------------------------|---------------------------|-----|-----------------|--------------|-----------|
| 145 | abcdefg | hla lah lah,ha lah lah | October, 10 2016 00:00:00 | 145 | used_by_already | blah de blah | 0 |
**Краткое описание**
Я представил 3 запроса, каждый из которых показывает только 2 самые последние комментарии, но каждый запрос делает это по-своему. Первый запрос (поведение по умолчанию) будет отображать 2 строки для каждой записи. Вариант 2 добавляет столбец, но удаляет вторую строку. Вариант 3 объединяет 2 самых последних комментария.
Пожалуйста, обратите внимание, что:
- В вопросе отсутствуют определения таблиц, охватывающие все столбцы
- В вопросе отсутствуют какие-либо примеры данных, что затрудняет понимание вами любых результатов, представленных здесь, но также затрудняет для нас подготовка решений
- В вопросе также отсутствует определенный "ожидаемый результат" (желаемый результат), и это привело к дальнейшей сложности при ответе
Я надеюсь, что дополнительная предоставленная информация будет полезна, и что к настоящему времени вы также знаете, что для SQL нормально представлять данные в виде нескольких строк. Если вы не хотите такого нормального поведения, пожалуйста, уточните, чего вы действительно хотите в своем вопросе.
Постскриптум. Чтобы включить еще один подзапрос для "следует" вы можете использовать подзапрос, аналогичный тому, который у вас уже есть. Он может быть добавлен до или после этого подзапроса. Вы также можете увидеть его в использовании в sqlfiddle здесь
LEFT JOIN (
SELECT
COUNT(*) FollowCNT
, IdOtherUser
FROM Activity
WHERE type = 'Follow'
GROUP BY
IdOtherUser
) F ON USERS.id = F.IdOtherUser
Хотя добавление другого подзапроса может решить ваше желание получить больше информации, общий запрос может замедлиться пропорционально росту ваших данных. Как только вы определитесь с функциональностью, которая вам действительно нужна, возможно, стоит подумать о том, какие индексы вам нужны в этих таблицах. (Я полагаю, вам было бы рекомендовано обратиться за этим советом отдельно, и если вы это сделаете, убедитесь, что вы включили 1. полный DDL ваших таблиц и 2. план объяснения запроса.)
Я немного запутался в вашем запросе, но если вы хотите загрузить данные для нескольких сообщений одновременно, не стоит включать данные комментариев в первый запрос, так как вы бы включили все данные о публикации и пользователе публикации несколько раз. Вам следует выполнить другой запрос, который соединял бы сообщения с комментариями. Что-то вроде:
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, которые имеют дубликаты
Если вы не уверены в этом (хотя из-за поля даты в таблице это должно быть невозможно).
НЕПРОВЕРЕННЫЙ: Я бы рекомендовал собрать SQL-скрипку с некоторыми образцами данных и существующей структурой таблиц, показывающей проблему; таким образом, мы могли бы поиграть с ответами и обеспечить функциональность вашей схемы.
Поэтому мы используем переменные для имитации функции окна (например, номер строки)
В этом случае @Row_num и @prev_value. @Row_number отслеживает текущую строку для каждого сообщения (поскольку в одном сообщении может быть много комментариев) затем, когда встречается новый идентификатор записи (UUIDPOST?), переменная row_num сбрасывается на 1. Когда текущая запись UUIDPOST совпадает с переменной @prev_value, мы просто увеличиваем строку на 1.
Этот метод позволяет нам присваивать номер строки на основе даты или идентификатора действия в порядке убывания. Поскольку каждое перекрестное соединение приводит только к 1 записи, мы не вызываем появления повторяющихся записей. Однако, поскольку мы затем ограничиваемся номером строки
Это предполагает, что сообщения относятся к пользователям как много к одному, то есть в сообщении может быть только 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
Нам пришлось поместить и номер строки
Кроме того, нам, вероятно, следует взглянуть на поле "комментарий", чтобы убедиться, что оно не пустое или пустое, но давайте сначала убедимся, что это работает.
Этот тип комментариев публиковался много раз, и попытка получить "последнюю информацию для каждого" всегда кажется камнем преткновения и кошмаром присоединения/подзапроса для большинства.
Особенно для веб-интерфейса, вам может быть лучше прикрепить столбец (или 2 или 3) к одной таблице, которая является вашей активной таблицей "сообщений", такой как Latest1, Latest2, Latest3.
Затем, с помощью вставки в таблицу комментариев, установите триггер вставки в своей таблице, чтобы обновить основную запись с помощью новейшего идентификатора. Тогда у вас всегда будет этот идентификатор на столе без каких-либо вложенных соединений. Теперь, как вы упомянули, вы можете захотеть иметь последние 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-вставок и автоматической штамповки с помощью триггера для обновления основной таблицы post. Затем, запросив таблицу сообщений, вы можете увидеть, как самые последние автоматически перемещаются на первую, вторую и третью позиции. Наконец, соединение, показывающее, как извлекать все данные из каждого "действия после публикации"
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- и присоединяюсь к сообщениям, потому что мне это кажется более логичным. Затем я получаю все лайки за пост с подзапросом.
Теперь вместо группировки и ограничения размера группы мы объединяем только те значения, которые хотим, ограничивая количество дат после даты, на которую мы сейчас смотрим.
ВНУТРЕННЯЯ активность ПРИСОЕДИНЕНИЯ, если вы хотите показывать сообщения только с хотя бы одним комментарий.
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
Тем не менее, редизайн, вероятно, был бы лучшим, также с точки зрения производительности (действие скоро будет содержать много записей, и их всегда нужно фильтровать по нужному типу). Таблица пользователей в порядке с автоматически увеличиваемым идентификатором и в качестве первичного ключа. Для сообщений я бы также добавил автоматически увеличиваемый идентификатор в качестве первичного ключа и идентификатор пользователя в качестве внешнего ключа (вы также можете решить, что делать при удалении, например, с помощью каскада все его сообщения также будут удалены автоматически).
Для комментариев и лайков вы можете создать отдельные таблицы с двумя внешними ключами user_id и post_id (простой пример, например, вам могут нравиться только посты и ничего больше, но если не так много разных лайков, все равно было бы неплохо создать post_likes и несколько других..._ таблицы лайков, вы должны подумать о том, как обычно запрашиваются эти данные, если эти лайки в основном независимы друг от друга, это, вероятно, хороший выбор).