Gossamer Forum
Home : Products : Gossamer Links : Development, Plugins and Globals :

Omit leading "The" in sort, but still display it.

Quote Reply
Omit leading "The" in sort, but still display it.
Hi,

I am using a global to display my category listing, I would like to omit the leading "The" in the sort, but still display it, here is my code:
Code:
sub {
my ($db, $sth, $row, $oldletter, $letter, $i);
my $output="";

$db = $DB->table("Category");
$db->select_options("ORDER BY Name");
$sth = $db->select( ["ID","Name", "Full_Name"] );

my $cols = 1;
my $row_count = $sth->rows;
my $breakpoint = int (($row_count) / $cols) + ( (($row_count) % $cols) ? 1 : 0);
my $width = int (100 / $cols);
my $output = qq|\n|;

while ($row = $sth->fetchrow_hashref) {
$letter = uc(substr($row->{Name}, 0, 1));
if ($letter !~ /$oldletter/i ){
$output .= qq|<div class="col-sm-12 center"><div class="feature-box"><div class="feature-box-info"><h4 class="tall"><a name="#$letter"><strong>$letter</strong></a></h4></div></div></div>\n|;
$oldletter = $letter;
}
my $name = $db->as_url($row->{Full_Name});
$output .= qq|<div class="col-sm-4"><div class="feature-box"><div class="feature-box-info"><h4 class="tall"><a href="$CFG->{build_root_url}/$name/">$row->{Name}</a></h4></div></div></div>\n|;
($i > 0) and !($i % $breakpoint) and ($output .= qq|\n\n|);
$i++;
}
$output .= "\n";
return $output;
}

I would have to use a statement like:
Code:
select Name, case when Name like 'The %' then trim(substr(Name from 4)) else Name end as Name2 from tablename order by Name2;

but not exactly sure where and how... Anyone ?
Quote Reply
Re: [macbethgr] Omit leading "The" in sort, but still display it. In reply to
Why not have another field - without the "The" in? :) That'd by far be the easiest (and most efficient) way to do it.

Alternatively, you could try this (not tested, so may not even work!)

Code:
$sth = $db->select( ["ID","Name", "Full_Name","case when Name like 'The %' then trim(substr(Name from 4)) else Name end as Name2"] );

Hope that helps

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Omit leading "The" in sort, but still display it. In reply to
Thanks for the suggestion Andy, I did consider that already, but that would be a lot of work with close to 20,000 categories, and since we are working with a database I should be able to use a sql statement to achieve what I am looking for.
Quote Reply
Re: [macbethgr] Omit leading "The" in sort, but still display it. In reply to
Hi,

Not that hard :)

Add a new field called Name2. Then just run a SQL to populate it:

Code:
UPDATE glinks_Category SET Name2 = REPLACE(Name,"The ","");

It seems REPLACE does not let you do a replace using ^ as an anchor to the rule - so if you had something like:

"Something The Fool"

It would convert to:

"Something Fool"

If you do have that, lemme know and I'll knock up a simple script to do it for ya ;) (only a few lines of code)

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Omit leading "The" in sort, but still display it. In reply to
Thank you Andy.

I know exactly how to add another field to the database, that's not what I am looking for.

I think adding another field is not efficient, I already have what I need from the original name field, why would I want to have to type the information in twice when I should be able to simply omit the leading "The" with sql code?

I agree that adding the field itself is easy, but you are doubling the amount of time it takes to add a category name, not to mention the possibility of a typo or simply forgetting to enter the information in the second field. That solution may be fine for a small database with just a few categories, but in my case it's not the solution.

Thanks, I'll play around with the sql code and see what I come up with, was just wondering if someone else already had a solution.
Quote Reply
Re: [Andy] Omit leading "The" in sort, but still display it. In reply to
Hmmmm.

Maybe I'll try adding a sql view to the database and running my query off that????
Quote Reply
Re: [Andy] Omit leading "The" in sort, but still display it. In reply to
OK Andy!

Your first suggestion, untested worked like a charm!!!!! Here is my finished code:

Code:
sub {
my ($db, $sth, $row, $oldletter, $letter, $i);
my $output="";

$db = $DB->table("Category");
$db->select_options("ORDER BY Name2");
$sth = $db->select( ["ID","Name", "Full_Name","case when Name like 'The %' then trim(substr(Name from 4)) else Name end as Name2"] );

my $cols = 1;
my $row_count = $sth->rows;
my $breakpoint = int (($row_count) / $cols) + ( (($row_count) % $cols) ? 1 : 0);
my $width = int (100 / $cols);
my $output = qq|\n|;

while ($row = $sth->fetchrow_hashref) {
$letter = uc(substr($row->{Name2}, 0, 1));
if ($letter !~ /$oldletter/i ){
$output .= qq|<div class="col-sm-12 center"><div class="feature-box"><div class="feature-box-info"><h4 class="tall"><a name="#$letter"><strong>$letter</strong></a></h4></div></div></div>\n|;
$oldletter = $letter;
}
my $name = $db->as_url($row->{Full_Name});
$output .= qq|<div class="col-sm-4"><div class="feature-box"><div class="feature-box-info"><h4 class="tall"><a href="$CFG->{build_root_url}/$name/">$row->{Name}</a></h4></div></div></div>\n|;
($i > 0) and !($i % $breakpoint) and ($output .= qq|\n\n|);
$i++;
}
$output .= "\n";
return $output;
}

You're the best!
Quote Reply
Re: [macbethgr] Omit leading "The" in sort, but still display it. In reply to
NP - glad it worked =) Angelic

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!