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)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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:
|
1 2 3 4 5 6 7 8 9 |
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