decir/notes.sql
author Dan
Tue, 13 Nov 2007 22:28:30 -0500
changeset 7 37387f84fe25
parent 0 0417a5a0c7be
permissions -rw-r--r--
Add edit functionality to forum management and implemented a sick drag-and-drop reordering system for forums
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
0
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
     1
SELECT f.forum_id,f.forum_type,f.forum_name,f.forum_desc,
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
     2
       COUNT(t.topic_id) AS num_topics, COUNT(p.post_id) AS num_posts,
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
     3
       p.post_id,t.topic_id,t.topic_title,u.username,u.user_level,p.timestamp FROM decir_forums AS f
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
     4
  LEFT JOIN decir_topics AS t
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
     5
    ON (t.forum_id=f.forum_id)
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
     6
  LEFT JOIN decir_posts AS p
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
     7
    ON (p.topic_id=t.topic_id)
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
     8
  LEFT JOIN users AS u
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
     9
    ON (u.user_id=f.last_post_user)
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    10
  WHERE ( t.topic_id=f.last_post_topic AND p.post_id=f.last_post_id ) OR ( f.last_post_topic IS NULL AND f.last_post_id IS NULL )
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    11
    GROUP BY f.parent,f.forum_id
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    12
    ORDER BY f.forum_order;
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    13
    
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    14
SELECT COUNT(t.topic_id) AS num_topics, COUNT(p.post_id) AS num_posts FROM decir_forums AS f
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    15
  LEFT JOIN decir_topics AS t
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    16
    ON (t.forum_id=f.forum_id)
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    17
  LEFT JOIN decir_posts AS p
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    18
    ON (p.topic_id=t.topic_id)
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    19
  GROUP BY f.forum_id
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    20
  ORDER BY f.forum_order;
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    21
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    22
INSERT INTO decir_forums(forum_id,forum_type,forum_name,forum_order) VALUES(1,2,'Test category',1);
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    23
INSERT INTO decir_forums(forum_id,forum_type,forum_name,forum_desc,parent,forum_order,last_post_id,last_post_topic,last_post_user) VALUES(3,1,'Test forum 1','This is just a test forum.',1,2,3,3,2);
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    24
INSERT INTO decir_topics(topic_id,forum_id,topic_title,topic_icon,topic_starter,timestamp) VALUES(1,3,'Test topic 1',1,2,UNIX_TIMESTAMP());
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    25
INSERT INTO decir_posts(post_id,topic_id,poster_id,poster_name,timestamp) VALUES(1,1,2,'Dan',UNIX_TIMESTAMP());
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    26
INSERT INTO decir_posts_text(post_id,post_text,bbcode_uid) VALUES(1,'This post was created manually using SQL queries.
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    27
It is nothing more than a [b:0123456789]proof of concept[/b:0123456789]!
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    28
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    29
-Dan','0123456789');
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    30
INSERT INTO decir_forums(forum_id,forum_type,forum_name,forum_desc,parent,forum_order,last_post_id,last_post_topic,last_post_user) VALUES(4,1,'Test forum 2','This is just a test forum.',1,3,2,2,2);
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    31
INSERT INTO decir_topics(topic_id,forum_id,topic_title,topic_icon,topic_starter,timestamp) VALUES(2,4,'Test topic 2',1,2,UNIX_TIMESTAMP());
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    32
INSERT INTO decir_posts(post_id,topic_id,poster_id,poster_name,timestamp) VALUES(2,2,2,'Dan',UNIX_TIMESTAMP());
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    33
INSERT INTO decir_posts_text(post_id,post_text,bbcode_uid) VALUES(2,'This post was created manually using SQL queries.
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    34
It is nothing more than a [b:0123456789]proof of concept[/b:0123456789]!
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    35
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    36
-Dan','0123456789');
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    37
INSERT INTO decir_topics(topic_id,forum_id,topic_title,topic_icon,topic_starter,timestamp) VALUES(3,3,'Test topic 3',1,2,UNIX_TIMESTAMP());
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    38
INSERT INTO decir_posts(post_id,topic_id,poster_id,poster_name,timestamp) VALUES(3,3,2,'Dan',UNIX_TIMESTAMP());
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    39
INSERT INTO decir_posts_text(post_id,post_text,bbcode_uid) VALUES(3,'This post was created manually using SQL queries.
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    40
It is nothing more than a [b:0123456789]proof of concept[/b:0123456789]!
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    41
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    42
-Dan','0123456789');
0417a5a0c7be Initial repository population
dan@fuhry
parents:
diff changeset
    43