Posted on by lhl

I had started writing a custom MT plugin, but it turns out that Brad’s MT-SQL plug-in will let me do just about everything that I need (the latest version of MT-SQL [v1.6] isn’t linked, but can be gotten by changing the d/l link appropriately or grabbing it from Brad’s CVS tree – necessary if you want to use the MTSQLBlog tag).

Now, the first, simple thing I was trying to do was to write a last-updated blog-roll. I’m sure it’s been done before, I just couldn’t find code. I did learn a couple things. Doing something like:

SELECT DISTINCT entry_blog_id blog_id from mt_entry WHERE entry_status=2 ORDER BY entry_modified_on DESC

won’t work because DISTINCT functions like a GROUP BY and doesn’t let you order on another field. OK, so do a subquery like:

SELECT DISTINCT entry_blog_id blog_id FROM mt_entry WHERE entry_status=2 AND blog_id IN (SELECT FROM entry_blog_id FROM mt_entry ORDER BY entry_modified_on DESC)

that’d work fine, except that only MySQL 4.1 (alpha code) has subquery support (*sigh*). The final code that does it is using a MAX and GROUP BY and then ordering by that:

SELECT DISTINCT entry_blog_id blog_id, max(entry_modified_on) AS d FROM mt_entry WHERE entry_status=2 GROUP BY blog_id ORDER BY d DESC

Now, I’ll probably do a plugin wrapper for the multi-blog aggregation I want to do, since the SQL code will be quite messy otherwise.

(yeah, you’d think with all this MT programming I’ve been doing I’d switch my blog off vim, and onto, well, any blogging tool, really. At this point however, I think it’s pride/stubborness more than anything else.)