Dark Light

Okay, I’m working fixing bugs in AqWiki before I release, and one of the major ones is that currently the Search function requires MySQL4’s subqueries (Actually, the whole thing requires MySQL at the moment, but that’s a post-1.0 issue) and I’m trying to de-subquery the query, but it’s not working, so I’m throwing it out in the open to see what happens.

This works, but relies on Subqueries which I want to avoid (Or at least support people not having)

SELECT wikipage.page, wikipage.name, wikipage.created,
	max(revision.created) as revised, revision.revision
 FROM revision
 LEFT JOIN
	wikipage ON wikipage.page = revision.page
	and revision.revision =
	(SELECT max(r2.revision)
		from revision as r2
		where r2.page = revision.page)

 WHERE content LIKE "%TODO%"
	AND wiki = "personal"
 GROUP BY wikipage.page;

This doesn’t work (the second AND statement isn’t valid) but it’s basically what I want to do:

SELECT wikipage.page, wikipage.name, wikipage.created,
revision.created as revised, revision.revision, max(revision.revision) as toprev
 FROM revision
 LEFT JOIN
	wikipage ON wikipage.page = revision.page
 WHERE content LIKE "%TODO%" AND wiki = "personal"
	AND revision.revision = max(revision.revision)
 GROUP BY wikipage.page
 ORDER BY revision.revision desc;

Any ideas? (Yes, I know I should use fulltext instead of LIKE, that’s next on the Big List Of Bugfixen Before We Go Live)

In case it helps, the Table structure is now online

Related Posts

MLP

May I present to you, courtesy of Dave Winer (Who I doubt will ever know he is extending…

Bad days

Distressingly, the Badby youth hostel is going to close. Badby was the site of my very first proper…

Yay lack of dialup

Typical. The day NTL are due to install the Whizzy Cool broadband connection, I manage to connect to…

Songs

Via Meg, sort of: The JCB Song (From the animator of the Radiohead Creep video )