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

Wibble

More audiobloggery. Radio Aquarion Random Help-me-I’m-single leach Lemmings! Oh, and I finally got around to blogrolling Cathy, despite…