Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
How do I sort an article based on age and a custom field value?
My client wants to highlight certain articles by an internal rating system, and output a list of rated articles to the homepage based on the rating and age of the article as follows:
<txp:custom_field name="rating" />is our rating for the article, which will be a number from 1 to 100.<txp:custom_field name="rating_date" />is the birth of the rating— the date that a site editor rated the article.- The age of a rating is today –
<txp:custom_field name="rating_date" />.
His requirements are to sort rated articles so that articles with a higher rating are ranked higher than those with a lower rating, in such a way that age deteriorates an article’s rank in the list.
He proposed other variables to work this out mathematically:
<txp:variable name="rating_legs" />: the higher this variable, the less age diminishes the ranking.<txp:variable name="rating_accellerator" />: is an accelerator on how quickly age (that is, Today –<txp:custom_field name="rating_date" />) creates staleness. If this accellerator is larger, then staleness is quicker.
The formula:
Sort order (descending) = <txp:custom_field name="rating" /> * <txp:variable name="rating_legs" /> / (<txp:variable name="rating_legs" /> + <txp:variable name="rating_accellerator" />*(Today – <txp:custom_field name="rating_date" />))
Does this make sense at all? I explained to him that Textpattern can sort articles based on any column in the database table, like a spreadsheet, and that something this creative might not be possible.
You guys have blown my mind before with awesome solutions— what are my options here? What are ways to write a <txp:article /> tag that grabs articles with any value in the rating field and sorts them in a sensible way, taking age and rating into account?
Offline
Re: How do I sort an article based on age and a custom field value?
Seems very complex. Couldn’t you just use something like sort="custom_1 desc, Posted desc" with the custom field number relating to the field used for “rating”?
Stuart
In a Time of Universal Deceit
Telling the Truth is Revolutionary.
Offline
Re: How do I sort an article based on age and a custom field value?
At a high level (not thought it through entirely), adi_calc can do some of the maths heavy lifting with some judicial tags-in-tags stuff. Though you might want to check out the recently released aks_var which may be able to do it in one tag if you structure the maths correctly. Whether you can actually embed that maths inside an article tag and sort by it at the same time remains to be seen. I’d expect not, but I’ve been wrong before (a two-pass approach might work, though it’s messy).
smd_query should help if you’re a dab hand at SQL because you can create a custom ORDER BY and may be able to use some of the MySQL maths routines to simplify things. But you may run aground trying to do it all in one go. What people tend to do is create an extra column in the result set that is based on some calculation you perform on the current row, then ORDER BY that column. Or you can get stupidly freaky (scary!)
I’d go the SQL route myself but it does imply you have to do the heavy lifting of the article tag yourself, which is a bit of a pain. Others might come up with better solutions that are more native / easier.
Last edited by Bloke (2009-03-30 20:50:42)
The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.
Txp Builders – finely-crafted code, design and Txp
Offline
Re: How do I sort an article based on age and a custom field value?
Thank you Stuart & Stef!
@thebombsite: This sort is exactly what I need for the article list in the section for highlighted articles. The reason for the complex sort criteria above is that my client wants to push newer content to the homepage, weighted by rating. If sort="Posted desc, custom_x desc" is the best I can do for the homepage, that’s what I’ll do.
@bloke: Stef, you always get behind a fellow facing an impossible scenario, and I’m immensely grateful! The smd_query + create an extra column method looks very promising. But looking at SQL and trying to express the problem in SQL terms is like smashing by brain against the language barrier at mach 5. The article you posted on performing math with SQL seems almost plain enough to get my head around, but when I throw in the txp:variables and custom fields, I have a seizure.
Do you have an example smd_query to show how a SQL calculation might work with one txp:variable? If this topic is too cracked out for this forum, I wouldn’t mind posting it on stackoverflow, but how would I even phrase the question?
Would smd_query regenerate the database column every time the page loads?
Thanks again for your guidance and support!
Offline