Subqueries wegwerken
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
SELECT
t.id,
t.title,
t.locked_on,
t.locked_by_user_id,
t.deleted_on,
t.deleted_by_user_id,
f_p.user_id as creating_user_id,
f_p.created_on as created_on,
l_p.user_id as last_replying_user_id,
l_p.created_on as last_replied_on,
COUNT(p_c.id) as post_count
FROM
topics as t
INNER JOIN posts as f_p_r ON
f_p_r.id = (SELECT MIN(id) FROM posts WHERE posts.topic_id = t.id)
INNER JOIN post_versions as f_p ON
f_p.id = (SELECT MIN(id) FROM post_versions WHERE post_id = f_p_r.id)
INNER JOIN posts as l_p_r ON
l_p_r.id = (SELECT MAX(id) FROM posts WHERE posts.topic_id = t.id)
INNER JOIN post_versions as l_p ON
l_p.id = (SELECT MAX(id) from post_versions WHERE post_id = l_p_r.id)
LEFT JOIN posts as p_c ON
p_c.topic_id = t.id
WHERE
t.deleted_on IS NULL
GROUP BY
t.id,
t.title,
t.locked_on,
t.locked_by_user_id,
t.deleted_on,
t.deleted_by_user_id,
f_p.user_id,
f_p.created_on,
l_p.user_id,
l_p.created_on
ORDER BY
last_replied_on DESC
t.id,
t.title,
t.locked_on,
t.locked_by_user_id,
t.deleted_on,
t.deleted_by_user_id,
f_p.user_id as creating_user_id,
f_p.created_on as created_on,
l_p.user_id as last_replying_user_id,
l_p.created_on as last_replied_on,
COUNT(p_c.id) as post_count
FROM
topics as t
INNER JOIN posts as f_p_r ON
f_p_r.id = (SELECT MIN(id) FROM posts WHERE posts.topic_id = t.id)
INNER JOIN post_versions as f_p ON
f_p.id = (SELECT MIN(id) FROM post_versions WHERE post_id = f_p_r.id)
INNER JOIN posts as l_p_r ON
l_p_r.id = (SELECT MAX(id) FROM posts WHERE posts.topic_id = t.id)
INNER JOIN post_versions as l_p ON
l_p.id = (SELECT MAX(id) from post_versions WHERE post_id = l_p_r.id)
LEFT JOIN posts as p_c ON
p_c.topic_id = t.id
WHERE
t.deleted_on IS NULL
GROUP BY
t.id,
t.title,
t.locked_on,
t.locked_by_user_id,
t.deleted_on,
t.deleted_by_user_id,
f_p.user_id,
f_p.created_on,
l_p.user_id,
l_p.created_on
ORDER BY
last_replied_on DESC
Kleine uitleg over de structuur:
Ieder topic heeft posts (tabel: posts) Iedere post heeft versies (tabel: post_versions) en de eigenaar van een topic is de schrijver van de eerste post-versie, net als dat de laatst reagerende de schrijver van de laatste versie van een van de posts in het topic is. Hetzelfde geldt voor de starttijd en laatste-reactie tijd.
De tabel posts is eigenlijk een beetje vreemd. Het zorgt ervoor dat iedere unieke post zeg maar bij één topic hoort, en alle post_versions verwijzen weer terug naar deze unieke post. Maar meer staat er niet in. Echter, op zich wil ik hier later misschien nog wel dingen bij stoppen zoals de rechten wie revisies op deze post mag schrijven. [del]Daarnaast is hij nodig om m'n foreign keys in bedwang te houden.[/de] dat is niet waar. Maar ik gebruik het post_id wel om bijvoorbeeld tags aan te koppelen, en de AUTO_INCREMENT & PK geven mij een mooi uniek nummertje daarvoor.
Gewijzigd op 01/01/1970 01:00:00 door Jelmer -
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
INNER JOIN posts AS f_p_r ON
f_p_r.id = MIN(f_p_r.id) AND
f_p_r.topic_id = t.id
INNER JOIN post_versions AS f_p ON
f_p.id = MIN(f_p.id) AND
post_id = f_p_r.id
// etc. etc.
f_p_r.id = MIN(f_p_r.id) AND
f_p_r.topic_id = t.id
INNER JOIN post_versions AS f_p ON
f_p.id = MIN(f_p.id) AND
post_id = f_p_r.id
// etc. etc.
Ik vat het idee, en als je het volgens mij goed zou uitwerken kom je op dit:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
SELECT
t.id,
t.title,
t.locked_on,
t.locked_by_user_id,
t.deleted_on,
t.deleted_by_user_id,
f_p.user_id as creating_user_id,
f_p.created_on as created_on,
l_p.user_id as last_replying_user_id,
l_p.created_on as last_replied_on,
COUNT(p_c.id) as post_count
FROM
topics as t
LEFT JOIN posts as a_p ON
a_p.topic_id = t.id
INNER JOIN posts as f_p_r ON
f_p_r.id = MIN(a_p.id)
INNER JOIN post_versions as f_p ON
f_p.id = (SELECT MIN(id) FROM post_versions WHERE post_id = f_p_r.id)
INNER JOIN posts as l_p_r ON
l_p_r.id = MAX(a_p.id)
INNER JOIN post_versions as l_p ON
l_p.id = (SELECT MAX(id) from post_versions WHERE post_id = l_p_r.id)
LEFT JOIN posts as p_c ON
p_c.topic_id = t.id
WHERE
t.deleted_on IS NULL
GROUP BY
t.id,
t.title,
t.locked_on,
t.locked_by_user_id,
t.deleted_on,
t.deleted_by_user_id,
f_p.user_id,
f_p.created_on,
l_p.user_id,
l_p.created_on
ORDER BY
last_replied_on DESC
t.id,
t.title,
t.locked_on,
t.locked_by_user_id,
t.deleted_on,
t.deleted_by_user_id,
f_p.user_id as creating_user_id,
f_p.created_on as created_on,
l_p.user_id as last_replying_user_id,
l_p.created_on as last_replied_on,
COUNT(p_c.id) as post_count
FROM
topics as t
LEFT JOIN posts as a_p ON
a_p.topic_id = t.id
INNER JOIN posts as f_p_r ON
f_p_r.id = MIN(a_p.id)
INNER JOIN post_versions as f_p ON
f_p.id = (SELECT MIN(id) FROM post_versions WHERE post_id = f_p_r.id)
INNER JOIN posts as l_p_r ON
l_p_r.id = MAX(a_p.id)
INNER JOIN post_versions as l_p ON
l_p.id = (SELECT MAX(id) from post_versions WHERE post_id = l_p_r.id)
LEFT JOIN posts as p_c ON
p_c.topic_id = t.id
WHERE
t.deleted_on IS NULL
GROUP BY
t.id,
t.title,
t.locked_on,
t.locked_by_user_id,
t.deleted_on,
t.deleted_by_user_id,
f_p.user_id,
f_p.created_on,
l_p.user_id,
l_p.created_on
ORDER BY
last_replied_on DESC
Je selecteert eerst alle posts die bij het topic horen, en pakt dan de eerste en laatste. Maar je kan MIN en MAX op dat moment, tijdens het selecteren en combineren van je rows nog niet gebruiken.
Een andere oplossing waar ik aan denk is om alles bij elkaar te joinen, en dan met order by de goeie rows in de juiste positie te schuiven, zodat het eerste resultaat van de query is wat ik nodig heb. Die zou je er dan af kunnen schuiven met LIMIT 1, ware het niet dat ik dit voor alle (of een setje) topics wil ophalen. En het is langzamer wss, omdat hij onnodig (heel veel onnodig) veel JOINS moet uitvoeren en rows moet bedenken.
edit: het probleem is zeg maar dat ik niet alleen de waarde van MIN(x) wil hebben, ik wil twee waarden hebben uit de row waarbij x het laagste is.
Gewijzigd op 01/01/1970 01:00:00 door Jelmer -