dive into mark

‣ June 6, 2003 ‣

Nothing personal

Are you tired of all the personal stuff posted here recently? Would you prefer this site be renamed Dive Into Mark except for all of that uncomfortable stuff about Mark? Subscribe to my new Nothing Personal RSS feed and bury your head as far in the sand as it is up your ass.

Nothing personal.

Update: all right, now that that’s out of my system, let me explain how I did it. It’s actually quite a neat trick, and solves a problem which many people have but to which I could not find any satisfactory answer: how, in Movable Type, to select entries that are not in a certain category, or list of categories. In this case, we’re selecting, in reverse chronological order, the latest 3 entries that are not in Addiction, Personal, Wedding, or Family.

I would like to warn you right up front that this is a gross and ugly hack. Why? Because I haven’t found any other solution that satisfies all 4 of these requirements:

Allows me to specify exactly how many entries I want to select. Most solutions select entries and then selectively filter them after the fact, so the final number of entries is variable. This is unacceptable; I want exactly 3 entries.
Allows me to specify multiple categories to exclude.
Works with entries in multiple categories.
Works with entries not in any category (i.e. does not exclude them)

MT-ExcludeCategories seems like the obvious choice but fails #1. A simpler (and single) SQL statement could be used to satisfy all criteria except for #3, but the current production versions of MySQL don’t support subselects, so this exact logic can not be duplicated without a temp table. (SQL-heads, prove me wrong!) Going at it in reverse and specifying cateogries to include instead of exclude fails #4. I am open to any and all suggestions here.

Ready? OK, this requires that you’re running Movable Type on a MySQL database, and that you have access to that database to create a new table, and that you’ve installed Brad Choate’s amazing MT-SQL plugin.

Log into your MySQL database and create a new table:

create table mt_cat_tmp (entry_id int(11));

Now take a look at the Nothing Personal template. Besides changing the <dc:rights> element, which has my name hard-coded in it, you’ll need to fiddle with the SQL statements about 2/3 of the way down, in the <MTSQLEntries> tags.

The first one just cleans out the temp table (don’t execute these statements in MySQL, just follow along):

delete from mt_cat_tmp

The second one populates the temp table with a list of entries that are in the categories we want to exclude. You’ll need to change two things here, the blog_id of your blog, and the list of categories you want to exclude.

insert into mt_cat_tmp select distinct(placement_entry_id) from mt_placement p inner join mt_category c on p.placement_category_id = c.category_id where p.placement_blog_id = 3 and c.category_label in (’Addiction’, ‘Personal’, ‘Wedding’, ‘Family’)

Note that both of these <MTSQL> tags are empty. We just want to execute these SQL statements for their side effects; they don’t return any data. (This is what I consider the gross and ugly part of this solution, since it assumes a certain order of execution, and since in general, template tags shouldn’t have destructive database side effects.)

Finally, the third statement actually selects the entries we want — every entry that isn’t in one of the excluded categories. There are two, possibly three things to fiddle with here. First, the blog_id again. Second, the order of the entries; here we’re sorting by creation date, most recent first, which is just like the default behavior of a normal <MTEntries> tag. If you want some other sort order, you’ll need to code it directly in the SQL yourself. Third, the number of entries; here I’m limiting it to 3 because I like to keep my RSS feeds small, but the limit can be however many entries you like.

select e.entry_id from mt_entry e left outer join mt_cat_tmp t on e.entry_id = t.entry_id where t.entry_id is null and e.entry_blog_id = 3 and e.entry_status = 2 order by e.entry_created_on desc limit 3

Note that this solution works with entries in multiple categories; if any of the categories is on the exclusion list, the entry is excluded.

This is the sort of thing that is infinitely easier in a real database that supports real subselects, but MySQL 4.1 (currently in alpha testing) is the first version that does. The above solution works fine in MySQL 3.23, which is what I’m using, and probably what you’re using too.

Update #2: the amazing Brad Choate was able to create a single SQL statement that accomplished the same thing, without a subselect or a temporary table. All Hail Brad! I’m still trying to wrap my head around exactly why it works, but it does work. In the meantime, view the template and check it out. You’ll need to know your category IDs, and your blog ID, if you want to customize it. Here’s some easy SQL to get your category IDs:

select category_id, category_label from mt_category

Put as many as you like in the parentheses, comma-separated like mine. If you just want to exclude one category, you can just put one in there, with no commas.

All Hail Brad. All Hail Brad.

Forty six comments here (latest comments)

