"who's online" block broken

Try out this bad boy.

I tested it with MySQL and, as you'd expect, it still works fine.

Same here, easy and straight forward patch which is ready to get in.. :-)

Committed to CVS HEAD.

Committed to 5.x.

This fixes the syntax breakage in pgsql, but the double sessions reappear http://drupal.org/node/107051

This works in mysql:
SELECT DISTINCT u.uid, u.name FROM {users} u INNER JOIN {sessions} s ON u.uid = s.uid WHERE s.timestamp >= %d AND s.uid > 0 ORDER BY s.timestamp DESC;

This does not work:
SELECT DISTINCT u.uid, u.name, s.timestamp FROM {users} u INNER JOIN {sessions} s ON u.uid = s.uid WHERE s.timestamp >= %d AND s.uid > 0 ORDER BY s.timestamp DESC;
Because a distinct result is u.uid, u.name, s.timestamp which results almost certainly in multiple rows for a user if you're using multiple browsers with the same login at the same time - and then you may see double usernames in the list...

(this also appplies to 6.x/head)

Even though this query uses a temporary table and might be a little slower, this query works in mysql 2.23.58 and mysql 5.0.24 and produces the results I expect...

SELECT s.uid, u.name, s.timestamp
FROM {users} u
INNER JOIN {sessions} s ON u.uid = s.uid
WHERE s.timestamp >= %d
AND s.uid >0
GROUP BY s.uid
ORDER BY s.timestamp DESC

Hopefully this works for pgqsl too, could someone please check?

i don't know if having users logged in multiple time listed multiple times is really a bug...

there is a whole query doing stuff... but doing the wrong stuff - that is a bug IMO, either remove it or fix it :)

Yes, it's a bug that the same user shows up multiple times. It's "who's online" not "who has active sessions are in the sessions table?" ;)

so .. still noone with a postgresql configuration who can test this query?

it would be nice to have this fixed in 6.x

this is still an issue in the 6.x dev as far as I can see...

Subscribing

Mysql lets you GROUP BY without requiring the field to be in the SELECT clause but Postgres follows the SQL 92 standard as requires fields in GROUP BY to be in the SELECT clause. I've tested the most recent approach in this thread with Postgres but do not get the desired result so I tried a few other ideas and came up with this as a possible solution.

SELECT s.uid, u.name, MAX(s.timestamp) AS timestamp
FROM {users} u
INNER JOIN {sessions} s
ON u.uid = s.uid
WHERE s.uid > 0
GROUP BY s.uid, u.name
HAVING MAX(s.timestamp) >= %d
ORDER BY timestamp;

Or as a single easy to paste line:

SELECT s.uid, u.name, MAX(s.timestamp) AS timestamp FROM {users} u INNER JOIN {sessions} s ON u.uid = s.uid WHERE s.uid > 0 GROUP BY s.uid, u.name HAVING MAX(s.timestamp) >= %d ORDER BY timestamp;

This works in both Mysql (4.1) and Postgres (8.1). I do not have a Mysql 5 db around. Also this was only tested on the latest Drupal 6.x beta2 release. I logged in to both Mysql and Postgres based installations from 2 browsers to generate duplicate user logins with different sid and timestamps and the query returns identical results.

Probably should be tested with a site with more than a single user...

#107051: Same user listed multiple times in who's online block

Fixed patches can be downloaded at
http://e-utm.890m.com/content/drupal-same-user-listed-multiple-times-who...

You just extract it into module/user/user.module