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

Displaying Newest Links on Homepage

Quote Reply
Displaying Newest Links on Homepage
I have the following global which works great to show the top 5 latest links on my homepage, and restricted to a certain length. I was wondering how I can amend this to only include links from a certain category, for example, only links from category with ID 500.

Thanks!

Code:

sub {
# Displays the newest links on the home page.
my ($output,$sth,$link);
my $search_db = $DB->table('Links');
$search_db->select_options ('ORDER BY ID DESC Limit 5');
$sth = $search_db->select ( { isValidated => 'Yes' });
while ($link = $sth->fetchrow_hashref) {
if (length $link->{Title} > 31) {
$link->{Title} = substr($link->{Title}, 0, 28) . '...';
}
$output .= Links::SiteHTML::display ('link4', $link);
}
return $output;
}
Quote Reply
Re: [Jobu] Displaying Newest Links on Homepage In reply to
Never mind, figured it out!
Quote Reply
Re: [Jobu] Displaying Newest Links on Homepage In reply to
You know, I just noticed that with this global, if a link is in multiple categories, it will be selected multiple times. Is there a way to restrict this to unique IDs?

Thanks.
Quote Reply
Re: [Jobu] Displaying Newest Links on Homepage In reply to
What's the code you're using?

Philip
------------------
Limecat is not pleased.
Quote Reply
Re: [fuzzy logic] Displaying Newest Links on Homepage In reply to
Code:
sub {
# Displays the newest news links on the home page.
my ($output,$sth,$link);
my $search_db = $DB->table('Links', 'CatLinks');
$search_db->select_options ('ORDER BY ID DESC Limit 5');
my $cond = GT::SQL::Condition->new(IsValidated => '=' => 'Yes', CategoryID => '<>' => 1274);
$cond->bool('AND');
$sth = $search_db->select ($cond);
while ($link = $sth->fetchrow_hashref) {
if (length $link->{Title} > 31) {
$link->{Title} = substr($link->{Title}, 0, 28) . '...';
}
$output .= Links::SiteHTML::display ('link4', $link);
}
return $output;
}
Quote Reply
Re: [Jobu] Displaying Newest Links on Homepage In reply to
I think you can do something like this:

Code:

$search_db->select({Links.IsValidated => 'Yes', CatLinks.CategoryID => 1274}, 'DISTINCT(CatLinks.LinkID)');

- Jonathan
Quote Reply
Re: [jdgamble] Displaying Newest Links on Homepage In reply to
Thanks, I will try this.

One other question. Say I have the following category hierarchy:

Cat 1
Cat 2
Cat 3
Cat 4
Cat 5
Cat 6
Cat 7
Cat 8
Cat 9
...

How would I show all links from Cat 1 and below (i.e., Cat 1 - 7), but not other links?
Quote Reply
Re: [Jobu] Displaying Newest Links on Homepage In reply to
You would have to be more specific, but I'm guessing you would use a condition statement like:

Code:

my $cond = GT::SQL::Condition->new(

Name => '<' => 'Cat 8'
);


It just depends on your category names and EXACTLY what you are trying to do.

- Jonathan
Quote Reply
Re: [jdgamble] Displaying Newest Links on Homepage In reply to
Yeah, I can get it working if I specify the categories. But I was wondering if there is a way to do it so that all subcategories under the main category are selected, so taht if category names/#s change, I don't have to redo the global.
Quote Reply
Re: [Jobu] Displaying Newest Links on Homepage In reply to
Code:
my $id = 4;
my $children = $DB->table("Category")->children($id);
#push @$children, $id;
my $cond = GT::SQL::Condition->new(ID => "IN" => $children);

remove the '#' if you want to include $id, as otherwise ONLY $id's children is included.

Philip
------------------
Limecat is not pleased.
Quote Reply
Re: [jdgamble] Displaying Newest Links on Homepage In reply to
In Reply To:
I think you can do something like this:

Code:

$search_db->select({Links.IsValidated => 'Yes', CatLinks.CategoryID => 1274}, 'DISTINCT(CatLinks.LinkID)');


I don't think this works. Currently the following global works, but if a link goes in > 1 cats, I get duplicate results.

Code:

sub {
# Displays the newest news links on the home page.
my ($output,$sth,$link);
my $search_db = $DB->table('Links', 'CatLinks');
$search_db->select_options ('ORDER BY ID DESC Limit 5');
my $cond = GT::SQL::Condition->new(IsValidated => '=' => 'Yes', CategoryID => '<>' => 1274);
$cond->bool('AND');
$sth = $search_db->select ($cond);
while ($link = $sth->fetchrow_hashref) {
if (length $link->{Title} > 30) {
$link->{Title} = substr($link->{Title}, 0, 27) . '...';
}
$output .= Links::SiteHTML::display ('link4', $link);
}
return $output;
}
Quote Reply
Re: [Jobu] Displaying Newest Links on Homepage In reply to
Try this:

Code:

$sth = $search_db->select (['DISTINCT(CatLinks.LinkID)'], $cond);

- Jonathan
Quote Reply
Re: [jdgamble] Displaying Newest Links on Homepage In reply to
In Reply To:
Try this:

Code:

$sth = $search_db->select (['DISTINCT(CatLinks.LinkID)'], $cond);


Here's the error when I do this instead of the original:

Code:


A fatal error has occured:
GT::SQL::Relation (12476): Bad columns / column clash: columns named 'DISTINCT(CatLinks.LinkID)' have been found in current relation, please qualify your expression at (eval 35) line 8.

Please enable debugging in setup for more details.
Quote Reply
Re: [jdgamble] Displaying Newest Links on Homepage In reply to
And if I change "CatLinks.LinkID" to "LinkID" I get an unknown tag (Title) error for each New link instead of the links themselves.
Quote Reply
Re: [Jobu] Displaying Newest Links on Homepage In reply to
Hmmm, try it without the []... there seems to be similar unsolved problems:

http://www.gossamer-threads.com/...20expression;#262820

http://www.gossamer-threads.com/...20expression;#203437

while using DISTINCT and other functions in select.

- Jonathan

Last edited by:

jdgamble: Sep 24, 2006, 4:04 PM
Quote Reply
Re: [jdgamble] Displaying Newest Links on Homepage In reply to
In Reply To:
Hmmm, try it without the []... there seems to be similar unsolved problems:

http://www.gossamer-threads.com/...20expression;#262820

http://www.gossamer-threads.com/...20expression;#203437

while using DISTINCT and other functions in select.


Same error. Again, when I use DISTINCT(ID) it appears to work, except that the Title of the link is returned as unknown.
Quote Reply
Re: [Jobu] Displaying Newest Links on Homepage In reply to
Although I think what you have for this part is fine, here is just another options to mess with.

Code:

my $cond = GT::SQL::Condition->new(Links.IsValidated => '=' => 'Yes', CatLinks.CategoryID => '<>' => 1274);



But first try referencing it like this:

Code:

$sth = $search_db->select(\'DISTINCT(CatLinks.LinkID)', $cond);


I can't imaging why it would not pull the title, unless it is just pulling from the CatLinks db, in which the title doesn't exists. It may also send the hash of variables as $rec->{Links.Title}.

Maybe something I said here will help,


- Jonathan
Quote Reply
Re: [jdgamble] Displaying Newest Links on Homepage In reply to
In Reply To:
Although I think what you have for this part is fine, here is just another options to mess with.

Code:

my $cond = GT::SQL::Condition->new(Links.IsValidated => '=' => 'Yes', CatLinks.CategoryID => '<>' => 1274);




But first try referencing it like this:

Code:

$sth = $search_db->select(\'DISTINCT(CatLinks.LinkID)', $cond);



I can't imaging why it would not pull the title, unless it is just pulling from the CatLinks db, in which the title doesn't exists. It may also send the hash of variables as $rec->{Links.Title}.

Maybe something I said here will help,


Thanks for the help, but no luck. The closest we get is if I add 'DISTINCT(ID)' to the select, but then while I get the proper IDs pulled, no Title. So maybe it is taking from the wrong table?
Quote Reply
Re: [Jobu] Displaying Newest Links on Homepage In reply to
Quote:
Thanks for the help, but no luck. The closest we get is if I add 'DISTINCT(ID)' to the select, but then while I get the proper IDs pulled, no Title. So maybe it is taking from the wrong table?

