Получтиь в запросе данные из другой таблицы с максимлаьной датой?

Question

[Петр Питерский]

Допустим есть запрос
SELETEC * FROM `users` WHERE `id` = 1

Так же есть таблица Actions, где есть поля id, user, action, date

Как получить в запросе SELETEC * FROM `users` WHERE `id` = 1, еще получить последнюю самое свежее из таблицы Actions, к данному пользователю, при этом не у всех пользователей есть записи в таблице Actions

Answer 1

[Immortal_pony]

Просто получить дату последнего события:

SELECT  
    `users`.*,
        (SELECT 
            `actions`.`date` 
        FROM 
            `actions` 
        WHERE 
            `actions`.`user`=`users`.`id` 
        ORDER BY 
            `date` DESC 
        LIMIT 1) AS 
    'last_action_date'
FROM 
    `users` 
WHERE 
    `id` = 1

Получить всё событие целиком:

SELECT  
    `users`.*,
        (SELECT 
            JSON_OBJECT(
                'date', `actions`.`date`,
                'action', `actions`.`action`
            )
        FROM 
            `actions` 
        WHERE 
            `actions`.`user`=`users`.`id` 
        ORDER BY 
            `date` DESC 
        LIMIT 1) AS 
    'last_action'
FROM 
    `users` 
WHERE 
    `id` = 1

Comments

[Петр Питерский]

Подзапрос не пойдёт, потому что у меня надо получить в случае успеха 4 поля, а данном случае 4 подзапроса слишком

[Immortal_pony]

Петр Питерский данные в подзапросе можно упаковать в JSON или сделать просто два запроса

[Immortal_pony]

Петр Питерский обновил ответ, добавив подробности

[Akina]

Петр Питерский,

Подзапрос не пойдёт, потому что у меня надо получить в случае успеха 4 поля, а данном случае 4 подзапроса слишком

Чё за бред-то? ещё скажите, что у каждого поля в одной и той же записи - своя дата.

Просто вместо коррелированного запроса в списке вывода должен быть агрегирующий подзапрос в секции FROM. А если связанных записей много (навскидку - более ~15-20 на одну запись основной таблицы), так 4 коррелированных подзапроса могут оказаться и побыстрее...

[Immortal_pony]

Akina, что-то я не улавливаю как это через from должно работать. Покажите пример, пожалуйста

[Akina]

Immortal_pony,

SELECT *
FROM table1
JOIN table2 USING (user)
JOIN ( SELECT user, MAX(date) AS date
       FROM table2
       GROUP BY 1 ) AS get_max_date USING (user, date)

Подзапрос выгребает крайнюю дату. По ней из другой копии второй таблицы берётся вся запись.

[Immortal_pony]

Akina, признаюсь честно - первый раз вижу чтоб использовали using.
Но всё ещё нет у меня понимания, как оно работает.
Изменил запрос на такой (в соответствии со схемой БД, которую предоставил автор)

SELECT *
FROM users
JOIN actions USING (`user`)
JOIN ( SELECT `user`, MAX(`date`) AS 'date'
       FROM actions
       GROUP BY 1 ) AS get_max_date USING (`user`, `date`)

... но мне выдало ошибку Error Code: 1054 Unknown column 'user' in 'from clause'

[Akina]

мне выдало ошибку Error Code: 1054 Unknown column 'user' in 'from clause'

Ну так примени свои имена полей-то... это же ж шаблон.

первый раз вижу чтоб использовали using.

Ничем не хуже, чем ON. Местами даже лучше. Правда, применяется редко, потому что мало кто заботится об одинаковом именовании соответствующих полей - ведь id написать проще, чем users_id, а что в тексте запроса фиг поймёшь, чей это id - какие, право, мелочи...
Впрочем, ничто не мешает использовать классический ON-синтаксис, благо соответствующие поля понятны.

[Петр Питерский]

Akina, Задача сделать всё в одном запросе, принципиально.

[Immortal_pony]

Akina ок, переименовал user.id в user.user чтоб это заработало. В реальности такое именование редко встречается (Opencart разве что? В нём что-то подобное было).
Но запрос этот всё равно работает некорректно - если у пользователя нет данных, то мы эту строку не получим.
Если там можно делать, то тогда проще сделать запрос сразу в actions и JOIN'ить users

Петр Питерский чем тебя мой вариант-то не устраивает? Проще не сделаешь )

[Immortal_pony]

Akina Поигрался ещё с твоим запросом, но никак мне не удаётся получить результат в том виде, в котором необходимо.

В результирующей выборке должны быть колонки: `users`.*, `last_action`.`action`, `last_action`.`date`.
Если попробовать засунуть в SELECT подзапроса ещё и action, то mysql затребует более тщательной группировки. Если закинуть action в группировку, то мы получим все action'ы.
В общем, прошу приложить рабочий пример - мне интересен вариант с FROM, но я никак не могу понять как этого возможно добиться

[Akina]

Петр Питерский,

Задача сделать всё в одном запросе, принципиально.

А у меня их сколько?

Immortal_pony,

никак мне не удаётся получить результат в том виде, в котором необходимо.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=39fe57...

Заодно обрати внимание на именование полей - всё ясно и понятно, никаких пояснений и разбирательств не требуется.