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

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!

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

Thanks greg. In reality this got a good bit more complicated as I also had to account for titles starting with Quote+A, Quote+An, and Quote+The. But the pattern in the code was easy to decipher and I was able to add these as well. thanks again.