I enjoy reading the personal posts. They’re a nice change from the tech articles I usually read in the course of a day. If I do say so myself, you’re a very good writer, and a joy to read.

— Adam
I guess some cretin(s) complained about personal content in your journal? Like they couldn’t just skip the parts they didn’t want to read. Talking about lazy. No, make that, pathetic and lazy!

— Damelon Kimbrough
I can’t get enough personal stuff. What did you last eat? Mmmmm.

Actually, I like there being a mix. It’s great. (I am so jealous of your honeymoon. Grrrr)

— gord
I like the personal stuff; there’s more to life than geekery.

Plus, I’m waiting for the one that explains how you came to be a philosophy major.

— James
By an amazing coincidence, I was just explaining that to Dora last night. It’s not a long story. I was sitting on Libe Slope at Cornell West Campus, on a perfectly sunny fall day, looking through the monstrous course catalog. I saw a Philosophy 101 course that looked interesting and wasn’t too early in the morning, and I said to myself, “hey, that sounds like fun.”

I was actually a double major, computer science and philosophy. When I switched colleges, their philosophy department was very strong and their computer science department was relatively weak, and they screwed me on my required courses, so I didn’t have enough time to finish both in four years and had to drop one. By then it was obvious to me that I was going to be a professional programmer no matter what I studied, but that I would only ever have one chance to study philosophy, so I dropped computer science and studied philosophy. You only go around once.

— Mark
This is why Mark is my hero =]

All you losers who complain, use this =]

— Steven Canfield
Thanks for the explanation, Mark; I’d been wondering if it was something like that. Now you’ve added more force to my “none of us actually *intended* to be philosophy majors, we just took the 101 course and got sucked in” theory.

— James
I generally don’t make a comment ’round here, preferring to simply read and absorb, but this post made me laugh. Out loud. Literally.

Cheers, Mark!

— Dan
Philosophy required very few courses for the major, so I could take what I wanted and learn about tons of other fascinating things in other departments (when I chose to go to class). I ended up taking more philosophy courses than I was required to, because I liked it; but I fell into the major because of Phil 11 (Greeks-Aquinas) and the low threshhold for majoring.

— AKMA
Where did you go to college, exactly, Mark?

— tamaracks
Keep the personal comments. It’s one way that I evaluate the writer of all of the weblogs that I read. If I think that I know what kind of person you are, I’m more likely to put more intellectual value on what you write. My internal rating of you went to number one when you wrote about your impending wedding. Not only did you and I share similar experiences but the writing showed courage. I believe the courage displayed in your personal writing demonstrates the courage you might display in your programming/profession.

Keep it up. I can’t wait to buy the book.

Steve

— Steve Kirks
I went to Cornell for 2 years, then I finished up at Earlham. There was a year in between there when I was living at home and getting into all sorts of trouble, but never mind that.

— Mark
AKMA: That was one of the other nice things about philosophy. My school’s philosophy major required 9 classes over four years, so I got plenty of electives. But somehow it got ahold of me beyond that; it was like I showed up to 3rd-floor West Hall sprnig of my freshman year (where most of the courses were taught and most of the professors’ offices were) and never managed to leave again. And eventually found myself liking it.

— James
Did you look at the Compare plugin from Staggernation? http://mt-plugins.org/local/CompareReadMe.php

I’ve used it for similar situations although admittedly not for this particular one.

— john
Thanks for the invite, but I’d rather keep my head sand and a%$ free. Keep the hits coming — and keep making them as personal as you want them to be.

— Greg Greene
Unfortunately, I believe the Compare plugins work within an MTEntries tag, which means I could not control the exact number of entries, since I would not know in advance how many I was filtering out.

— Mark
Just wondering why ExcludeCategories wouldn’t work for this situation…

http://mt-plugins.org/archives/entry/excludecategories.php

— eliot
I was drawn to your blog by Addiction Is…, though now I keep coming back for the lessons in CSS and everything else.

— epc
I am addicted to nicotine too, but looks like you are also addicted to agreements and praises. I am shifting over to nothing-personal feed and bury my head in the sand by sitting down. Too bad. I’ll miss your personal posts.

— Don Park
Another vote for the personal posts. It’s the balance between the concretely valuable technical posts and the less tangibly but nevertheless valuable personal posts that I appreciate about this site. (Please, though, no “what did you eat last” posts. I’m not fond of the “Bob Graham’s diary” mode of weblogging. God, is anybody?)

As for your writing, I value most of all the clarity and economy of your prose, which implies to me that you actually put thought into what you write, that you say what you mean to say.

