Sorting the user list

[Migrated From Jforum.net]

Is there a way to have the user list sorted by the number of posts instead of the reference number?

Thanks.
[originally posted on jforum.net by bcc]

[Migrated From Jforum.net]

Currently, no.

Rafael
[originally posted on jforum.net by Rafael Steil]

[Migrated From Jforum.net]

Also, there seems to be a weird problem with pagination of the user list with PostgreSQL.
I got 99 users right now, when I click the member listing link, i see all of them, and when I click the next link, I see 32-61. Next, 62-91. Next, 92-99.

So it seems to work fine on the following pages, but not on the first one.

With the post pagination, everything seems to be fine.
[originally posted on jforum.net by bcc]

[Migrated From Jforum.net]

BTW, thanks for the project. I like phpbb, but always hated having to install php in addition to an app server.
[originally posted on jforum.net by bcc]

[Migrated From Jforum.net]

I think I found it.

It uses UserModel.selectAll instead of UserModel.selectAllByLimit for the first page of the user list.

BTW, that's a beautiful way to do db abstraction you got there!
[originally posted on jforum.net by bcc]

[Migrated From Jforum.net]

This seems to do the trick.

--- net/jforum/dao/postgresql/PostgresqlUserDAO.java   2005-07-26 00:05:10.000000000 -0400
+++ net/jforum/dao/postgresql/PostgresqlUserDAO.java  2006-11-20 07:24:24.000000000 -0500
@@ -42,8 +42,13 @@
  */
 package net.jforum.dao.postgresql;

+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
 import java.util.List;

