This is the blog of Adam Kalsey. Unusual depth and complexity. Rich, full body with a hint of nutty earthiness.
Excerpt: A better way to create a list of related blog entries using MySQL fulltext indexes and the MTSQL plugin. Includes a complete technical overview of how the solution works and performance considerations. Read the whole article…
Excerpt: There was once a plugin for getting Related Entries, and it worked well. If you used categories in a good way (I do) then life is great. However, there are times when a really related entry is in another category...
Excerpt: Adam Kalsey has come up with a great idea using one of Brad Choate's plugins. I love watching genius at work, and then implementing their ideas.
Woah, very nice. The html shows up in the SQL query in this entry though. Might want to fix that.
Excerpt: Thanks to all the comments on my most recent Related Entries post, I've installed Adam Kalsey's Related Entries code. The
I don't see any HTML in the SQL. There's some HTML inside the container tag that formats the list of related entries, but not in the query.
No, I meant this query: [span class="caps"]ALTER TABLE[/span] mt_entry [span class="caps"]ADD FULLTEXT [/span]( entry_keywords, entry_title, entry_excerpt ) (made the tags brackets instead so they wouldn't get stripped)
Ahh. There it is. That was caused by a strange interaction between Textile and an MT macro. It's fixed now.
Excerpt: MTshell (a command line frontend to MovableType) Noel Jackson.com Related Entries Revisited :: Kalsey Consulting Group Waag Connect > E-book can last six months on two AA cells - News & Technology - CNETAsia Blackmask Online: Wet Paperback Before Scan...
I think MT's future is not as CGI scripts for blogging, but as a full-fledged tag-based language. With its access to Perl, I think it'll be like a ColdFusion that doesn't suck. Maybe I'm insane, but I think it's an avenue worth pursuing: http://www.joegrossberg.com/archives/000632.html
Excerpt: Adam Kalsey has a new method for automatically generating related entries links between articles in a Movable Type weblog. It requires a little setup work, but it look like a promising system that isn't dependent on keeping a taxonomy of...
This is a brilliant idea! Having said that, I have a feeling this method is not going to work if the Movable Type is not using MySQL object driver. I believe there is still some room for improvement untill there is a plugin that can do this independant of the driver being used. P.S. It can be done through MT::Object class :-)
Since the whole idea relies upon MySQL's fulltext indexing support, making a plugin that supported non-MySQL backends would be somewhat pointless. If they don't have MySQL to begin with, then I'd need to build a whole indexing system.
Excerpt: Using the SQL plugin, you can simulate a semantic search of your blog to find entries related to a given entry. A clever idea from Adam Kalsey. Just follow his instructions and you're good to go. Note that you will...
Excerpt: I'm experimenting with Adam Kalsey's Related Entried Revisited MovableType modification.
I'm currently using your MT related entries plugin to grab related thumbnails for my photos using the following: ">-thumb.jpg" alt="" title="" border="0" width="120" height="90" class="littlepic"> The MTEntryMore tag is where the picture location is stored, so I was trying to figure out how to change your sql code to the above. Does this query grab the whole entry? Can I just use MTEntryMore in there also? Ideally I would prefer using the MTrelated tag its a lot simpler!
Could all the sql be incorporated into a plugin so that it can be used just like your older plugin? i.e just specifying a tag, and not a chunk of code.
Thanks, this plugin works great. I added a [h4] tag and a [ul] tag at the start of the [MTSqlEntries] query and a [/ul] tag after the query (as I assume you implied). It works beautifully whenever I have related entries. Sometimes, however, I don't. Is there a way to combine this so that it leaves off the [h4], [ul], and [/ul] tags whenever there are no related entries?
You could use the IfEmpty plugin to check to see if the MTSQLEntries returns any data and only display your formatting if it does.
Hmm. Okay, I've been playing with the formatting for the IfNotEmpty tag, but I keep getting tripped up. Is there some additional guidelines or examples of nesting the expression?
You probably want to do something like... <MTIfNotEmpty expr="[MTSQLEntries query="SELECT entry_id, MATCH (entry_keywords, entry_title, entry_excerpt) AGAINST ('[MTEntryKeywords encode_php='q'] [MTEntryTitle encode_php='q']') AS score FROM mt_entry WHERE MATCH (entry_keywords, entry_title, entry_excerpt) AGAINST ('[MTEntryKeywords encode_php='q'] [MTEntryTitle encode_php='q']') AND entry_id != '[MTEntryID]' AND entry_blog_id = [MTBlogID] ORDER BY score DESC LIMIT 0 , 4"]1[/MTSQLEntries]"> ... your related entries template ... </MTIfNotEmpty>
That still doesn't seem to be working. I verified I had the latest version of the SQLEntries and the IfEmpty plugins installed. Anything else I should check?
Excerpt: Related Entries Revisited by Kalsey Consulting Group explains how to add a fulltext related entry feature to MT! Great stuff Adam!...
Is there any reason why Related Entries would only find entries that share a common word in their titles? I've added this to my MT installation, following the above directions to the letter, and this seems to be the case; only the 'entry_title' appears to be getting indexed. Thoughts?
Probably because you don't have excerpts or keywords. The only things compared using my code is titles, excerpts, and keywords. The autogenerated excerpts don't count; those aren't actually stored in the database.
Hmm, that's a good reason. Would doing a full text search on the entire entry bodies yield odd results? If not, how would that be done? And, to sympathize with Jason Mevius above, the IfEmpty statement doesn't work.
That depends on your entries, really. By using the whole entry body, you're pretty much guaranteeing that there will always be a match, even if it's just on words that really aren't part of the meaning of the entry (like "that" and "some"). If your entries aren't typically very similar, your related entries would show entries that simply had a bunch of similar words. The reason that I use title, keywords, and excerpt is because those fields usually have a very high concentration of words pertaining to the general subject of the entry. You could avoid some of the not-really-related problem by adding the fulltext match score as one of the criteria. Just change a portion of the SQL code to read: AND entry_blog_id = [MTBlogID] AND score > 5 ORDER BY score DESC LIMIT 0 , 4 You'll want to adjust the score > 5 part to suit your needs. Play with it a bit by using different numbers until you like the results. A higher number is more sensitive and will require a closer match. A lower number is less sensitive and will show more entries. Using Brad's MTNull and MTRegex plugins, I've come up with a method for not showing the related block if there were no related entries. Make sure your related block prints on all one line in the HTML source and add a comment to the end of it. Then use regex to look for the beginning of the block followed immediately by the comment. If that's found, there were no related entries so replace the block with nothing. <MTAddRegex name="norelated">s|<h2>Related:</h2><!--end related-->||</MTAddRegex> <MTNull regex="norelated"> <h2>Related:</h2><MTSQLEntries query="SELECT entry_id, MATCH (entry_keywords, entry_title, entry_excerpt) AGAINST ('[MTEntryKeywords encode_php='q'] [MTEntryTitle encode_php='q']') AS score FROM mt_entry WHERE MATCH (entry_keywords, entry_title, entry_excerpt) AGAINST ('[MTEntryKeywords encode_php='q'] [MTEntryTitle encode_php='q']') AND entry_id != '[MTEntryID]' AND entry_blog_id = [MTBlogID] ORDER BY score DESC LIMIT 0 , 4"><li><a href="<MTEntryLink>"><MTEntryTitle></a></li></MTSQLEntries><!--end related--> </MTNull>
I've done the modifications to my mySQL tables to allow full-text searches on the entry bodies themselves (stored in entry_text), and I've got the query changed to grab entries from them. That all works fine, but no matter where I stick the 'AND score > 5', I get the error: "Build error in template 'Individual Entry Archive': Error in tag: You did not specify a query." It seems that this isn't proper syntax, or something (as you can probably tell, databases aren't my thing). Without specifying reasonable scores I get some pretty unrelated related entries, to say the least. Any thoughts as to why MTSQLEntries would be barfing on this?
Excerpt: There was a bit of downtime for my sites individual entry archive today, but I hope it didn't affect any of you too drastically. Moveable Type corrupted itself but not, fortunately, my database. A "reinstallation" (that is, uploading a fresh...
MT is seeing that > sign and thinking you're trying to end the tag. So you could use > intead and it should work.
Well, with that modification it now processes the whole query, but errors with: Error in tag: Error in query: Unknown column 'score' in 'where clause'. Nuts. Thanks for your patience with this, Adam. I totally owe you a coffee or something if we ever meet at a conference ;-)
I forgot that th published version of the code was different than my version. You'll need to alter the SQL statement. It should start with "SELECT entry_id, MATCH (entry_keywords, entry_title, entry_excerpt) as score"
Not quite... "Error in query: You have an error in your SQL syntax near 'AS score AGAINST (' Glitches, Relations, Additions Oh My') AS score FROM mt_entr' at line 1"
try adding this to my blog and got this error when I did a rebuilt. Can't get DBI::st=HASH(0x872c3d8)->{NAME_hash}: unrecognised attribute at extlib/bradchoate/sql.pm line 132. i've followed the instructions you've stated. please advise.
According to Brad, you are probably using an old version of DBI. Have your host update your DBI drivers.
This plug-in is extremely brilliant. My 800+ posts gathering dust in the archives have no come alive thanks to this tool.
Excerpt: What if each time I posted something I parsed the RSS feeds in my daily whirl, and apply Bayesian algorithms to generate links to similar recent entries on other people's sites...
How much effect does this have on rebuilding times? For a lot of entries, is this in effect searching each entry against all the others over and over?
Essentially yes, this does cause each entry to search all the others, but it's not much of a performance hit for several reasons. First, we're searching against a MySQL fulltext index. This is very fast. Its nothing like using "like" matching in SQL. Fulltext indexing is designed to quickly do precisely what we're doing here. We're also limiting the results returned to four. So MySQL does the lookup in the index, sorts the results and spits back the first four. Then it quits. Movable Type doesn't have to parse through hundreds of entries; it on;y get the four it needs. The vast majority of the time you are only rebuilding a single entry. When you post a new entry or someone comments, only that entry gets rebuilt. The only time you rebuild all the entries is when you force a full rebuild from the MT publishing interface.
hi, brilliant plugin. now, is there a way to show the whole "related entries" section ONLY if there is at least one match?
Excellent stuff. It took all of ten minutes to set up. This is a very close match to what I was looking for. The quality of the semantic search still depends rather heavily, it seems, on the application of keywords, but that's fine with me. I'm curious if you can explain, perhaps I've just overlooked it in the discussion above, what the zero in this phrase means: DESC LIMIT 0 , 4 I understand the limit portion; that's easy. Changing the zero to other numbers does seem to impact greatly the nature of the results. If the query stops when it has found LIMIT, does that not preclude better matches deeper within the database?
LIMIT determines how many rows will be returned by the statement. All rows are still examined, LIMIT only affects the output. So LIMIT 0, 4 says return 4 rows skipping the first 0 rows (ie start at row 1). LIMIT 2, 4 means return 4 rows starting with the third one. You can change the number of related entries by changing the limit. LIMIT 6 would return the first six related entries. You can read more about LIMIT inthe MySQL documentation at http://www.mysql.com/doc/en/SELECT.html
Excerpt: After yesterday's entry regarding Plugin and how to tweak MT, I couldn't help but do a little tweaking myself. That coupled with the comments from my review at The Weblog Review, I thought of a few things that I could do to kind of spruce up the place....
Thank you for this method. I've just set it up, it works fine, but, like Alexander Payne, I haven't been entering excerpts or keywords, and thus I get only entries with common words in their titles. I've read through your explanation in the comments here, and it seems logical, but I am wondering a couple of things: Your query looks at Titles, Keywords, and Excerpts, but I would expect something about MTEntryExcerpt in the AGAINST ('[MTEntryKeywords encode_php='q'] [MTEntryTitle encode_php='q']') section of the code. Is that missing? or does it check Excerpts some other way? I don't read SQL code at all, but this seems like it's checking only titles and keywords, not excerpts (though it wants to with the entry_excerpt). And if I were daring and wanted to check within MTEntryBody (using the score > # adjustment), would I just add [MTEntryBody encode_php='q'] inside both AGAINST parenthesis? Would I also need to add a entry_body thing inside the MATCH parenthesis? My solution may be to just start using excerpts and keywords (and have the blog's other authors do it too), but I thought I'd give this a try first (as a way of including the old posts). Thanks a lot.
Excerpt: Results are pretty good and will continue to get better if I can remember to be diligent about keywords.
Hi there, I have a problem with your plugin. I have installed it as described and it works, but it always shows up only one result and it is the very same entry. Any idea what can be the problem?
Hi, congratulations to this wonderful "plugin". It's a great help! One thing we couldn't figure out - how can we alter the statement to search for realted entries in all blogs? We used "...AND entry_blog_id in (8,9,10)..." instead of the existing entry_blog_id statement but it still only produces matches from one weblog? What's the problem? All teh best, Torsten
I was wanting to add a related entry list to my blog as well. I knew things could be done this way, but if the pages were made static, older posts wouldn't be able to link to newer ones. Or you'd have to regenerate all static files at every post (too much over-head). It seems to me that Simon's way is right. I wonder why he'd get MySQL choke-ups but. The function mysql_pconnect() was reported to be buggy.
I have the same problem as Junjan: "it always shows up only one result and it is the very same entry." Well, that happened on some entries, in at least one, there are none (when there should be more, I checked ones that should have lots).
Excerpt: The keyword index will work, but I've got a lot of work to do on my keywords before I can bring it live. In the meantime, I've re-implemented Adam Kalsey's 'related entries' code, listing five similar entries in the sidebar of each individual entry page.
Excerpt: An extremely useful site I came across while installing pluggins for movable type is Adam Kelseys blog which contains such beauties as the related entries plugin which I'm attempting to implement right now (had it working am just trying to...
Thanks for this idea. I tried a few alternatives, and your solution is the most elegant for displaying related entries.
I realize that I'm about two years behind the curve on this one, but I just implemented this great little hack on my blog today. Very nice!
Is this plugin compatible with WordPress?
Now that I have been using this query in my MT individual archives for a bit over a month, I have found one shortcoming. See this page for an example: http://full-speed.org/archives/2006/06/13/inaudible_ring_tones.php (Adam, feel free to delete that link once you've checked it out.) Basically, when the query doesn't find related entries, nothing is shown. I would really like to fix that. Whether it be a simple message that says that there were no related entries found or (preferably in my case) displaying some less relevant links. I have dealt with MySQL quite a bit, but this FULLTEXT stuff is new to me. And MTSQL is something that I have only used in a copy & paste manner. Any ideas?
Sorry for so many postings here, but I have been hacking up your query a bit and have a result that at least gives me _something_. It's far from perfect at this point, but I now have _some_ links displaying, however irrelevant. What I did was replace MTEntryKeywords (a field that was returning an empty string for my blog postings) with MTEntryExcerpt in two places in the query. Like I said, it's not perfect, but at least I have some links now. Back to hacking that query... :)
hi there is that possible to we find the re;ated entries of one post, from other blogs entries? HOW??!!! How we should change the code? please mail me the method .. thanks
This discussion has been closed.
Trackback from Random Neural Misfirings
May 1, 2003 12:21 PM
Natural keywords and categories
Excerpt: How to extract keywords in blogs without even trying