Rayd Forums

Rayd Forums » New Forums Discussion

61. 

    Ardentfrost
    Cooler than Tim

    Did you look to see how I sort by joined date and username? I build a link and use $_GET to pull out what I want to sort by when the query occurs. If I can make it so that it counts the posts of each user, and sorts it by that (check out how I count posts in my post count plugin) automatically, then I'm golden. The rest is just link building. It seems the first part of your post will be able to do what I need (although I haven't tested it yet) to get me to where all I have left is to link build. I didn't know you could make variables in the query, that's neat

    A guy from the bbpress forums suggested I also make it so that the displayed number of users per page is able to be changes (like via a dropdown menu). That isn't hard, I think I'll do them both in a future release. I'm hoping they release .74 soon because I'll have to make them usable in that version anyway, might as well do the work then.

    Posted 3 years ago #
62. 

    codered
    Hax0r at L4rge

    "I didn't know you could make variables in the query, that's neat" --ArdentFrost

    It's more renaming of column headers than the use of variables. Let's break down the SQL statement from my post.

    SELECT user_login , COUNT( * ) AS posts

    The SELECT portion tells mySQL that all I want in my result is a table with columns of usernames and the result of the COUNT() function.

    FROM bbpress_posts , bbpress_users

    FROM tells mySQL from which tables I want to pull this information. When using more than one table, you are inheriantly performing either a JOIN or UNION.

    WHERE poster_id = ID

    This WHERE statement completes a translation between the two tables. It translates the poster_ID column in the bbpress_posts table into the corresponding user_logins. It does this by using the primary_key of ID in the bbpress_users table and mapping that to the foreign key of poster_ID in the bbpress_posts table.

    GROUP BY user_login

    GROUP BY works in relation with the COUNT() function. It tells mySQL to group each of the distinct user_logins and count each one. The result is a table of users and the number of posts that each have made.

    ORDER BY posts DESC

    Fairly straightforward here - sort the table by the number of posts in descending order. If you wanted to do ascending, then change DESC to ASC.

    Georgia Tech offers a great course on database application design. The course is targeted to CmpEs, CS majors, and ISyEs as well. The course number is cs4400 - Intro to Database Design. It's a great course. I took it as an elective over the last summer in school.

    Posted 3 years ago #
63. 

    Ardentfrost
    Cooler than Tim

    I actually have to take 4400 this coming summer. I'm looking forward to it

    I'll add the functions to sort by post count into rayd here in a little bit.

    Posted 3 years ago #
64. 

    Ardentfrost
    Cooler than Tim

    Riggan, your query sorts users by post count, but it drops any user with zero posts. Know how to make it not do that? I mean, I can see why... if the user's id doesn't exist in the posts table, it has nothing to join them with. But I want all users represented, even if they have no posts (especially since my page creation function depends on the number of users, not the number of users with posts).

    You can check out what I'm talking about here: http://www.rayd.org/forums/mlist?orderby=postcount

    I'm not going to add the link until it works obviously

    Edit: Also added a part where it wouldn't get deleted users

    Posted 3 years ago #
65. 

    Ardentfrost
    Cooler than Tim

    Looking at sql.org (which is where I have learned SQL over the past 4 weeks) I need to use an outter join. I gotta figure out the syntax for mysql (damn tutorial I'm reading defaults to oracle)

    Posted 3 years ago #
66. 

    Ardentfrost
    Cooler than Tim

    Alright, I think I got it. Had to change it around a little. Ended up making it a right join. Now I gotta get it tested on other databases.

    Posted 3 years ago #
67. 

    Ardentfrost
    Cooler than Tim

    Now I gotta figure out how to get it to count right. Currently it's counting deleted and spam posts, so it's kinda messing up the order a little

    Posted 3 years ago #
68. 

    Ardentfrost
    Cooler than Tim

    Ohhh.. looks like the query is counting NULLs (which it lists if there are no posts) as a count of 1... so anyone with only 1 post gets the same number of posts as someone who has none.

    That sucks. Any ideas how to combat that? I need it to not count when post_text is NULL, but still display that person.

    Posted 3 years ago #
69. 

    Ardentfrost
    Cooler than Tim

    I also need it to not count if post_status = 0

    Posted 3 years ago #
70. 

    Ardentfrost
    Cooler than Tim

    Got it. Instead of trying to adjust the count in some way, I added a second ORDER BY field which went by post_text (so that even if they only have 1 post, it orders them above those with no posts)

    Posted 3 years ago #
71. 

    codered
    Hax0r at L4rge

    Damn dude, you made like 5 posts while I was working on this. Here's my solution:

    SELECT * , IF(post_id IS NULL, 0, COUNT(*)) AS posts
    FROM (bbpress_users LEFT JOIN bbpress_posts ON ID = poster_id)
    WHERE user_status = 0
    GROUP BY user_login
    ORDER BY posts DESC

    The IF statement puts a zero in the posts column if no post_id exist in the resultant joined table.

    Posted 3 years ago #
72. 

    Ardentfrost
    Cooler than Tim

    My way works, I don't think I want to change it. Is there a way, however, that I can get a more accurate post count? Right now it counts even deleted posts, so I need it to count only when post_status = 0.

    Any ideas?

    Without that, the displayed post count could be out of order.

    PS. Here's my resulting query: $result = $bbdb->get_results("SELECT * , COUNT(*) AS posts FROM $bbdb->posts RIGHT JOIN $bbdb->users ON poster_id = ID WHERE user_status = 0 GROUP BY user_login ORDER BY posts DESC, post_text DESC LIMIT $page, 10 " ) ;

    Posted 3 years ago #
73. 

    codered
    Hax0r at L4rge

    on your WHERE part of the SQL statement, try using:

    WHERE (user_status = 0 AND post_status = 0)

    Posted 3 years ago #
74. 

    Ardentfrost
    Cooler than Tim

    If I do that, it gets rid of the users without posts

    Posted 3 years ago #
75. 

    codered
    Hax0r at L4rge

    WHERE (user_status = 0 AND post_status !=1) ?

    Posted 3 years ago #
76. 

    Ardentfrost
    Cooler than Tim

    Nope, that gets rid of them too =\

    Posted 3 years ago #
77. 

    codered
    Hax0r at L4rge

    Looks like you fixed it.

    Posted 3 years ago #
78. 

    Ardentfrost
    Cooler than Tim

    It works for rayd at the moment, but if I have 65 posts, 6 deleted and you have 60 posts, none deleted, I'll be listed ahead of you, but only have 59 displayed.

    If that makes sense

    Posted 3 years ago #
79. 

    codered
    Hax0r at L4rge

    What does your current SQL query look like?

    Posted 3 years ago #
80. 

    Ardentfrost
    Cooler than Tim

    I pasted it 4 posts ago

    Posted 3 years ago #
81. 

    Ardentfrost
    Cooler than Tim

    Added the ability to change the number of users per page in the member list.

    I'm not TOO worried about it calculating post count slightly wrong, since it'll work for the most part Riggan, lemme know if you think of something. I'll probably update the release soon.

    Posted 3 years ago #
82. 

    Ardentfrost
    Cooler than Tim

    Keeping this thread to track what plugins people want on the forums, but am starting a new forum specifically for individual plugin discussion. Hopefully that will cut down on this thread's bulk

    Posted 3 years ago #
83. 

    Ardentfrost
    Cooler than Tim

    Changed my mind, this has now been duplicated in the plugin forum as a request thread. Therefore, this thread is now closed

    Posted 3 years ago #

RSS feed for this topic

Topic Closed

This topic has been closed to new replies.