Gossamer Forum: Products: Gossamer Links: Development, Plugins and Globals: Global to list links without reviews

May 17, 2007, 4:03 AM

aus_dave

User (276 posts)

May 17, 2007, 4:03 AM

Post #1 of 8

Shortcut

Global to list links without reviews

I'm trying to write a global to select 20 random links without reviews. I have worked out the SQL statement required but am having trouble getting it to return anything in Links:

Code:
sub { 
# Displays 20 random links with no review 
my ($output,$sth,$link); 
my $search_db = $DB->table('Links', 'Reviews'); 
$search_db->select_options ('ORDER BY RAND()', 'LIMIT 20'); 
$sth = $search_db->select (GT::SQL::Condition->new('isValidated', '=',' Yes', 'Review_LinkID', 'IS',  'NULL')); 
while ($link = $sth->fetchrow_hashref) { 
if (length $link->{Title} > 26) { 
$link->{Titleshort} = substr($link->{Title}, 0, 25) . '...'; 
} 
$output .= Links::SiteHTML::display ('templatename', $link); 
} 
return $output; 
}

I suspect the 'Review_LinkID is NULL' part is the problem, nothing I have tried has worked. Any suggestions appreciated Smile

May 17, 2007, 6:55 AM

Andy

Veteran / Moderator (18436 posts)

May 17, 2007, 6:55 AM

Post #2 of 8

Shortcut

Re: [aus_dave] Global to list links without reviews In reply to

Hi,

The easiest way, would be to make a new .cgi script - say "update_reviews.cgi" in the admin folder, with the following:

Code:
#!/usr/local/bin/perl 

    use strict; 
    use lib '/path/to/admin'; 
    use Links qw/$DB $IN $USER $CFG/; 
    use GT::Mail; 
    use CGI::Carp qw(fatalsToBrowser);  
    use Links::Plugins; 
    use Links; 

    Links::init('/path/to/admin'); 

    my $sth = $DB->table('Links')->select( ['ID'] ) || die $GT::SQL::error; 

    while (my $id = $sth->fetchrow) {       
         my $cnt = $DB->table('Reviews')->count( { Review_LinkID => $id } ) || 0; 
	 $DB->table('Links')->update( { ReviewCount => $cnt }, { ID => $id } ) || die $GT::SQL::error;  
	 print qq|Updated linkID $id with $cnt reviews \n|; 
    }

Then, setup a cronjob to run each day.

perl /path/to/admin/update_reviews.cgi

Then, be sure to add a new field in the "Links" table via:

Database > Links > Properties > Add Column.

Name: ReviewCount
Type: INT

Then, run this script manually (via SSH), and check to see if the links have had their new ReviewCount field updated correctly.

Then, this global should grab the results you want:

Code:
sub { 
   # Displays 20 random links with no review 
   my ($output,$sth,$link); 
   my $search_db = $DB->table('Links'); 
      $search_db->select_options ('ORDER BY RAND()', 'LIMIT 20'); 
   $sth = $search_db->select ( { isValidated => 'Yes', ReviewCount => '0' } ); 
   while ($link = $sth->fetchrow_hashref) { 
     if (length $link->{Title} > 26) { 
       $link->{Titleshort} = substr($link->{Title}, 0, 25) . '...'; 
     } 
     $output .= Links::SiteHTML::display ('templatename', $link); 
   } 
   return $output; 
}

Hope that helps :)

Cheers

Andy (mod)
andy@ultranerds.co.uk

May 17, 2007, 3:01 PM

aus_dave

User (276 posts)

May 17, 2007, 3:01 PM

Post #3 of 8

Shortcut

Re: [Andy] Global to list links without reviews In reply to

I'll keep that in mind Andy - seems to be a roundabout way to do things though. I can get the exact SQL query I need to work fine in a front end client I use, so I thought it would be simple to rewrite that in Links.

If it's not possible to find NULL records in Links then I'll have to use another way I guess.

May 19, 2007, 2:50 AM

Andy

Veteran / Moderator (18436 posts)

May 19, 2007, 2:50 AM

Post #4 of 8

Shortcut

Re: [aus_dave] Global to list links without reviews In reply to

Hi,

What query are you trying to run then? i.e the exact MySQL query. I may be able to help you structure it =)

Cheers

Andy (mod)
andy@ultranerds.co.uk

May 19, 2007, 4:14 AM

aus_dave

User (276 posts)

May 19, 2007, 4:14 AM

Post #5 of 8

Shortcut

Re: [Andy] Global to list links without reviews In reply to

Andy, pretty standard apart from the 'is NULL' part:

Code:
select ID, Title from glinks_Links left join glinks_Reviews on Review_LinkID=ID where Review_LinkID is NULL and isValidated='Yes' order by rand() limit 20

I've looked through the Links help and tried a few things like escaping various parts with backslashes, with no luck :(.

May 22, 2007, 1:23 AM

Andy

Veteran / Moderator (18436 posts)

May 22, 2007, 1:23 AM

Post #6 of 8

Shortcut

Re: [aus_dave] Global to list links without reviews In reply to

Hi,

This may work:

load_links_without_reviews

Code:
sub { 
   my $sth = $DB->table('Links')->do_query(qq|select ID,Title from glinks_Links left join glinks_Reviews on Review_LinkID=ID where Review_LinkID is NULL and isValidated='Yes' order by rand() limit 20|); 
   my @loop; 
   while (my $hit = $sth->fetchrow_hashref) { 
      push @loop, $hit; 
   } 

  return {  links_without_reviews_loop=> \@loop }  

}

Code:
<%load_links_without_reviews%> 
<%loop links_without_reviews_loop%> 
   <%include link.html%> 
<%endloop%>

Took a while to get it right, but think that will do the job for ya =)

Cheers

Andy (mod)
andy@ultranerds.co.uk

May 31, 2007, 7:21 PM

aus_dave

User (276 posts)

May 31, 2007, 7:21 PM

Post #7 of 8

Shortcut

Re: [Andy] Global to list links without reviews In reply to

Thanks Andy, that works perfectly Wink

Jun 1, 2007, 3:09 AM

Andy

Veteran / Moderator (18436 posts)

Jun 1, 2007, 3:09 AM

Post #8 of 8

Shortcut

Re: [aus_dave] Global to list links without reviews In reply to

Glad to hear it :)

Cheers

Andy (mod)
andy@ultranerds.co.uk