<blockquote>
  <p>How to achieve the same functionality on Craft 3?</p>
</blockquote>

<p>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:</p>

<pre><code>{% 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() %}
</code></pre>

CC BY-SA 4.0
2019-04-08T08:17:49.570Z
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
CC BY-SA 4.0
2020-07-16T06:39:00.680Z
[This helped me as well, thanks!](https://craftcms.stackexchange.com/questions/35805/orderby-title-but-remove-the-word-the-from-titles)
MarkD
CC BY-SA 4.0
2019-03-30T23:23:12.390Z
2019-03-30T23:23:12.390Z
Brad Bell
<p>I have this code which used to work with 2.x versions:</p>

<pre><code>{% 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") %}
</code></pre>

<p>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.</p>

<p>However the code is giving a SQL error message:</p>

<pre><code>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
</code></pre>

<p>How to achieve the same functionality on Craft 3?</p>

<p>SOLVED (thanks to Brad):</p>

<pre><code>{% 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() %}
</code></pre>

<p>This is still unsolved:</p>

<p>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.</p>

<p>In Craft 2 this was done with:</p>

<pre><code>{% 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) %}
</code></pre>

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

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

<p>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.</p>

<p>Now I've tried using this:</p>

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

{% set prev = clone(query).title("&lt; " ~ 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("&gt; " ~ 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() %}
</code></pre>

<p>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".</p>

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

<pre><code>{% 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) &lt; 'Foobar'").orderBy("(CASE WHEN `title` LIKE 'The %' THEN SUBSTRING(`title`, 5) WHEN `title` LIKE 'A %' THEN SUBSTRING(`title`, 3) ELSE `title` END) desc").one() %} ?
</code></pre>

<templating><craft3><ordering>
Ignoring "The " and "A " when sorting entries
# Ignoring "The " and "A " when sorting entries

(Asked by: username_0 on 2019-03-26)

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

**Tags:** templating, craft3, ordering

---

## 1 answers

### Answer by username_1 on 2019-03-30. Score: 3

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

> **Comments:**
> **username_0**: 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.  
> **username_2**: [This helped me as well, thanks!](https://craftcms.stackexchange.com/questions/35805/orderby-title-but-remove-the-word-the-from-titles)  

---

