MySQL LAST() en FIRST()

Door toby hinloopen op 14-10-2009 02:16

3.869 views

Owke, ik ben er ondertussen al achter: LAST en FIRST zijn 2 functies die niet in MySQL voorkomen. Hoe kan ik deze query dan laten werken?

Dit is een query die een lijst met topics laat zien in een bepaald board.
De volgende data wordt opgehaald, van boven naar beneden:
-Topic ID, voor het linken naar het topic
-Topic title, voor het weergeven van de titel
-De username van de gebruiker van de 1e post.
-De user-id van de gebruiker van de 1e post.
-De username van de gebruiker van de laatste reactie.
-De user-id van de gebruiker van de laatste reactie
-De tijd van de laatste reactie
-Het aantal reacties in dat topic

Tenminste, dat hoort de query te doen. Dat doet-ie echter niet, want MySQL ondersteund geen LAST en FIRST.

Ik weet dat je gewoon embed queries kan gebruiken, maar dat vind ik slordig; Er is vast wel een betere methode.

SELECT
	topic.id AS id,
	topic.title AS title,
	FIRST(user.nickname) AS username, 
	FIRST(user.id) AS user_id,
	LAST(user.nickname) AS lastreplyusername,
	LAST(user.id) AS lastreplyuser_id,
	UNIX_TIMESTAMP(LAST(post.time)) AS time,
	COUNT(1) AS posts
FROM
	posts AS post
JOIN
	users AS user
	ON user.id = post.user_id
JOIN
	topics AS topic
	ON post.topic_id = topic.id
WHERE
	topic.board_id = $boardid
GROUP BY topic.id
ORDER BY post.time DESC
LIMIT 0 , 50

Oftewel, wat is een alternatief voor LAST() en FIRST() in MySQL?

Jelmer -

14-10-2009 08:47

Je kan je user-tabel 2 maal joinen, eentje voor first, en een je voor last.

Dit is een query uit een forum van mijzelf. Zie f_p en l_p, daar join ik 2 keer dezelfde tabel voor de eerste en de laatste post. Maar hij gebruikt subqueries, en volgens mij is dat niet heel erg efficiënt. (desalniettemin hij werkt wel :) )


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,
	(SELECT COUNT(*) FROM posts WHERE posts.topic_id = t.id) as post_count
FROM
	topics as t
LEFT JOIN posts as f_p_r ON
	f_p_r.id = (SELECT MIN(id) FROM posts WHERE posts.topic_id = t.id)
LEFT JOIN post_versions as f_p ON
	f_p.id = (SELECT MIN(id) FROM post_versions WHERE post_id = f_p_r.id)
LEFT JOIN posts as l_p_r ON
	l_p_r.id = (SELECT MAX(id) FROM posts WHERE posts.topic_id = t.id)
LEFT JOIN post_versions as l_p ON
	l_p.id = (SELECT MAX(id) from post_versions WHERE post_id = l_p_r.id)
WHERE
	t.id = :id
	AND t.deleted_on IS NULL

Als ik zo forums als phorum en phpBB3 doorblader, krijg ik het idee dat zij het niet eens op deze manier doen, maar gewoon alle nodige informatie uit één tabel selecteren, en daar dan via PHP zelf de eerste en laatste user bijzoeken. Dat zou langzamer zijn, maar zij hebben daar dan weer een cache tussen zitten. Als zo'n cache bijvoorbeeld APC is, of iets anders wat in het geheugen leeft, dan zou dat best snel kunnen zijn. Misschien zelfs wel sneller dan een ingewikkelde query die dankzij z'n subqueries niet in de query cache van MySQL terecht komt.
En de kleine stukjes (laatste user van topic x, eerste user van topic x) veranderen minder vaak dan het resultaat van die grote query, waardoor er meer cache hits zijn, en je database het rustiger heeft denk ik.

Reageren

Inloggen om te reageren