Textpattern CMS

#1 2011-04-14 00:04:12

nabrown78

Member

From: Northampton, MA, USA

Registered: 2006-10-04

Posts: 294

Website

List of articles related by keywords -- weighted by relevance?

Hi All, I realized I posted this on an orphaned thread for chh_related_articles:

This plugin is doing a simple match, and sorting the results by date or whatever you specify. What I really want is to sort by some kind of relevance. Like an article that shares 3 Keywords with the current article is more relevant than one that shares only 2 Keywords.

I got as far as checking out the MySQL function MATCH…AGAINST …but I’m stumped. Would anyone know how to modify this plugin to achieve sorting by relevance? Or more simply, how to just write the correct MySQL query?

Thanks for any help from MySQL experts!

#2 2011-04-14 00:38:46

nabrown78

Member

From: Northampton, MA, USA

Registered: 2006-10-04

Posts: 294

Website

Re: List of articles related by keywords -- weighted by relevance?

Okay, I actually made some progress by messing around with different queries in PHPMyAdmin. First, I created a FULLTEXT index on the Keywords field in the textpattern table. Then I can run a query like so:

SELECT * FROM `textpattern` WHERE MATCH (Keywords) AGAINST ('dog,adoption,sentimental')

and I get what I want. Now I just need to figure out how to integrate it plugin-style, either into chh_related_keywords or a new plugin. Help in this arena also very much appreciated!

#3 2011-04-14 11:13:52

redbot

Plugin Author

Registered: 2006-02-14

Posts: 1,410

Re: List of articles related by keywords -- weighted by relevance?

Hi Nora,
probably I’m missing something but I would go this way:

1) stop using chh_related_keywords and chh_related_articles. You don’t need them.
2) install smd_query
3) Assuming your query is right, create a txp variable which holds the query results (fetched throught smd_query).
If you are using txp 4.4 (which fixes a bug with txp_variable when used as a container tag) you can try something like:

<txp:variable name="related_ids">
     <txp:smd_query column="id"
     table="textpattern"
     where="MATCH (Keywords) AGAINST ('<txp:keywords />')" />
</txp:variable>

This is not tested, it’s just to give you an idea.

4) After this you can fetch related articles using something like:

<txp:article_custom id='<txp:variable name="related_ids" />' />

Does this make sense or I’m raving?

Last edited by redbot (2011-04-14 15:12:28)

---

Some Txp stuff

#4 2011-04-14 14:10:44

nabrown78

Member

From: Northampton, MA, USA

Registered: 2006-10-04

Posts: 294

Website

Re: List of articles related by keywords -- weighted by relevance?

Thanks for setting me on the right path, redbot!

At first I wasn’t getting any output. I realized it was because <txp:smd_query /> as a single tag needs a form attribute to actually produce output (or at least I think that was the problem), or alternatively you can use it as a container tag. Here is my final code:

<txp:smd_query column="id" table="textpattern" where="MATCH (Keywords) AGAINST ('?keywords') AND section='designs' AND ID != '?id' LIMIT 3">
   <txp:article_custom form="related" id="{id}" />
</txp:smd_query>

Apparently you can use any field in the current context by prefacing it with a ? — so to access the current article’s keywords: ?keywords'. This comes in handy to exclude the current article, which would otherwise be the top match: ID != '?id'

You can see this in action on any individual design page on the Roll Over Rover Threads website.

#5 2011-04-14 15:29:32

redbot

Plugin Author

Registered: 2006-02-14

Posts: 1,410

Re: List of articles related by keywords -- weighted by relevance?

Glad you sorted it out (nice site design by the way).

---

Some Txp stuff