Order by title, but skip "The", "A", "An", etc

5 posts by 3 authors in: Forums > CMS Builder
Last Post: December 11, 2012   (RSS)

By zip222 - December 7, 2012

How can I order a multi record by title, but if the title starts with any of the combinations listed below, the ordering show be based on the second word.

A
An
The
"A
"An
"The

Re: [zip222] Order by title, but skip "The", "A", "An", etc

By gregThomas - December 7, 2012

Hi,

The easiest way to do this is using this orderBy statement in your getRecords function:

list($blogRecords, $blogMetaData) = getRecords(array(
'tableName' => 'blog',
'loadUploads' => true,
'allowSearch' => false,
'orderBy' => 'IF(LEFT(title,2)="A ",SUBSTRING(title FROM 3),IF(LEFT(title,3)="An ",SUBSTRING(title FROM 4),IF(LEFT(title,4)="The ",SUBSTRING(title FROM 5),title)))',
));

The only problem with this method is that it's a fairly server intensive.

Another method is to have a second field called something like title_search in the section, which contains the same text as the title field, but removing A an and the from the beginning of the text. Then setting the orderBy field to title_search in your getRecords function.

Thanks!
Greg Thomas







PHP Programmer - interactivetools.com

Re: [zip222] Order by title, but skip "The", "A", "An", etc

By mizrahi - December 11, 2012

This works well, but I also need to account for situations where the title begins with a quotation mark (")

Re: [zip222] Order by title, but skip "The", "A", "An", etc

By gregThomas - December 11, 2012 - edited: December 11, 2012

Hi,

I modified the statement so that it includes anything starting with a quotation mark as well:

'orderBy' => 'IF(LEFT(title,2)="A ",SUBSTRING(title FROM 3),IF(LEFT(title,1)="\"",SUBSTRING(title FROM 2),IF(LEFT(title,3)="An ",SUBSTRING(title FROM 4),IF(LEFT(title,4)="The ",SUBSTRING(title FROM 5),title))))',

Thanks

Greg
Greg Thomas







PHP Programmer - interactivetools.com