Page 1 of 1
s9y 0.9-beta2, statistics and Postgres
Posted: Fri Oct 14, 2005 10:16 am
by CaptainCrunch
I just tried out the new beta (Postgres 7.4.7 as DB), and I'm kinda impressed by its new functionality and speed.
Otoh, there's one thing that's annoying me a lot:
Whenever I want to have a look at the statistics, the SQL-clauses regarding the Karma-plugin throws many errors regarding the timestamp-table:
Warning: pg_query(): Query failed: ERROR: column "e.timestamp" must appear in the GROUP BY clause or be used in an aggregate function in /var/www/blog/include/db/postgres.inc.php on line 111
Error in SELECT e.id, e.title, e.timestamp, SUM(k.visits) AS no FROM serendipity_karma AS k JOIN serendipity_entries AS e ON k.entryid = e.id WHERE k.visits IS NOT NULL AND k.visits != 0 GROUP BY e.id, e.title ORDER BY no DESC LIMIT 25
ERROR: column "e.timestamp" must appear in the GROUP BY clause or be used in an aggregate function
It'd be just great if this could be fixed for the final release. Keep up the great work guys!
Possible bugfix
Posted: Fri Oct 14, 2005 10:22 am
by CaptainCrunch
The following SQL-statement works for me:
Code: Select all
SELECT e.id, e.title, e.timestamp, SUM(k.visits) AS no FROM serendipity_karma AS k JOIN serendipity_entries AS e ON k.entryid = e.id WHERE k.visits IS NOT NULL AND k.visits != 0 GROUP BY e.id, e.title, e.timestamp ORDER BY no DESC LIMIT 25;
Hth
Fixed
Posted: Fri Oct 14, 2005 11:05 am
by CaptainCrunch
Sorry for that rather boring monologue...
Here's the fix for that sucker:
Code: Select all
--- serendipity_event_karma.php.old 2005-10-14 08:50:21.000000000 +0200
+++ serendipity_event_karma.php 2005-10-14 08:50:45.000000000 +0200
@@ -392,7 +392,7 @@
AS e
ON k.entryid = e.id
WHERE k.{$rows[0]} IS NOT NULL AND k.{$rows[0]} != 0
- GROUP BY e.id, e.title ORDER BY no {$rows[1]} LIMIT {$addData['maxitems']}";
+ GROUP BY e.id, e.title, e.timestamp ORDER BY no {$rows[1]} LIMIT {$addData['maxitems']}";
$sql_rows = serendipity_db_query($q);
?>
<dt><strong><?php echo constant('PLUGIN_KARMA_STATISTICS_' . strtoupper($key)); ?></strong></dt>
Please check, works for me, although I know nearly anything about PHP.
Re: Fixed
Posted: Fri Oct 14, 2005 2:21 pm
by garvinhicking
Hi!
Great stuff, thanks for getting involved in this! Sadly the postgreSQL grouping methods always put a pain in our developers backs, since MySQL and SQLite aren't so picky about multi-group statements.
Your patch is perfect, I just applied it and committed to our SVN.
Best regards,
Garvin
Posted: Fri Oct 14, 2005 3:35 pm
by CaptainCrunch
Great! I'm glad I could help with that one. Keep up the great work. I'll try and keep an eye open for Postgres-bugs as I fear it's not too heavily used.