Textpattern CMS

#1 2017-06-03 17:32:25

uli

Moderator

From: Cologne

Registered: 2006-08-15

Posts: 4,306

[SQL] Unorthodox ordering wanted

I have a custom field with exactly 4 possibilities to fill in via glz_radio_buttons. They’re given and I can’t rename those without causing too much hassle on the (live) site. Now I want to order articles in groups but can’t sort these groups alphabetically cause they are named like

• first group
• second group
• third group
• fourth group

If I build the query with order custom_1 asc the result would be

• first group
• fourth group
• second group
• third group

You get the picture (my query is of course more complex like order custom_1 asc, position asc, BTW). Is it possible to predefine the sort order of these custom field groups so the result would be sorted like the first list?

---

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

#2 2017-06-03 18:48:04

kuopassa

Plugin Author

From: Porvoo, Finland

Registered: 2008-12-03

Posts: 231

Website

Re: [SQL] Unorthodox ordering wanted

Maybe this answers your question? :-) So the query would be something like:

SELECT * FROM `textpattern`
ORDER BY CASE WHEN `custom_1` = 'first group' THEN 1
WHEN `custom_1` = 'second group' THEN 2
ELSE 3
END ASC;

---

Textpattern Plugins

#3 2017-06-03 20:35:18

uli

Moderator

From: Cologne

Registered: 2006-08-15

Posts: 4,306

Re: [SQL] Unorthodox ordering wanted

Great, Petri, that looks like it might work! Will put that in a plugin (customized stm_article_order), get some CSS classes into the HTML markup etc, before I can finally test it.
Thanks for looking around, I didn’t know what I should search for, how call it.

---

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

#4 2017-06-04 00:06:45

GugUser

Member

From: Quito (Ecuador)

Registered: 2007-12-16

Posts: 1,473

Re: [SQL] Unorthodox ordering wanted

I don’t know if this example could be a further approach:

<tbody>
<txp:php>safe_query("SET @ord1='Montag', @ord2='Dienstag', @ord3='Mittwoch', @ord4='Donnerstag', @ord5='Freitag', @ord6='Samstag', @ord7='Sonntag'");</txp:php>
<txp:article_custom category="regelmaessige-kurse" limit="50" sort="FIELD(custom_10, @ord1, @ord2, @ord3, @ord4, @ord5, @ord6, @ord7)" form="tr-EducationEvent-mit-Tag" />
</tbody>

#5 2017-06-04 13:52:32

uli

Moderator

From: Cologne

Registered: 2006-08-15

Posts: 4,306

Re: [SQL] Unorthodox ordering wanted

Thanks, GugUser, good feeling to have that up my sleeve :)

---

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

#6 2017-06-05 19:14:41

uli

Moderator

From: Cologne

Registered: 2006-08-15

Posts: 4,306

Re: [SQL] Unorthodox ordering wanted

This worked right from the first attempt¹ :) Will add that technique to our SQL topic sometime soon.

Thanks again to both of you!

¹ kuopassa’s code worked too easily to try GugUser’s.

---

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