1

I have this code which used to work with 2.x versions:

{% set foobars = craft.entries({ section: "foobars"}).order("(CASE WHEN title LIKE 'The %' THEN SUBSTRING(title, 5) WHEN title LIKE 'A %' THEN SUBSTRING(title, 3) ELSE title END) asc") %}

The idea is sort the entries ignoring the possible "The " and "A " prefix in the entry title, so that the order is more natural and what my client expects.

However the code is giving a SQL error message:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '`subquery` INNER JOIN `entries` `entries` ON `entries`.`id` = `subquery`.`elemen' at line 9

How to achieve the same functionality on Craft 3?

SOLVED (thanks to Brad):

{% set foobars = craft.entries({ section: "foobars" }).orderBy("(CASE WHEN `title` LIKE 'The %' THEN SUBSTRING(`title`, 5) WHEN `title` LIKE 'A %' THEN SUBSTRING(`title`, 3) ELSE `title` END) asc").all() %}

This is still unsolved:

In addition I have to get the previous and next entry in this channel based on the entry title, while excluding "The " and "A " in the beginning.

In Craft 2 this was done with:

{% set foobars = craft.entries({ section: "foobars" }).order("(CASE WHEN title LIKE 'The %' THEN SUBSTRING(title, 5) WHEN title LIKE 'A %' THEN SUBSTRING(title, 3) ELSE title END) asc") %}

{% set prev = entry.getPrev(foobars) %}
{% set next = entry.getNext(foobars) %}

In Craft 3 this seems to be a bit more complicated than that. According to what I've discovered this would almost work:

{% set query = craft.entries.section("foobars").id("not " ~ entry.id).limit(1) %}
{% set prev = clone(query).title("<= " ~ entry.title).orderBy("title desc").all() %}
{% set next = clone(query).title(">= " ~ entry.title).orderBy("title asc").all() %}

But I would have to be able to set the orderBy-clause to use the "(CASE WHEN title LIKE 'The %' THEN SUBSTRING(title, 5) WHEN title LIKE 'A %' THEN SUBSTRING(title, 3) ELSE title END) asc", which does not work in Craft 3.

Now I've tried using this:

{% set query = craft.entries.section("foobars").id("not " ~ entry.id).limit(1) %}

{% set prev = clone(query).title("< " ~ entry.title).orderBy("(CASE WHEN `title` LIKE 'The %' THEN SUBSTRING(`title`, 5) WHEN `title` LIKE 'A %' THEN SUBSTRING(`title`, 3) ELSE `title` END) desc").one() %}

{% set next = clone(query).title("> " ~ entry.title).orderBy("(CASE WHEN `title` LIKE 'The %' THEN SUBSTRING(`title`, 5) WHEN `title` LIKE 'A %' THEN SUBSTRING(`title`, 3) ELSE `title` END) asc").one() %}

But the problem is that if I'm on an entry titled "The Foobar", I would need to search for entries around "Foobar" and not "The Foobar".

How would you modify the query, so that it would include the MySQL "(CASE WHEN...)" logic?

{% set prev = clone(query).search("(CASE WHEN `title` LIKE 'The %' THEN SUBSTRING(`title`, 5) WHEN `title` LIKE 'A %' THEN SUBSTRING(`title`, 3) ELSE `title` END) < 'Foobar'").orderBy("(CASE WHEN `title` LIKE 'The %' THEN SUBSTRING(`title`, 5) WHEN `title` LIKE 'A %' THEN SUBSTRING(`title`, 3) ELSE `title` END) desc").one() %} ?
1

How to achieve the same functionality on Craft 3?

This is a MySQL specific answer (Craft 3 supports PostgreSQL as well), but you probably just need to escape your column names like so and that should solve both of your problems:

{% set entries = craft.entries({ section: "test"}).order("(CASE WHEN `title` LIKE 'The %' THEN SUBSTRING(`title`, 5) WHEN `title` LIKE 'A %' THEN SUBSTRING(`title`, 3) ELSE `title` END) asc").all() %}
  • Thanks Brad! This solves the main question. The other issue is moving from one entry to previous/next. I updated the main question to elaborate on this. – LosHosez Apr 8 at 8:17

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.