| Author |
 Topic  |
|
RichardKinser
Snitz Forums Admin
USA
16655 Posts |
 Posted - 20 April 2002 : 18:46:54
|
No, 3.4 won't be out today. But, I wasn't trying to imply that it won't be out for a long time. Once you look at search.asp, I pretty sure you'll figure out an easy way to implement this. 
Here is the what the link from the profile page looks like:
/search.asp?mode=DoIt&MEMBER_ID=1 |
 |
|
|
Aaron S.
Average Member
USA
985 Posts |
Posted - 20 April 2002 : 19:27:40
|
The feature Richard talkes about seems to have the same sorting issue that I originally posted about (by LAST_POST).
So... I am moving forward (and am almost finished) building new code that will:
1. remove the FORUM table from the SQL stmt since it is not needed (less overhead on the join) <-- this should be done in the original code also
2. sort by R_DATE or T_DATE depending on which is latest (and you are the author of)
The end result will be a list of posts made by a given member in the POSTING order of that member.
--Aaron
MySubs Email MOD
MOD to Smile Mgr
HotKeys MOD |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 20 April 2002 : 19:44:15
|
Well changing search.asp to accept that QueryString was pretty easy. I have it working already.
There are other issues to this strategy, however, at least related to the way search.asp shows results.
First the organization by Category is really awkward, since effectively it breaks the topic date sorting criteria, by breaking the topics according to the forum category they belong to.
Second Category can be useful sometimes, just to give context to the topic title...
Third, Aaron's post does touch an important aspect, regarding the date used for sorting.
Uumm..., gotta think on the best way to conciliate all this...
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
Aaron S.
Average Member
USA
985 Posts |
Posted - 20 April 2002 : 20:27:10
|
Ok... I'm going to try to post again (for some reason the page seemed to refresh which erased everything I typed).
First, I created 4 new topics called "test topic ordering # X" (X = 1 through 4).
I then had another user post a reply to "test topic ordering # 2".
Using the CURRENT SQL stmt, this is how it would show up in RECENT TOPICS:
I created a new SQL stmt and then sorted by R_DATE (if author = given member_id) or T_DATE. The result is a list by ACTUAL post date:
The next post will have the code to produce this.
--Aaron
MySubs Email MOD
MOD to Smile Mgr
HotKeys MOD |
|
|
|
Aaron S.
Average Member
USA
985 Posts |
Posted - 20 April 2002 : 23:23:36
|
Whew... the sort was tougher than I thought, but it is now done:
Here is a link to a clean version of pop_profile.asp with the new code added. Look for Recent Posts MOD starting on line 212.
Recent Posts MOD
This uses a VB bubble sort mostly because I couldn't get the jscript (runat=server) one to work.
Please try this on a heavy volume query to see if there is noticeable slowdown.
I am assuming that people aren't posting thousands of posts per month so for the pop_profile page it should be fine.
For a search.asp page... it might get some slowdown. I don't have thousands of records to test it against.
Please give me comments and suggestions on how to improve the page.
--Aaron
P.S. I guess this forum now needs to be moved to MOD W Code. 
MySubs Email MOD
MOD to Smile Mgr
HotKeys MOD
Edited by - aaron s. on 21 April 2002 09:30:55 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 20 April 2002 : 23:32:40
|
Aaron, I think that this could be made exclusively in SQL, using a UNION query (this would exclude MySQL, but maybe the MySQL users could use your code). Don't know how would it compare to your VB solution, but the SQL probably would be faster (at least it should...)
Gonna take a look at it tomorrow. I'm too tired now to even take a proper look at your code.
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
Aaron S.
Average Member
USA
985 Posts |
Posted - 21 April 2002 : 09:34:00
|
I played around with UNION but couldn't get it to work exactly right.
If you can get it to sort in UNION, you will still need an additional query to get the T_SUBJECT since it cannot be in the UNION stmt (since it is not in the REPLY table).
I am going to try to get the jscript sort to work which is the fastest option of all. Here is an article that shows some benchmark times using the jscript sort:
http://www.4guysfromrolla.com/ASPScripts/PrintFAQ.asp?FAQID=83
Let me know if can get UNION to work.
--Aaron
MySubs Email MOD
MOD to Smile Mgr
HotKeys MOD |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 21 April 2002 : 10:09:02
|
I'm working on the UNION thing. I don't think there will be major difficulties. I got the first query ready (to get all topics authored by the user and with no replies). Now working on the second query to get all the topics with replies from the REPLY table.
I don't see a problem with the T_SUBJECT field. All that is needed is to include the FORUM_TOPICS table in the join, so that you can get that field...
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
Aaron S.
Average Member
USA
985 Posts |
|
|
Aaron S.
Average Member
USA
985 Posts |
Posted - 21 April 2002 : 10:40:01
|
Ok... I'm a quick learner.
Here is a SQL Stmt that seems to return the same information that the bubble sort does (this leaves off a few fields that we will need as I stripped it down to test):
select r_date , t_subject FROM FORUM_TOPICS LEFT JOIN FORUM_REPLY ON FORUM_TOPICS.TOPIC_ID = FORUM_REPLY.TOPIC_ID
WHERE r_DATE>'20020321201244' AND r_AUTHOR=1
UNION select t_date,t_subject from forum_topics
WHERE t_DATE>'20020321201244' AND t_AUTHOR=1
ORDER BY r_date DESC;
In this case... you would replace the hard coded date with strStartDate and the hard coded AUTHOR with ChkString(Request.QueryString("id").
As I mentioned above, there are some fields that would need to be added back, but that is the easy part.
I'll add this to the page I have built (and pull out the bubble sort). Again, since I don't have a lot of data to test, I can't really run any tests to see which is faster in high traffic.
--Aaron
MySubs Email MOD
MOD to Smile Mgr
HotKeys MOD |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 21 April 2002 : 10:53:40
|
Don't you get repeated topics there, in the case where the same member has replied more than once to the same topic?
That's the problem I'm currently facing. I guess a subquery will be needed to get only the reply with the latest post date for a given topic...
Also, on your query some of the records returned won't have a r_date field with a value in it. That will disrupt ordering, I think.
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs
Edited by - ruirib on 21 April 2002 10:57:08 |
|
|
|
Aaron S.
Average Member
USA
985 Posts |
Posted - 21 April 2002 : 11:06:18
|
In both cases (bubble and union), it returns duplicate topics.
To resolve this, I simply have a pipe delimited string that checks for duplicate topics. The UNION could leave out duplicated but then you can't sort correctly on DATE (R or T)
Here is a link to the new text file for SQL Server and MS Access:
Recent Posts MOD (SQL Server and MS Access)
--Aaron
MySubs Email MOD
MOD to Smile Mgr
HotKeys MOD |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 21 April 2002 : 11:10:35
|
I see. What about the r_date issue, or rephrasing, what about topics that don't have replies? Doesn't look like the SQL is ordering them properly...
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
Aaron S.
Average Member
USA
985 Posts |
Posted - 21 April 2002 : 11:13:34
|
The first Stmt pulls out Replies with Author = chosen member (this will grab topics member has replied to).
The second Stmt pull out Topics with Author = chosen member (this will grab topics member created regardless of whether or not there is reply).
They are then UNIONed together and sorted by DATE.
I think it is working properly (as I get the same results as I did when I used a bubble sort).
--Aaron
MySubs Email MOD
MOD to Smile Mgr
HotKeys MOD
Edited by - aaron s. on 21 April 2002 11:14:43 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 21 April 2002 : 11:19:58
|
The second query does not return a r_date field. The records for it won't be ordered by the r_date ordering clause, at least as far as my limited SQL knowledge can tell...
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
Topic |
|
Topic Locked
