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
A
An
The
"A
"An
"The
Re: [zip222] 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:
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!
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
PHP Programmer - interactivetools.com
Re: [zip222] Order by title, but skip "The", "A", "An", etc
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:
Thanks
Greg
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
PHP Programmer - interactivetools.com
Re: [zip222] Order by title, but skip "The", "A", "An", etc
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.