1

I know this is partly a Twig question, but I've poked around quite a bit here, in Twig docs, Craft docs, and cannot find the right syntax.

I have a bunch of entries with titles like:

  • Hotel A
  • Grand Hotel B
  • Zither Hotel Grand Plaza
  • The Top Hotel

I want to order by title, alphabetical, but I need to exclude leading 'The ' from the order. I've tried this, but it barfs.

 {% for entry in craft.entries.section('hotels').order('(CASE when title LIKE 
'%The ' THEN LTRIM(title, 4) ELSE title END )') %}
  • How does it barf? Is there an error message? Also, pretty sure the LIKE should be 'The %', since you want to it start with The and match anything after it. – Brad Bell Feb 5 '17 at 0:34
  • I get Arguments must be separated by a comma. Unexpected token "string" of value " THEN LTRIM(title, 4) ELSE title END )" ("punctuation" expected with value ","). – 4midori Feb 6 '17 at 16:46
2

Completely untested, but try something like this:

{% for entry in craft.entries.section('hotels').order('(CASE WHEN title LIKE 'The %' THEN SUBSTRING(title, 4) ELSE title END )') %}

MySQL's LTRIM isn't what you want because that just removes leading spaces. SUBSTRING should do it, though.

Also, keep in mind this is a MySQL specific query. If you ever planning on running on PostgreSQL (that the Craft 3 beta supports), it would have to be adjusted.

|improve this answer|||||
  • Now I get Arguments must be separated by a comma. Unexpected token "name" of value "The" ("punctuation" expected with value ","). Can I pull all the entries into an array and use Twig to filter? If so, is there an example available? Thanks. – 4midori Feb 7 '17 at 3:12
1

Just a little tweak, and double quotes needed around the thing it is LIKE.

{% for entry in craft.entries.section('hotels').order('(CASE WHEN title LIKE "The %" THEN SUBSTRING(title, 3) ELSE title END ) asc') %}
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.