Ignoring "The " and "A " when sorting entries

Question

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() %} ?

Answer 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() %}