Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

#1 2016-08-24 20:41:34

jakob
Admin
From: Germany
Registered: 2005-01-20
Posts: 4,578
Website

Group by year but sort alphabetically?

So I made a page that neatly lists 80-odd projects and divides it into togglable year groups using if_different to insert a year heading and toggle panel wrappers at the beginning / end of each year. The client loved it, then made the perfectly reasonable suggestion:

“Can we order the projects alphabetically within each year?”

Off the top of my head, I said:

“Sure, I can probably do that right away…” thinking I could just do sort="Posted desc, Title desc".

Hmmm. Famous last words! Because I’m using if_different to insert my year headings, that sort trick won’t work. Is there an efficient way to do this?

One option I think I could try:
  1. Do a reversed sort query with limit="1" to retrieve the year of the oldest article in that section and store it in a variable. Something like: <txp:article_custom section="projects" limit="1" sort="Posted asc"><txp:posted format="%Y" /></txp:article_custom>.
  2. Do the same for the newest article using normal sort order Posted desc to get the year of the newest project (or just use this year). Store that in a variable.
  3. Use rah_repeat with the range="min, max, step" attribute as a wrapper for <txp:article_custom section="projects" month='<txp:rah_repeat_value />' sort="Title desc">…</txp:article_custom> to output each year’s projects alphabetically.

I guess that will work but it now requires 1 (or 2) queries up front and then one for each year, rather than just one query for the whole list. It’s no disaster as I plan on aks_caching the result but my question is: Is there a better / more efficient way to do this?


TXP Builders – finely-crafted code, design and txp

Offline

#2 2016-08-24 21:20:51

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 11,250
Website GitHub

Re: Group by year but sort alphabetically?

<txp:article sort="YEAR(Posted) desc, Title asc" limit="999">
...
</txp:article>

?


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

#3 2016-08-24 22:15:46

jakob
Admin
From: Germany
Registered: 2005-01-20
Posts: 4,578
Website

Re: Group by year but sort alphabetically?

Brilliant, That works! So simple by comparison.

Does that appear anywhere in the documentation?

Thank you!


TXP Builders – finely-crafted code, design and txp

Offline

#4 2016-08-25 10:49:34

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 11,250
Website GitHub

Re: Group by year but sort alphabetically?

Glad it works. Its not in the docs as far as I can tell. Candidate for a TxpTip? Or an example in the docs under the article tag?


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

#5 2016-08-25 12:16:49

uli
Moderator
From: Cologne
Registered: 2006-08-15
Posts: 4,303

Re: Group by year but sort alphabetically?

What language is YEAR(Posted) desc, Title asc actually?


In bad weather I never leave home without wet_plugout, smd_where_used and adi_form_links

Offline

#6 2016-08-25 12:25:41

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 11,250
Website GitHub

Re: Group by year but sort alphabetically?

uli wrote #300789:

What language is YEAR(Posted) desc, Title asc actually?

SQL.


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

#7 2016-08-25 12:32:10

uli
Moderator
From: Cologne
Registered: 2006-08-15
Posts: 4,303

Re: Group by year but sort alphabetically?

Ah, so it might also be a quick tip for our SQL sticky topic.


In bad weather I never leave home without wet_plugout, smd_where_used and adi_form_links

Offline

Board footer

Powered by FluxBB