SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,139
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Forum building sql select statement assistance

    tables:

    Code:
    create table users (
    	id int(10) UNSIGNED NOT NULL auto_increment
    	,name varchar(20) NOT NULL
    	,first_name varchar(20) NOT NULL
    	,last_name varchar(20) NOT NULL
    	,email varchar(100) NOT NULL
    	,pwd blob NOT NULL
    	,access tinyint UNSIGNED NOT NULL
    	,site varchar(100) NULL
    	,status tinyint UNSIGNED NOT NULL
    	,newsletter boolean NOT NULL
    	,created timestamp null default null
    	,primary key(id)
    	,unique key(email)
    	,unique key(name)
    )ENGINE=MYSIAM;
    
    create table threads (
    	id int(10) UNSIGNED NOT NULL auto_increment
    	,user_id int(10) UNSIGNED NOT NULL
    	,topic_id tinyint UNSIGNED NOT NULL
    	,title varchar(40) NOT NULL
    	,message text NOT NULL
    	,status tinyint UNSIGNED NOT NULL
    	,created timestamp NULL default NULL
    	,primary key(id)
    )ENGINE=MYSIAM;
    
    create table posts (
    	id int(10) UNSIGNED NOT NULL auto_increment
    	,thread_id int(10) UNSIGNED NOT NULL
    	,user_id int(10) UNSIGNED NOT NULL
    	,message text NOT NULL
    	,status tinyint UNSIGNED NOT NULL
    	,created timestamp NULL default NULL
    	,primary key(id)
    )ENGINE=MYSIAM;
    
    create table sections (
    	id tinyint UNSIGNED NOT NULL auto_increment
    	,name varchar(20) NOT NULL
    	,weight tinyint UNSIGNED NOT NULL
    	,primary key(id)
    )ENGINE=MYSIAM;
    
    create table topics (
    	id tinyint UNSIGNED NOT NULL auto_increment
    	,section_id tinyint UNSIGNED NOT NULL
    	,name varchar(20) NOT NULL
    	,weight tinyint UNSIGNED NOT NULL
    	,primary key(id)
    )ENGINE=MYSIAM;
    Given these tables I would like to select the topics in each section. The number of threads and post for each topic. The most recent thread and that threads poster(user) for each topic. I also like the most recent post that posts thread and the user for each topic. The end display would be similar to how sitepoint has the forum area setup listing the sections and topics within those sections.

    - section c
    - topic a | post count | thread count | recently posted thread | recently posted user
    - topic b | post count | thread count | recently posted thread | recently posted user

    - section b
    - topic g | post count | thread count | recently posted thread | recently posted user
    - topic h | post count | thread count | recently posted thread | recently posted user
    - topic w | post count | thread count | recently posted thread | recently posted user

    I have been building this query, but it doesn't seem to result in the expected output. For example, it only ever selects one recent post and thread, but it doesn't do it per topic.

    Code:
    SELECT
         t0.id as t0_id
         ,t0.name as t0_name
         ,t1.name AS t1_name
         ,t1.id AS t1_topic_id
         ,COUNT(t2.id) AS t1_thread_count 
         ,t3.t0_title AS t3_recent_thread
         ,t3.t0_created AS t3_recent_thread_created
         ,t3.t2_name AS t3_recent_thread_poster
         ,t4.t2_name AS t4_recent_post_poster
         ,t4.t1_title AS t4_recent_post_thread
         ,t4.t0_created AS t4_recent_post_created
         ,t5.t1_post_count AS t5_topic_post_count
      FROM
         sections AS t0
      LEFT
      JOIN
         topics AS t1
        ON
          t0.id = t1.section_id
      LEFT
      JOIN
          threads AS t2
        ON
          t1.id = t2.topic_id
      LEFT
      JOIN
          (SELECT 
                t0.title AS t0_title
                ,t1.id AS t1_id
                ,t0.id AS t0_id
                ,t0.topic_id AS t0_topic_id
                ,t1.thread_id AS t1_thread_id
                ,t0.created AS t0_created
                ,t2.name AS t2_name
             FROM
                threads AS t0
             LEFT
             JOIN
                posts AS t1
               ON
                 t0.id = t1.thread_id
             LEFT
             JOIN
                 users AS t2
               ON
                t0.user_id = t2.id
            WHERE
                t0.status <> 0
                AND
                (t1.status IS NULL OR t1.status <> 0)
            ORDER
               BY
                t0.created DESC,t1.created DESC
            LIMIT
                1
           ) AS t3
         ON
           t1.id = t3.t0_topic_id
      LEFT
      JOIN
          (SELECT
         	    t1.topic_id AS t1_topic_id
         	    ,t2.name AS t2_name
         	    ,t1.title AS t1_title
         	    ,t0.created AS t0_created
            FROM
                posts AS t0
           INNER
            JOIN
               threads AS t1
              ON
                t0.thread_id = t1.id
           INNER
            JOIN
               users AS t2
              ON
               t0.user_id = t2.id
           WHERE
               t0.status <> 0
               AND
               t1.status <> 0
           ORDER
              BY
               t0.created DESC,t1.created DESC
           LIMIT
               1
         ) AS t4
        ON
          t1.id = t4.t1_topic_id
      LEFT
      JOIN
    	(SELECT
    	      COALESCE(COUNT(t1.id),0) AS t1_post_count
    	      ,t0.topic_id AS t0_topic_id
    	   FROM
    	       threads AS t0
    	   LEFT
    	   JOIN
    	       posts AS t1
    	     ON
    	       t0.id = t1.thread_id
    	  WHERE
    	       (t0.status <> 0 AND t1.status <> 0)
    	  GROUP
    	     BY
    	       t0.topic_id
    	) AS t5
    	ON
    	  t1.id = t5.t0_topic_id
     WHERE
         (t2.status IS NULL OR t2.status <> 0)
     GROUP
        BY
         t1.id
     ORDER
        BY
         t0.weight,t1.weight
    Any help appreciated, thanks.

  2. #2
    Twitter - @CarlBeckel busy's Avatar
    Join Date
    May 2004
    Location
    Richmond, VA, USA
    Posts
    819
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My initial reaction is that you're trying to do too much with one query. There's nothing wrong with doing 2 or 3 queries to keep your head from exploding. You might even have a performance hit from trying to run a query that complicated.

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,139
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    This query seems to be giving the sections, the topics within those sections and the number of posts within a topic and the number of threads per a topic. However, I'm a little concerned whether or not there is a more efficient way. Beyond that I still need to figure out how to get the most recent thread per topic and recent post and thread the post belongs to per topic along with the user name.

    Code:
    SELECT
         t0.name AS t0_name
         ,t1.t0_name AS t1_name
         ,COALESCE(t1.t1_post_count,0) AS t1_post_count
         ,COALESCE(t1.t1_thread_count,0) AS t1_thread_count
      FROM
         sections AS t0
      LEFT
      JOIN
         (SELECT
               t0.name AS t0_name
               ,t0.id AS t0_id
               ,t0.section_id AS t0_section_id
               ,t0.weight AS t0_weight
               ,SUM(t1.t1_post_count) AS t1_post_count
               ,SUM(t1.t1_thread_count) AS t1_thread_count
            FROM
               topics AS t0
            LEFT
            JOIN
               (SELECT
                     t0.id AS t0_id
                     ,t0.topic_id AS t0_topic_id
                     ,SUM(t1.t0_post_count) AS t1_post_count
                     ,COALESCE(COUNT(t0.id),0) AS t1_thread_count
                  FROM
                     threads AS t0
                  LEFT
                  JOIN
                     (SELECT
                          t0.thread_id AS t0_thread_id
                          ,COALESCE(COUNT(t0.id),0) AS t0_post_count
                        FROM
                           posts AS t0
                       GROUP
                          BY
                           t0.thread_id) AS t1
                   ON
                    t0.id = t1.t0_thread_id
                GROUP
                   BY
                    t0.id) AS t1
              ON
               t0.id = t1.t0_topic_id
           GROUP
              BY
               t0.id) AS t1
       ON
         t0.id = t1.t0_section_id
    ORDER
       BY
        t0.weight,t1.t0_weight

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,139
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    When you group information together is there any way to control the the order of records within that group? From what I've is it safe to say the particular record you get back is random? There wouldn't be any way of getting back the post recent post per group for example in the below scenario.

    Code:
    SELECT 
         COUNT(t0.id) AS t0_post_per_thread
         ,t0.id AS t0_id
         ,t0.message AS t0_message
      FROM
          posts AS t0
     GROUP
        BY
         t0.thread_id
     ORDER
        BY
         t0.created
    In that scenario I get back the first post id and message in the thread. I don't think there is a away to control this when grouping information correct?

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,139
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    These are the two queries I'm essentially trying to integrate in per topic.

    information regarding the the most recent thread:
    Code:
    SELECT
         t0.title AS t0_title
         ,t0.created AS t0_created
         ,t1.name AS t1_name
      FROM
         threads AS t0
     INNER
      JOIN
         users AS t1
        ON
         t0.user_id = t1.id
     ORDER
        BY
         t0.created DESC
     LIMIT
         1
    information regarding the post recent post, the thread it belongs to and user who the post belongs to:
    Code:
    SELECT
         t2.name AS t2_name
         ,t2.id AS t2_id
         ,t1.title AS t1_title
         ,t0.created AS t0_created
      FROM
         posts AS t0
     INNER
      JOIN
         threads AS t1
        ON
         t0.thread_id = t1.id
     INNER
      JOIN
          users AS t2
        ON
         t0.user_id = t2.id
     ORDER
        BY
         t0.created DESC,t1.created DESC
     LIMIT
         1

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,139
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    This seems to be generating the expected output yet perhaps there is a more simple/efficient way?

    Code SQL:
    SELECT
         t0.name AS t0_name
         ,t1.t0_name AS t1_name
         ,COALESCE(t1.t1_post_count,0) AS t1_post_count
         ,COALESCE(t1.t1_thread_count,0) AS t1_thread_count
         ,t2.t0_title AS t2_recent_thread_title
         ,t2.t0_created AS t2_recent_thread_created
         ,t2.t1_name AS t2_recent_thread_poster
         ,t3.t2_name AS t3_recent_poster_name
         ,t3.t1_title AS t3_recent_post_thread_title
         ,t3.t0_created AS t3_recent_post_created
      FROM
         sections AS t0
      LEFT
      JOIN
         (SELECT
               t0.name AS t0_name
               ,t0.id AS t0_id
               ,t0.section_id AS t0_section_id
               ,t0.weight AS t0_weight
               ,SUM(t1.t1_post_count) AS t1_post_count
               ,SUM(t1.t1_thread_count) AS t1_thread_count
            FROM
               topics AS t0
            LEFT
            JOIN
               (SELECT
                     t0.id AS t0_id
                     ,t0.topic_id AS t0_topic_id
                     ,SUM(t1.t0_post_count) AS t1_post_count
                     ,COALESCE(COUNT(t0.id),0) AS t1_thread_count
                  FROM
                     threads AS t0
                  LEFT
                  JOIN
                     (SELECT
                          t0.thread_id AS t0_thread_id
                          ,COALESCE(COUNT(t0.id),0) AS t0_post_count
                        FROM
                           posts AS t0
                       GROUP
                          BY
                           t0.thread_id) AS t1
                   ON
                    t0.id = t1.t0_thread_id
                GROUP
                   BY
                    t0.id) AS t1
              ON
               t0.id = t1.t0_topic_id
           GROUP
              BY
               t0.id) AS t1
       ON
         t0.id = t1.t0_section_id
     LEFT
     JOIN
        (SELECT
              t0.title AS t0_title
              ,t0.created AS t0_created
              ,t1.name AS t1_name
              ,t0.id AS t0_id
              ,t0.topic_id AS t0_topic_id
           FROM
              threads AS t0
           INNER
            JOIN
               users AS t1
              ON
               t0.user_id = t1.id
           ORDER
              BY
               t0.created DESC) AS t2
       ON
        t1.t0_id = t2.t0_topic_id
     LEFT
     JOIN
        (SELECT
              t2.name AS t2_name
              ,t2.id AS t2_id
              ,t1.title AS t1_title
              ,t1.id AS t1_id
              ,t1.topic_id AS t1_topic_id
              ,t0.created AS t0_created
           FROM
              posts AS t0
          INNER
           JOIN
              threads AS t1
             ON
              t0.thread_id = t1.id
          INNER
           JOIN
              users AS t2
             ON
              t0.user_id = t2.id
          ORDER
             BY
              t0.created DESC,t1.created DESC) AS t3
       ON
        t1.t0_id = t3.t1_topic_id
    GROUP
       BY
        t1.t0_id
    ORDER
       BY
        t0.weight,t1.t0_weight


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •