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

"Number of reviews" global

Quote Reply
"Number of reviews" global
I'm not sure if this was addressed before. Couldn't find it by searching the forum.
I was wondering if someone can tell me how to develop globals to display:
1. Total number of reviews in the directory
2. Total number of links with reviews

Thanks. Smile
----------------

waffle

Quote Reply
Re: [waffle] "Number of reviews" global In reply to
Total number of reviews is fairly easy,

sub {
return ( $DB->table ('Reviews')->total )
}

Getting the count is trickier, since the 'do' method was apparantly removed. The SQL statement
is

SELECT COUNT(DISTINCT Review_LinkID) FROM Reviews

But I can't figure out how to get it into a method call.


PUGDOG´┐Ż Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [pugdog] "Number of reviews" global In reply to
I *think* this would work for number 2...

$DB->table('Links', 'Reviews')->count;

It will use the foreign key to do the count and so should return the number of links with reviews.

Alternatively this should work....

my $total = $DB->table('Reviews')->('DISTINCT(Review_LinkID)')->fetchall_list;
Quote Reply
Re: [Paul] "Number of reviews" global In reply to
$DB->table('Links', 'Reviews')->count;

Wouldn't that return the total number of reviews rather than the total number of links with reviews? I assume that count just counts the number of rows in the joined table which should have multiple rows per link?

I use rows like this but I'm not sure what the difference is.

my $total = $DB->table('Reviews')->('DISTINCT Review_LinkID')->rows;
Quote Reply
Re: [afinlr] "Number of reviews" global In reply to
Quote:
Wouldn't that return the total number of reviews rather than the total number of links with reviews?

I think the resulting query is something like:

SELECT COUNT(*) FROM Links,Reviews WHERE Links.ID = Reviews.Review_LinkID

So it will count the number of links that have a corresponding review and hence the number of reviews.

I've not tested so I may be talking balls =)
Quote Reply
Re: [Paul] "Number of reviews" global In reply to
I agree with your query but still think that this will give the number of reviews?

Say you have 1 link and it has 5 reviews. Wouldn't the joined table have 5 rows?
Quote Reply
Re: [afinlr] "Number of reviews" global In reply to
Yeah I just tested - you're right.
Quote Reply
Re: [afinlr] "Number of reviews" global In reply to
My revised code :)

Code:
my $table = $DB->table('Links','Reviews');
$table->select_options("GROUP BY ID"); # Needed for query to work.

my $total = $table->select('COUNT(DISTINCT ID)')->fetchrow;
Quote Reply
Re: [Paul] "Number of reviews" global In reply to
What was wrong with my code? Don't see why you need the table join?

my $total = $DB->table('Reviews')->('DISTINCT Review_LinkID')->rows;
Quote Reply
Re: [afinlr] "Number of reviews" global In reply to
Good point - it's late......(that's my excuse) Wink
Quote Reply
Re: [afinlr] "Number of reviews" global In reply to
Oh one thing wrong with your code is you are missing a function name Smile
Quote Reply
Re: [Paul] "Number of reviews" global In reply to
Good point - whoops.