Как сделать правильно запрос на связанные таблицы группировка и сортировка?

Question

[Руслан Абсалямов]

У меня есть таблица комнаты ticket_themes и таблица сообщение ticket_message. Мне нужно получить комнату и последнее сообщение в ней.
Мой пример не работает, я не пойму как сделать правильно

SELECT * FROM ticket_themes ticket LEFT JOIN ticket_message tm on ticket.id = tm.theme_id WHERE ticket.user_id = 69625 ORDER BY tm.created_at DESC

Если в этом случае добавлять GROUP BY на id комнаты, то получаю ошибку

[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY ticket.id' at line 1

Как сделать правильно запрос

Comments

[Максим Федоров]

id комнаты

что за комната?

[Руслан Абсалямов]

Максим Федоров, Это по типу чата. Комната это где общается обычно пользователь и админ

[Максим Федоров]

Руслан Абсалямов, круто

[Максим Федоров]

а теперь предоставьте схему БД и название полей, по которым нужно что-либо найти

[Руслан Абсалямов]

Максим Федоров ticket_themes
id(primary_key int), user_id(int), admin_id(int), name(varchar255), status(int), created_at(int), closed_at(int)

ticket_message
id(primary key int), theme_id(int), author_id(int), text(text), created_at(int), viewed_at(int), admin_id(int)
ticket_message_idfk_1 FOREIGN_KEY (theme_id) REFERECES ticket_themes (id) ON DELETE CASCADE

Нужно получить ticket_themes и последнюю запись ticket_message. В селекте получить ticket_message text, created_at. При этом должно быть created_at самая свежая дата или так скачать самая максимальная из всех цифр. created_at храниться в unix формате.

Answer 1

[Fagi]

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

SELECT ticket.theme_text, tm.message_text FROM ticket_themes ticket LEFT JOIN ticket_message tm on ticket.id = tm.theme_id WHERE ticket.user_id = 69625 ORDER BY tm.created_at DESC

Comments

[Руслан Абсалямов]

Нет, там выводяться одинаковые комнаты, А нужно чтобы они группировались. Вот пример вывода запроса
То есть id это как раз ticket. и там встречаются несколько раз 4005 и 4006. А мне нужно чтобы это был как 1 запись 4005 id и 1 запись 4006. И так чтобы text был последним в данной комнате

Answer 2

[Androgen13]

SELECT *
FROM (SELECT ticket.name as ticket_name, tm.text as message_text, tm.created_at as message_dt, ROW_NUMBER() OVER(PARTITION BY ticket.user_id ORDER BY tm.created_at DESC, tm.id DESC) AS rn
	FROM 
		ticket_themes ticket 
		LEFT JOIN ticket_message tm on ticket.id = tm.theme_id 
	WHERE ticket.user_id = 69625 ) t
WHERE t.rn = 1

Comments

[Руслан Абсалямов]

Что-то у меня ошибка

[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY ticket.user_id ORDER BY ticket.created_at DESC) AS rn FROM ' at line 2

Так и не понял как ее решить, не разу с PARTITION. Вроде бы загуглил но ничего не помогло

[Androgen13]

Руслан Абсалямов, правильно, потому что в вопросе не указано что это для MySQL. Попробуй так:

SELECT *
FROM (SELECT
ticket.name as ticket_name
, tm.text as message_text
, tm.created_at as message_dt
, @row_number:=CASE WHEN @user_id = ticket.user_id THEN @row_number + 1 ELSE 1 END as rn
, @user_id:=ticket.user_id as CustomerNumber
FROM
ticket_themes ticket
LEFT JOIN ticket_message tm on ticket.id = tm.theme_id
WHERE ticket.user_id = 69625
ORDER BY tm.created_at DESC, tm.id DESC ) t
WHERE t.rn = 1

[Руслан Абсалямов]

Androgen13, не выводит записи, хотя там они есть