SMF Support > PostgreSQL and SQLite Support

Show new replies to your posts (bug?)

(1/4) > >>

habakuk:
Using 2.0rc1 on postgresql, I get a SQL error when trying to use "Show new replies to your posts":

ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
File: /Library/Apache2/htdocs/goodeye/forum/Sources/Recent.php
Line: 1078


> Select Distinct wants to have the distinct field in the order by clause. So, I added "ORDER BY t.id_topic" instead of
"ORDER BY {raw:sort}" and the error goes away.

However, the query produces not the expected results anyway. I see it returns all new posts form topics where I've posted to, but not necessarily started by me.

Could someone confirm the first part to be sort of a bug, maybe only related to postgres?

And maybe someone could tell me if I misunderstood the  intent of the query.

Thank you!

cheers
®

BTW: yeah, I checked the sources/recent.php in the very latest release and it is the same code base.

Aleksi "Lex" Kilpinen:

--- Quote from: habakuk on September 18, 2009, 05:52:10 AM ---However, the query produces not the expected results anyway. I see it returns all new posts form topics where I've posted to, but not necessarily started by me.

--- End quote ---
That is the way it is supposed to work, to help you follow topics you have posted in.

habakuk:
Thank you, LexArma.

Well, I find it a bit ambiguous. 

"Show replies to subscribed threads" would better describe the actual functionality, no?

For, the replies often aren't to MY posts, but to the post of the thread opener. I think it would be quite handy to have the functionality to list new posts to threads I opened myself.

cheers
®

Aleksi "Lex" Kilpinen:

--- Quote from: habakuk on September 18, 2009, 06:43:42 AM ---"Show replies to subscribed threads" would better describe the actual functionality, no?

For, the replies often aren't to MY posts, but to the post of the thread opener. I think it would be quite handy to have the functionality to list new posts to threads I opened myself.

--- End quote ---
Well, SMF holds no real subscribing to topics (and does not do threads) so not really.
You can "subscribe" to a topic though, clicking the "Notify" button in a topic, so that SMF sends you an e-mail when new posts are made.

The Show unread posts since last visit button works like it says, and Show new replies to your posts works by showing all topics that you have posted in, and someone has posted after you, that you haven't read yet. That is a bit hard word in a small sentence, and "Show new replies to your posts" is a bit of a compromise I give you that much. ;)

N. N.:

--- Quote from: habakuk on September 18, 2009, 05:52:10 AM ---ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
File: /Library/Apache2/htdocs/goodeye/forum/Sources/Recent.php
Line: 1078


> Select Distinct wants to have the distinct field in the order by clause. So, I added "ORDER BY t.id_topic" instead of
"ORDER BY {raw:sort}" and the error goes away.

--- End quote ---
It looks like Postgres-specific restriction. Please check out:
 http://archives.postgresql.org/pgsql-sql/2007-02/msg00161.php
or perhaps this little note:

--- Quote ---Require SELECT DISTINCT target list to have all ORDER BY columns

--- End quote ---
(from Postgresql 8.4 release 7.0 changelog)

Navigation

[0] Message Index

[#] Next page