SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Evangelist
    Join Date
    Feb 2007
    Posts
    402
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    counting number of forum entries

    Hi there, just having some issues with my cfquery, and wondering if anyone can assist..

    I currently have this cfquery for my forum

    <cfquery name="forumEntry" datasource="#APPLICATION.mx#">
    SELECT topic_list, topic_desc_list, id_list, COUNT(id_list_emex) AS topics, id_usr_exp, date_exp, username_exp, COUNT(content_exp) AS replies
    FROM (forum_list
    LEFT OUTER JOIN emex
    ON forum_list.id_list = emex.id_list_emex)
    LEFT OUTER JOIN emex_exp
    ON emex.id_emex=emex_exp.id_emex_exp
    GROUP by topic_list
    ORDER BY id_list
    </cfquery>

    What we have is three tables,

    Forum list is a table with the different categories of the forum..

    Emex is where I hold all the new topics

    and Emex_exp is where I hold all the replies for the topics...

    I am trying to count the number of topics and replies under a specific category, at the moment I am trying this by using "COUNT(id_list_emex) AS topics"...which is counting the number of foreign key entries from the forum list table which reside in the topics table....This doesnt seem to be working....

    I am also using "COUNT(id_emex_exp) AS replies" as the number of replies within a caterory, which is counting the number of foreign key entries from the new topics table which reside in the emex_exp table....this appears to be functioning at the moment

    Thanks
    discover song meanings and more http://www.music-explained.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    may i ask which database system this is? (it's not really a coldfusion question)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Feb 2007
    Posts
    402
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    its phpmyadmin

    thanks
    discover song meanings and more http://www.music-explained.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    actually, phpmyadmin is a front-end app for the mysql database system

    could you please run the following three queries (separately) and display what they produce
    Code:
    SHOW CREATE TABLE forum_list
    Code:
    SHOW CREATE TABLE emex
    Code:
    SHOW CREATE TABLE emex_exp
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Feb 2007
    Posts
    402
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, im not sure how to run those queries through phpmyadmin, so have produced screenshots..I hope this is the info you are after?

    http://www.musicexplained.co.uk/screenShots.cfm

    Appreciate the help, thanks
    discover song meanings and more http://www.music-explained.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that's part of it

    the other thing i'm missing is complete understanding of those tables

    do you by any chance roll up any totals from emex_exp into emex? e.g. replies_emex?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist
    Join Date
    Feb 2007
    Posts
    402
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post

    do you by any chance roll up any totals from emex_exp into emex? e.g. replies_emex?
    That was my initial intention with the replies_emex field, but I thought there would be away that could calculate the totals using sql..
    discover song meanings and more http://www.music-explained.com

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    try this --
    Code:
    SELECT f.topic_list
         , f.topic_desc_list
         , f.id_list
         , COUNT(e.id_emex) AS topics
         , COALESCE(SUM(e.ee_count),0) AS replies
      FROM forum_list AS f
    LEFT OUTER 
      JOIN ( SELECT emex.id_emex
                  , COUNT(emex_exp.id_emex_exp) AS ee_count
               FROM emex 
             LEFT OUTER 
               JOIN emex_exp 
                 ON emex_exp.id_emex_exp = emex.id_emex
             GROUP
                 BY emex.id_emex
           ) AS e
        ON e.id_emex = f.id_list
    ORDER 
        BY f.id_list
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Evangelist
    Join Date
    Feb 2007
    Posts
    402
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi there, thanks for this...much appreciation..

    am getting this error message however

    Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

    Am trying to get a grasp of this sql, as not come across some of the methods before....never seen COALESCE before....and i didnt know you could perform selects within a join...
    discover song meanings and more http://www.music-explained.com

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    whoops, my bad, try this --
    Code:
    SELECT f.topic_list
         , f.topic_desc_list
         , f.id_list
         , COUNT(e.id_emex) AS topics
         , COALESCE(SUM(e.ee_count),0) AS replies
      FROM forum_list AS f
    LEFT OUTER 
      JOIN ( SELECT emex.id_emex
                  , COUNT(emex_exp.id_emex_exp) AS ee_count
               FROM emex 
             LEFT OUTER 
               JOIN emex_exp 
                 ON emex_exp.id_emex_exp = emex.id_emex
             GROUP
                 BY emex.id_emex
           ) AS e
        ON e.id_emex = f.id_list
    GROUP
        BY f.topic_list
         , f.topic_desc_list
         , f.id_list
    ORDER 
        BY f.id_list
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Evangelist
    Join Date
    Feb 2007
    Posts
    402
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi there...

    Thanks, have added that, but its not calculating the number of topics and replies within a specific forum category correctly...


    Also, I am trying to output the last response made with a specific forum category from this query...and this involves outputting the fields "date_exp", which is the date of the last forum entry and "username"...which will be the name of the person who made the last entry.....at the moment an recieving an error message saying these variables are undefined, as they have not been included in the query
    discover song meanings and more http://www.music-explained.com

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by namtax View Post
    its not calculating the number of topics and replies within a specific forum category correctly...
    sorry, but try as i might, i don't see a "forum category" column anywhere

    in any case, i believe i have shown you the general strategy --

    in a three-table one-to-many-to-many relationship, you need to count the rows of the lowest table at the second level, and then sum those counts at the top level

    you'll have to work out which columns to use because frankly, i still don't think i understand them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Evangelist
    Join Date
    Feb 2007
    Posts
    402
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry, forum category column is f.topic_list if that helps?
    discover song meanings and more http://www.music-explained.com

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, remove both f.topic_desc_list and f.id_list from both the SELECT and GROUP BY clauses
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Evangelist
    Join Date
    Feb 2007
    Posts
    402
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    still not working unfortunately...

    The introductions category has got 3 topics and 7 replies....but the query states that it has 1 topic and 4 replies..

    not sure why
    discover song meanings and more http://www.music-explained.com

  16. #16
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,192
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    I rather not steal the thread but seems like your trying to accomplish a similar selection in the same context.

    http://www.sitepoint.com/forums/show...12#post4206053

    I'm not positive whether its the most efficient way, though.

  17. #17
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,192
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    Your column names a little weird, so it might be best to post the table structures. Normally for a primary key its just more readable to name it id rather then having inconsistent. Is this giving you some type of output?

    Code SQL:
    SELECT
         t1.t0_replies_per_category AS replies_per_category
         ,t1.t0_threads_per_category AS threads_per_category
      FROM
         forum_list AS t0
      LEFT
      JOIN
         (SELECT
               ,t0.id_list_emex AS t0_id_list_emex
               ,SUM(t0.t0_replies_per_thread) AS t0_replies_per_category
               ,COALESCE(COUNT(t0.id),0) AS t0_threads_per_category
            FROM
               emex AS t0
            LEFT
            JOIN
               (SELECT 
                     t0.id_emex_exp AS t0_id_emex_exp
                     ,COALESCE(COUNT(t0.id),0) AS t0_replies_per_thread
                  FROM
                     emex_exp AS t0
                  GROUP
                     BY
                      t0.id_emex_exp) AS t1
              ON
                t0.id_emex = t1.t0_id_emex_exp
            GROUP
               BY
                t0.id_list_emex) AS t1
         ON
           t0.id_list = t1.t0_id_list_emex
     GROUP
        BY
         t0.topic_list
     ORDER
        BY
         t0.id_list


    Regardless this is how you can structure the nested subqueries:

    Code:
    -- select categories
             - select both calculated columns
            - group by primary key
       -- select threads
             - group by category foreign key and count by primary key to get threads per category
             - add together the calculated column of the previous nested query using SUM to get replies per category
          -- select posts
             - group by thread foreign key and count by primary key to get replies per thread
    -- select most recent post
    -- select most recent thread
    The from my exploration I believe you need to handle selecting the most recent thread or post separately. Since, you have divided your threads and posts that would be done in the exact context in which I showed in the previous query. Essentially you need to run two left joins to select the most recent post and the most recent thread. Then decide on the server side which is most recent.

    I'm not sure this is the most optimal way to do all this in one query, but the logic seems to be providing me with the expected output on my main forum page. So if anyone want to chime in on a more optimal way by all means I'm all ears.

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    oddz, that looks like you did a lot of work on this

    nice job

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Evangelist
    Join Date
    Feb 2007
    Posts
    402
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks oddz...

    Quote Originally Posted by oddz View Post
    Your column names a little weird, so it might be best to post the table structures.
    http://www.musicexplained.co.uk/screenShots.cfm ..is this useful?

    I have run the query, but am recieving this error message

    Unknown column 't0.id' in 'field list'

    Maybe I should change my columns and table names to something more appropriate?
    discover song meanings and more http://www.music-explained.com


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
  •