I began college as an electrical engineering student, and had no little to no aptitude for the subject. I ended up majoring in English because that’s where I consistently made As. I minored in philosophy because of a fundamental misunderstanding of what the study of philosophy entailed (epistemology? ontology? phenomenology? ouch! I was expecting ethics!)

— jacob
Just in case I’m thick, what stops this being done with one SQL statement which joins the two you specified together (presumably mt_placement and mt_entry)? I should probably not comment at all since I’m not using MT and don’t know what the database is like. This comment is *really* here to express a certain amount of surprise that we share a degree, sort of, in that I have a philosophy and compsci joint honours, although I didn’t have to drop one of them…

— Anonymous
If I were to use one of these babies, I’d like one that only excluded posts where there are ONLY personal categories (one or several) as otherwise you could be excluding posts like http://diveintomark.org/archives/2002/07/29/while_you_were_out.html with lots of geekery too.

— Jesper
// bury your head as far in the sand as it is up your ass //

Maybe you’d like to apply a bit of strike-through to that, oh great wise Moderator.

Or is it one rule for posts and another for comments?

— James Kew
Apologies if this seems a little callous, but since I don’t know you, I read the personal entries as short fiction, and well-crafted, affecting short fiction at that. A well turned phrase, or a crafty blog hack - it’s all geek to me!

— Mot
Good programming evolves I have found, from well rounded people. Programming is about interaction…social interaction and the ability to achieve adaptation in many different situations. Screw the bastard critics Mark. I like the well rounded approach. Those that are critical(while not tring to be) of others are also shortsighted. I wonder how it effects their programming?

— don
Ah, the days on Libe Slope, digesting Nines pizza…

I was in Engineering, so I took a few CS courses and code-driven Industrial Engineering courses (Simulation, etc.), but never really got into it at the time (the use of PL/1 at the time may have been a factor :)).

And I took a few phil courses, but didn’t choose well…

(By the way, this new book-covers column thing is pretty, but if you have a narrow window, the text flows into the pix. I’ve noticed a similar-but-worse problem at http://www.typepad.com/ - is there a problem making *liquid* CSS-based designs?)

— BillSeitz
I must say that I mostly skip the really long entries in the weblogs I follow, but when I stumbled on your last 2 personal ones, I read them and really really enjoyed the reading.

I will not change the RSS-feed =)

— Glenn
“I believe the Compare plugins work within an MTEntries tag, which means I could not control the exact number of entries, since I would not know in advance how many I was filtering out.”

Right–plus it would break the logic of the MT*Header and MT*Footer tags. (And it would probably only work with primary categories in the first place.)

The ExcludeCategories plugin someone mentioned above might do the trick, but it’s an ugly solution in that the plugin contains a whole new version of MTEntries. There’s presently no clean way for a plugin to implement entry-filtering functionality without duplicating the rest of MTEntries, which is why FilterCategories http://www.staggernation.com/FilterCategoriesReadMe.html doesn’t do it.

— Kevin
Don, this wasn’t directed at you. You had a legitimate comment on the story; one that I disagree with, but you made your point well. This is directed at the people who read stories like this, or my earlier writings on addiction, and blurt out things like “is there a way I can skip the boring stuff?”

I used to get these pleas via email; now they just leave anonymous comments.

— Mark
Kevin is correct, and I have updated the main text of the post to explain exactly what my requirements are, and why various solutions fail to satisfy them. I am open to any and all suggestions here; it’s entirely possible that some enterprising young MySQL expert can teach me some magic trick to replicate the logic of a subselect and do this in a single statement. Here’s the statement I’d *like* to use:

select e.entry_id
from mt_entry e
where not exists (
select p.placement_entry_id
from mt_placement p
inner join mt_category c
on p.placement_category_id = c.category_id
where e.entry_id = p.placement_entry_id
and c.category_label in (’Addiction’, ‘Personal’, ‘Family’, ‘Wedding’)
)
order by e.entry_created_on desc
limit 3

That’s off the top of my head, so it may not be flawless, but I believe that’s the general idea.

— Mark
This is diveintoMARK not diveintoTECH.

— Tony Dennis
It’s not that we don’t enjoy the personal stuff, it’s the strange, cumbersome writing style you use (only) when relating your personal stuff.