+import net.jforum.JForumExecutionContext;
+import net.jforum.util.preferences.SystemGlobals;
+
 /**
  * @author Rafael Steil
  * @version $Id: PostgresqlUserDAO.java,v 1.4 2005/07/26 03:05:09 rafaelsteil Exp $
@@ -51,15 +56,31 @@
 public class PostgresqlUserDAO extends net.jforum.dao.generic.GenericUserDAO
 {
        /**
+        * We have to have a redundant implementation here because PostgreSQL has a reverse order
+        * of parameters for limit and offset in the statement string, and the GenericUserDAO
+        * checks for count>0, which in our case would be startFrom (if we simply swapped the
+        * parameters before passing them to the super's method).
         * @see net.jforum.dao.UserDAO#selectAll(int, int)
         */
        public List selectAll(int startFrom, int count) throws Exception
        {
-               if (count > 0) {
-                       return super.selectAll(count, startFrom);
-               }
-
-               return super.selectAll(0, 0);
+                       PreparedStatement p;
+
+                       if (count > 0) {
+                               p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("UserModel.selectAllByLimit"));
+                               p.setInt(1, count);
+                               p.setInt(2, startFrom);
+                       }
+                       else {
+                               p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("UserModel.selectAll"));
+                       }
+
+                       ResultSet rs = p.executeQuery();
+                       List list = this.processSelectAll(rs);
+                       rs.close();
+                       p.close();
+
+                       return list;
        }

        /**
@@ -69,4 +90,6 @@
        {
                return super.selectAllByGroup(groupId, count, (start > 0 ? start + count : 0));
        }
+
+
 }

[originally posted on jforum.net by bcc]

[Migrated From Jforum.net]

Now that I think about it, it might be easier to just change the postgresql.sql.
But the order of the parameters in PostgresqlUserDAO.selectAll(int,int) still needs to be put in the right order when it calls the super's method.
[originally posted on jforum.net by bcc]

[Migrated From Jforum.net]

I just changed the order of offset and limit in the postgresql.sql and removed all PostgreSQL's DAO classes, except for PostgresqlDataAccessDriver and PostgresqlScheduledSearchIndexerDAO.

I left them empty.

Everything seems to work fine.


[originally posted on jforum.net by bcc]

[Migrated From Jforum.net]

Interesting approach.

So, just changing the order of the parameters and removing all dao classes does the trick? Nice.

You think you can post the DIFF for postgresql.sql here?

Thanks a lot.
Rafael
[originally posted on jforum.net by Rafael Steil]

[Migrated From Jforum.net]

--- WEB-INF/config/database/postgresql/postgresql.sql      2005-12-22 17:41:04.000000000 -0500
+++ WEB-INF/config/database/postgresql/postgresql.sql   2006-11-20 09:20:29.000000000 -0500
@@ -2,7 +2,7 @@
 # UserModel
 # ##########
 UserModel.selectAllByLimit = SELECT user_email, user_id, user_posts, user_regdate, username, deleted, user_karma, user_from, user_website, user_viewemail \
-       FROM jforum_users ORDER BY user_id LIMIT ? OFFSET ?
+       FROM jforum_users ORDER BY user_id OFFSET ? LIMIT ?

 UserModel.lastGeneratedUserId = SELECT CURRVAL('jforum_users_seq')

@@ -18,7 +18,7 @@
        WHERE u.user_id = ug.user_id \
        AND ug.group_id = ? \
        ORDER BY user_id \
-       LIMIT ? OFFSET ?
+       OFFSET ? LIMIT ?

 # #############
 # PostModel
@@ -33,7 +33,7 @@
        AND p.user_id = u.user_id \
        AND p.need_moderate = 0 \
        ORDER BY post_time ASC \
-       LIMIT ? OFFSET ?
+       OFFSET ? LIMIT ?

 PostModel.selectByUserByLimit = SELECT p.post_id, topic_id, forum_id, p.user_id, post_time, poster_ip, enable_bbcode, p.attach, \
        enable_html, enable_smilies, enable_sig, post_edit_time, post_edit_count, status, pt.post_subject, pt.post_text, username, p.need_moderate \
@@ -43,7 +43,7 @@
        AND p.user_id = ? \
        AND p.need_moderate = 0 \
        ORDER BY post_time DESC \
-       LIMIT ? OFFSET ?
+       OFFSET ? LIMIT ?

 # ##########
 # PollModel
@@ -64,7 +64,7 @@
        AND p.post_id = t.topic_last_post_id \
        AND p.need_moderate = 0 \
        ORDER BY t.topic_type DESC, t.topic_last_post_id DESC \
-       LIMIT ? OFFSET ?
+       OFFSET ? LIMIT ?

 TopicModel.selectByUserByLimit = SELECT t.*, p.user_id AS last_user_id, p.post_time, 0 AS attach \
        FROM jforum_topics t, jforum_posts p \
@@ -72,7 +72,7 @@
        AND t.user_id = ? \
        AND p.need_moderate = 0 \
        ORDER BY t.topic_last_post_id DESC \
-       LIMIT ? OFFSET ?
+       OFFSET ? LIMIT ?

 TopicModel.lastGeneratedTopicId = SELECT CURRVAL('jforum_topics_seq')

@@ -95,7 +95,7 @@
        FROM jforum_posts p, jforum_posts_text pt \
        WHERE p.post_id = pt.post_id \
        AND p.post_id BETWEEN ? AND ? \
-       LIMIT ? OFFSET ?
+       OFFSET ? LIMIT ?

 # #############
 # SmiliesModel

[originally posted on jforum.net by bcc]

[Migrated From Jforum.net]

But you also need to delete the PostgreSQL DAO's classes. Someone reversed the parameters in them, so it won't work if you simply chage postgresql.sql.

PostgresqlDataAccessDriver and PostgresqlScheduledSearchIndexerDAO are references by other classes, so I kept them but removed all methods. It would make sense to remove them completely and update the classes that reference them.

Also, with the new layout (this board), the preview doesn't work for some reason. While I was posting the previous message (the diff), clicking the preview button took me to the home page.


[originally posted on jforum.net by bcc]

[Migrated From Jforum.net]

Ok, thank.. I updated the cvs with your fixes.

Rafael
[originally posted on jforum.net by Rafael Steil]