When you implicity specify a column to return, you MUST specificy ALL columns you want (ie, add 'Links.*' to get all the columns from the Links table)

Philip
------------------
Limecat is not pleased.
Quote Reply
Re: [fuzzy logic] Displaying Newest Links on Homepage In reply to
I'm not really sure what you are suggesting. I tried prefixing all variable names with the table as follows:

Code:

sub {
# Displays the newest news links on the home page.
my ($output,$sth,$link);
my $search_db = $DB->table('Links', 'CatLinks');
$search_db->select_options ('ORDER BY Links.ID DESC Limit 5');
my $cond = GT::SQL::Condition->new(Links.IsValidated => '=' => 'Yes', CatLinks.CategoryID => '<>' => 1274);
$cond->bool('AND');
$sth = $search_db->select ('DISTINCT(CatLinks.LinkID)', $cond);
while ($link = $sth->fetchrow_hashref) {
if (length $link->{Links.Title} > 30) {
$link->{Links.Title} = substr($link->{Links.Title}, 0, 27) . '...';
}
$output .= Links::SiteHTML::display ('link4', $link);
}
return $output;
}


And I get the error as follows:

Code:


Unable to compile 'newest_links': Bareword "Links" not allowed while "strict subs" in use at (eval 35) line 6. Bareword "CatLinks" not allowed while "strict subs" in use at (eval 35) line 6. Bareword "Links" not allowed while "strict subs" in use at (eval 35) line 11. Bareword "Title" not allowed while "strict subs" in use at (eval 35) line 11. Bareword "Links" not allowed while "strict subs" in use at (eval 35) line 12. Bareword "Title" not allowed while "strict subs" in use at (eval 35) line 12. Bareword "Links" not allowed while "strict subs" in use at (eval 35) line 12. Bareword "Title" not allowed while "strict subs" in use at (eval 35) line 12.
Quote Reply
Re: [Jobu] Displaying Newest Links on Homepage In reply to
What I mean is:

Code:
'DISTINCT(CatLinks.ID), Links.*'


Otherwise, the only value returned in the select is from 'DISTINCT(CatLinks.ID)'.

And you cannot have hash keys like Links.ID unless it's quoted, like "Links.ID"

Anyhow, I don't think that's how you should be doing things...

I think what you really want is:

Code:
sub {
# Displays the newest news links on the home page.
my ($output,$sth,$link);
my $search_db = $DB->table('Links', 'CatLinks');
$search_db->select_options ('GROUP BY Links.ID ORDER BY Links.ID DESC Limit 5');
my $cond = GT::SQL::Condition->new(Links.IsValidated => '=' => 'Yes', CatLinks.CategoryID => '<>' => 1274);
$cond->bool('AND');
$sth = $search_db->select ($cond);
while ($link = $sth->fetchrow_hashref) {
if (length $link->{Title} > 30) {
$link->{Title} = substr($link->{Title}, 0, 27) . '...';
}
$output .= Links::SiteHTML::display ('link4', $link);
}
return $output;
}

Philip
------------------
Limecat is not pleased.
Quote Reply
Re: [fuzzy logic] Displaying Newest Links on Homepage In reply to
Thanks for all the help, I really do appreciate it! I tried that latest code, and got the following error where I called teh global:

Code:

Unable to compile 'newest_links': Bareword "Links" not allowed while "strict subs" in use at (eval 35) line 6. Bareword "CatLinks" not allowed while "strict subs" in use at (eval 35) line 6.
Quote Reply
Re: [Jobu] Displaying Newest Links on Homepage In reply to
change:

Code:
my $cond = GT::SQL::Condition->new(Links.IsValidated => '=' => 'Yes', CatLinks.CategoryID => '<>' => 1274);

To:

Code:
my $cond = GT::SQL::Condition->new("Links.IsValidated" => '=' => 'Yes', "CatLinks.CategoryID" => '<>' => 1274);

again, most special characters are not allowed in that context (exception: '_', since _ is valid in variable and subroutine names)

Philip
------------------
Limecat is not pleased.
Quote Reply
Re: [fuzzy logic] Displaying Newest Links on Homepage In reply to
Thanks, but now another error :)

In Reply To:

Can't call method "fetchrow_hashref" on an undefined value at (eval 35) line 9.[/quote]