Mam takie pseudo query
user
- id
time_entry
- id
- user_id
- start
- end
SELECT
...
u.id AS userId,
IFNULL(a.total_time, 0) AS totalTime
...
FROM user AS u
...
LEFT JOIN (
SELECT
user_id,
SUBSTRING(SEC_TO_TIME(SUM(total_time)), 1, 5) AS total_time
FROM (
SELECT DISTINCT
te.user_id AS user_id,
TIMESTAMPDIFF(SECOND, te.start, te.end) AS total_time
FROM time_entry AS te
) AS a1
GROUP BY user_id
) AS a ON a.user_id = u.id
...
GROUP BY u.id
Obecne query działa poprawnie, ponieważ obecnie relacja time_entry
jest ManyToOne do user
# userId | totalTime
1. 123 | 130
2. 343 | 57
Jednak od teraz zmienia się tabela i musi być relacja ManyToMany
user
- id
time_entry
- id
- start
- end
time_entry_user
- user_id
- time_entry_id
I nie wiem za bardzo jak mam teraz się wziąć za przepisanie tego query w LEFT JOIN.
Prawdopdobnie:
LEFT JOIN (
SELECT
user_id, <--- to juz nie istnieje
SUBSTRING(SEC_TO_TIME(SUM(total_time)), 1, 5) AS total_time
FROM (
SELECT DISTINCT
te.user_id AS user_id, <--- to juz nie istnieje
TIMESTAMPDIFF(SECOND, te.start, te.end) AS total_time
FROM time_entry AS te
INNER JOIN time_entry_user AS teu ON teu.time_entry_id = te.id <--- prawdopodobnie tak dodać MANY TO MANY?
) AS a1
GROUP BY user_id <--- to już nie istnieje
) AS a ON a.user_id = u.id <--- to też
...
GROUP BY u.id
MySQL 8.x