Displaying comments and postgresql

[rickmans]

It seems like in the postgresql version of 0.7.1 a join is missing, because comments are printed twice although they appear only once in the comments table (see for example: http://internetschoon.nl/archives/12-Se ... l#comments, you also the comments counter states that there are only two comments).

I couldn't find in which file the select query was, could you please mention in which it is, so I could correct it provisionally by myselves instead of waiting for the next update .

[garvinhicking]

Hm, I think this bug has been fixed in 0.8 already, but I don't remember what it was about.

The function to fetch the entries is in serendipity_functions.inc.php, serendipity_fetchComments()...

Regards,
Garvin

[rickmans]

It seems like its the join on the category table. For every category assigned to the entry an comment will be displayed. So if you have X categories assigned, you will get X time printed your comments of this entry.

I tweaked the query in the way it works okay with mine site. The query I used is following:

Code: Select all

    $query = "SELECT $distinct
                    co.id,
                    co.entry_id, co.timestamp, co.title, co.email, co.url, co.ip, co.body, co.type, co.subscribed,
                    co.author AS username,
                    co.id AS commentid,
                    co.parent_id AS parent_id
              FROM
                    {$serendipity['dbPrefix']}comments co
              WHERE co.type LIKE 'NORMAL' AND entry_id > 0 $and
              $group
              ORDER BY
                    co.id " . ($order != '' ? $order : '') . "
                    $limit";

The only question i have left is: why joining on the extra tables entry, entrycat and categories? Is it because of other settings for serendipity that I do not use and if so, is it an idea to keep the join optional, just to be more effecient since this joins cost appr 5-10 times extra query executing time.

[garvinhicking]

rickmans,

thanks for looking into this. You are of course right with the cause of that pgsql problem.

The category joins was used previously only because it was used in the RSS comment feed, but there the 'category' is not really evaluated so it's not necessary anymore.

That's why I followed your suggestion to drop the join on entrycat and categories.

The join on the entries table is still needed though because title and other information is drawn from there.

Thanks and regards,
Garvin