|
Diary Subscriptions
|
|
By theantix , Section Wishlist []
Posted on Mon Jan 21, 2002 at 12:00:00 PM PST
|
 |
|
Several Kuro5hin users have requested that scoop could have a new box that alerts users to when a diary is posted from a preselected list of users. The box would look very similar to the hotlist box that is currently implemented, but instead would list diaries. This request is not new, but I have come up with some ways to implement it.
Unfortunately I have had trouble implementing this myself, as I have little experience with Perl and MySQL. I am hoping that a more expert programmer in these areas can build on my ideas.
|
| The output of the box would look like this:
theantix's Diary Suscriptions:
rusty (0 new)
hurstdog (2 new)
panner (1 new)
I have two ideas for the database implementation. The first idea would be adding a CSV list of subscribed users in the userprefs table. The other way would be by adding a simple new table, with a definition of:
CREATE TABLE dsubscribed_users (
uid int(11) NOT NULL default '0',
dsubscribed _uid int(11) NOT NULL default '0'
)
Since I know SQL, here is my idea for the SQL query that should return the count of unseen diaries.
For each subscribed user:
SELECT COUNT(*) FROM Stories S,
HAVING S.section='diary' AND S.aid=$ds_uid
AND S.time > (SELECT MAX(S.time) FROM Stories S, Viewed_stories V WHERE S.section='diary' and S.aid=$ds_uid AND V.sid=S.sid AND V.uid=$my_uid)
(where $ds_uid is the uid of the subscribed user, and $my_uid is the session uid)
This example assumes that the CSV list is used instead of the table. If the table is used, you could reduce the number of queries performed on the db by using a MySQL join on the dsubscribed_users table.
Obviously some variation will be necessary for the implemenation, and I apologize again for not coding it myself. But this is a feature that would be greatly appreciated by the regular K5 diary folk. If someone could assist with the coding and get it submitted to kuro5hin, I'll kiss you (not on the lips). |
|
|
Story Views
|
|
42 Scoop users have viewed this story.
|
|
