How to exclude categories

There are various plugins available for Movable Type that let you exclude categories from indices quite easily. I have used ExcludeCategories, and CatEntries with some success. Both plugins work as advertised for the main index.

My trouble came when I wanted to exclude categories from each monthly archives index as well. Neither CatEntries, nor ExcludeCategories recognized the date context in the monthly archive. So when I built the monthly archives, I would get entries from my entire history (excluding the appropriate categories, of course).

Eventually, I turned to Brad Choate's SQL plugin. It required that I move from BerkeleyDB to mySQL as my backing store. MT's web page used to say to download the upgrade package (which contains the CGI script necessary to move from BerkeleyDB to mySQL), but ever since the MT 3.0 release, older versions don't seem to be available readily. I registered to download the free 3.0 version, and thankfully, it gave me the option to download 2.661. So I downloaded the upgrade tarball, and successfully converted my database. On to the SQL plugin.

The SQL plugin page provided the table structure necessary to build my query. The first thing I found out was, the entry_category_id field in the mt_entry table is not the category for the entry. I'm not sure what the heck it is. Ignore it. The mt_placement table has a row for every entry↔category association.

Regular indices

Here's the query I came up with:

  SELECT entry_id FROM mt_entry
  WHERE entry_blog_id = 2 AND entry_status = 2 AND
    entry_id NOT IN
    (
      SELECT placement_entry_id FROM mt_placement
      WHERE placement_category_id = 6
    )
  ORDER BY entry_created_on DESC
  LIMIT 10

Let's go over this little-by-little:

We're SELECTing the entry_id to identify the entries that the MT engine will display.

The first line of the WHERE clause has two conditions — get entries out of the correct blog (substitute your correct blog id there; mine was 2), and get only published entries (as opposed to drafts and such; this may be unnecessary, as the plugin does some filtering for you already).

Now comes the important part. We only want entries that are not in the excluded category. So we do a sub-query (the indented query) to find all entries that are in the excluded category, and then we say we only want entries that are not in that set. (Substitute the correct category id there; mine was 6)

(If I had no uncategorized entries, I could have selected the entries with just a simple WHERE clause, equating the entry_id and placement_entry_id from their respective tables, and making sure the placement_category_id wasn't the forbidden one. But since I have uncategorized entries, they would not appear in the mt_placement table, and I would have missed them.)

The ORDER BY line says to sort by the created on date, in descending order (newest on top). LIMIT 10 says to return only the ten most recent entries. This replaces the lastn attribute from MTEntries.

Now, all you have to do is, put all of that in one line, and use it as the query= attribute to SQLEntries. Voilà!

Date-based archives

I tried using the same query as above for my monthly archives, but found out that like the other two plugins I tried, the SQL plugin didn't grok date contexts either. Fortunately, we can fake that with a little bit of SQL. So I modified the above query as below:

  SELECT entry_id FROM mt_entry
  WHERE entry_blog_id = 2 AND entry_status = 2 AND
  entry_id NOT IN
  (
      SELECT placement_entry_id FROM mt_placement
      WHERE placement_category_id = 6
  ) AND
  (entry_created_on BETWEEN [MTArchiveDate format='%Y%m%d%H%M%S'] 
  AND [MTArchiveDateEnd format='%Y%m%d%H%M%S'])
  ORDER BY entry_created_on

The only difference between this query and the query above, is that there is an additional condition in the WHERE clause, that checks to make sure that the entry is between the beginning and end dates for this particular archive.

Note how square brackets are used to insert MT variables. Also note that the format string is enclosed in single quotes. This is because I used double quotes for the query="put query here" attribute, and using them here would terminate the string prematurely.

And that's all there is to it.

My host used to have an older version of MySQL that did not support sub-queries. If you are stuck that way, try using this query instead (substituting the correct blog id and category id):

  SELECT entry_id,entry_created_on FROM mt_entry
  LEFT JOIN mt_placement ON entry_id = placement_entry_id
  WHERE (entry_blog_id = 2) AND (entry_status = 2) AND
        ((placement_category_id != 6) OR (placement_entry_id IS NULL))
  ORDER BY entry_created_on DESC
  LIMIT 10

It has a bug: if you have an entry that is in both forbidden and unforbidden categories, it will not get excluded.

You can modify this query as above for date-based archives as well.

That's all, folks!

Let me know if you have any problems with the data in this page. If you find any inaccuracies or misinformation, just show me how and why I'm wrong, and I will correct it.