Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-04-08T08:17:49.570Z
Answers.Text: 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.
Answers.UserDisplayName: LosHosez
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-07-16T06:39:00.680Z
Answers.Text: [This helped me as well, thanks!](https://craftcms.stackexchange.com/questions/35805/orderby-title-but-remove-the-word-the-from-titles)
Answers.UserDisplayName: MarkD
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-03-30T23:23:12.390Z
Answers.LastActivityDate: 2019-03-30T23:23:12.390Z
Answers.UserDisplayName: Brad Bell
Body: <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>
  
Tags: <templating><craft3><ordering>
Title: Ignoring "The " and "A " when sorting entries
ThreadText: # 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 username_1):
  
  ```
  {% 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 username_1! 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)  
  
  ---
  
  