— William
Couldn’t you have just used the ExcludeCategories (http://mt-plugins.org/archives/entry/excludecategories.php) plugin? I believe it replaces MTEntries therefore allowing you to control the number of items it will produce. I could be wrong, as I don’t use the plugin. I just remembered seeing that it existed.

— Reverend Jim
I guess I should have reloaded the page before commenting (I think it had been sitting open since last night and I did’t realize it) because someone else suggested ExcludeCategories already. Sorry about that.

— Reverend Jim
What categories are my last 3 entrys in?

mysql> select
e.entry_id
, p.placement_category_id
from
mt_entry e
, mt_placement p
where e.entry_blog_id = 2
and e.entry_id = p.placement_entry_id
order by e.entry_created_on desc
limit 3;

| entry_id | placement_category_id |

| 536 | 26 |
| 530 | 22 |
| 529 | 26 |

3 rows in set (0.01 sec)

I don’t like those 2 categories for this RSS feed, so let’s knock them out:

mysql> select
e.entry_id
, p.placement_category_id
from
mt_entry e
, mt_placement p
where e.entry_blog_id = 2
and e.entry_id = p.placement_entry_id
and p.placement_category_id not in (26, 22)
order by e.entry_created_on desc
limit 3;

| entry_id | placement_category_id |

| 528 | 3 |
| 527 | 28 |
| 526 | 51 |

3 rows in set (0.00 sec)

Only difference is I had to do it by category_id, but the label is probably possible too. Am I missing something on your requirements list here Mark?

— Phillip Harrington
I think I still missed #4 (not in any category). But that was off the top of my head… Hmmm… Another 30 minutes and you could probably have it.

— Phillip Harrington
Actually, after some testing, I think that works… (*real* SQL-heads, prove *me* wrong???)
I set one of the placement_category_id’s in my origial result set to null and ran the query again - same result set. So nulls (not in a category) are not a problem (since null is not in (22,26) duh - I’m not awake)

Details:

mysql> update mt_placement set placement_category_id = null where placement_entry_id = 528;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1

mysql> select
-> e.entry_id
-> , p.placement_category_id
-> from
-> mt_entry e
-> , mt_placement p
-> where e.entry_blog_id = 2
-> and e.entry_id = p.placement_entry_id
-> and p.placement_category_id not in (26, 22)
-> order by e.entry_created_on desc
-> limit 3;

| entry_id | placement_category_id |

| 528 | 0 |
| 527 | 28 |
| 526 | 51 |

3 rows in set (0.01 sec)

— Phillip Harrington
Couldn’t you have used that exclude categories plugin? Oh, wait, I mean this one: http://mt-plugins.org/archives/entry/catentries.php . I have a few outstanding bugs, but it’s been working on my front page a while now and its lastn actually works.

An SQL solution is better except for those of us using Berkeley DB, though.

— Mark Paschal
In that case, I’ll avoid playing in the sand.

— Don Park
I wouldn’t worry too much about it

— Haroeris Astrum
Big Pink Cookie (trackback)
My own blog doesn’t get enough comments to warrant this, blogs that attract double-digit comments regularly (like Mark’s) really need threaded comments because it is difficult to figure out who is replying to who.

— Don Park
yowkee essential (trackback)
Thanks!

Usually, I just don’t have the time to read personal stuff even if I would be interested. I’ve now subscribed to “nothingpersonal” with the default refresh, and set the old feed to NO autorefresh. I can still manually refresh when I have time to kill (not often).

I do wish more authors would keep things separate like this!

— Marjolein Katsma
Heh. Some sites I read *because* of the personal stuff, some sites I read *despite* the personal stuff…

— nolo
Without knowing the MT DB schema, I can tell you that doing a left outer join on Alpha and Beta (in that order returns all rows in Alpha, and any related rows from Beta, using the fields specified in the “on” clause to match the rows between the table.

Using this pattern:

Alpha Left Join Beta
on Alpha.foo = Beta.foo
Where Beta.foo is null

is a common idiom which says “give me all rows from Alpha which do not have related rows in Beta”.

So the SQL below says “give me all entries w/ status = 2 (I assume this is published?) for blog_id = 3 (I assume this is DiM?) which are not included in the categories (1, 9, 12, 137), giving me the latest entries first, and returning at most 3 records.”

Hopefully that helps get your head around it. :)

select e.entry_id
, e.entry_title
, p.placement_category_id
from mt_entry e
left outer join mt_placement p on e.entry_id = p.placement_entry_id
and p.placement_category_id in (1, 9, 12, 137)
where e.entry_status = 2
and e.entry_blog_id = 3
and p.placement_entry_id is null
order by e.entry_created_on desc
limit 3

— Jeremy Dunck

Respond privately

I am no longer accepting public comments on this post, but you can use this form to contact me privately. (Your message will not be published.)

firehose ‧ code ‧ music ‧ planet