s9y 0.9-beta2, statistics and Postgres

[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!

[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

[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.

[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

[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.