SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query going haywire when table added.

    Working with a CMS (EE)

    I have a query which I'm using to
    1) pull in entry info
    2) order entries by activity
    where activity is defined as the greater of the two values entry_date or recent_comment_date.

    Below is the query and it works as advertised
    Code:
        SELECT DISTINCT m.username, 
               m.avatar_filename, 
               t.title, 
               t.url_title,
               t.view_count_four, 
               t.comment_total, 
               t.entry_date, 
               t.recent_comment_date, 
               IF(t.entry_date > t.recent_comment_date, t.entry_date, t.recent_comment_date) as recent_activity, 
               cats.cat_url_title 
          FROM exp_members AS m 
     LEFT JOIN exp_weblog_titles AS t 
            ON m.member_id = t.author_id 
     LEFT JOIN exp_category_posts AS cp 
            ON t.entry_id = cp.entry_id 
     LEFT JOIN exp_categories AS cats 
            ON cp.cat_id = cats.cat_id 
         WHERE cats.cat_url_title = 'trends' //where trends is equal to {segment_4}
         ORDER 
            BY recent_activity DESC
    I'd like to update this so if a comment is made to an entry within the selected category, I can display some additional info related to the comment. This data will be coming from the exp_comments table:
    1) {commnet_id}
    2) {name} // username of comment author

    But when I add this table to the query everything goes haywire.
    Code:
        SELECT DISTINCT c.comment_id, 
               c.name, 
               m.username, 
               m.avatar_filename, 
               t.title, 
               t.url_title, 
               t.view_count_four, 
               t.comment_total, 
               t.entry_date, 
               t.recent_comment_date, 
               IF(t.entry_date > t.recent_comment_date, t.entry_date, t.recent_comment_date) as recent_activity, 
               cats.cat_url_title 
          FROM exp_members AS m 
     LEFT JOIN exp_weblog_titles AS t 
            ON m.member_id = t.author_id
     LEFT JOIN exp_comments AS c //exp_comments added here on common column of entry_id
            ON t.entry_id = c.entry_id 
     LEFT JOIN exp_category_posts AS cp 
            ON c.entry_id = cp.entry_id 
     LEFT JOIN exp_categories AS cats 
            ON cp.cat_id = cats.cat_id 
         WHERE t.weblog_id = 6 AND cats.cat_url_title = 'trends' 
         ORDER 
            BY recent_activity DESC
    The above attempt seems to be returning all comments to each entry within the selected category. Not what I want.

    I want to do what my initial query is doing, return each entry within a selected category, and if that entry has a comment, pull in the data for {comment_id} and {name}.

    This make sense? This is clearly related to how I'm joining the tables and the inclusion of the {comment_id) column in relationship to the DISTINCT statement but I can't seem to figure out how to get around this and at the data from exp_comment without screwing up my original intent.

    If I remove c.comment_id and c.name from my SELECT the query performs as I want it to but I don't have the additional info I'm after.

    Would anyone be able to lend a hand?

    Thanks
    Last edited by hothousegraphix; Jun 13, 2008 at 11:58.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    so you problem is you joined the comments table and you're getting all of the comments? that's what happens when you join.

    you need to decide WHICH comment you want. the first? the last?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey, thank you for responding.
    that's what happens when you join.
    I understand; and I understand what I've done is incorrect based on my desired results.
    you need to decide WHICH comment you want. the first? the last?
    This will not work for me.

    I may not have indicated this clearly enough; but, I need to base this around each entry, not comments as some entries may not have comments but I still need to display those entries.

    This will mean for some entries the {comment_id} and {c.name} columns will return values of null.

    How do I keep my query keying off each entry but also pull in the data from my exp_comments table?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    could you please give a quick rundown on the one-to-many relationships involved here

    also, where is the posts table? is there a posts table?

    you see, the biggest hurdle in debugging a query is understanding the tables and their relationships

    and i'm not sure your first query is correct either, because you're using DISTINCT (often a sign that something is wrong)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ...and i'm not sure your first query is correct either, because you're using DISTINCT (often a sign that something is wrong)
    I believe I have that addressed with the addition of my "weblog_id" to the WHERE statement.
    Code:
        SELECT m.username, 
               m.avatar_filename, 
               t.title, 
               t.url_title,
               t.view_count_four, 
               t.comment_total, 
               t.entry_date, 
               t.recent_comment_date, 
               IF(t.entry_date > t.recent_comment_date, t.entry_date, t.recent_comment_date) as recent_activity, 
               cats.cat_url_title 
          FROM exp_members AS m 
     LEFT JOIN exp_weblog_titles AS t 
            ON m.member_id = t.author_id 
     LEFT JOIN exp_category_posts AS cp
            ON t.entry_id = cp.entry_id 
     LEFT JOIN exp_categories AS cats 
            ON cp.cat_id = cats.cat_id 
         WHERE t.weblog_id = "6" AND cats.cat_url_title = 'trends'
         ORDER 
            BY recent_activity DESC
    ...could you please give a quick rundown on the one-to-many relationships involved here
    I'll try my best to explain whats occurring. The second query in my initial post (which includes the LEFT JOIN to my exp_comments table) returns what I need if {c.comment_id} and {c.name} are NOT included in the SELECT.

    When included in the SELECT the query returns all comment_id's for each entry in the selected category. See second attached image.
    where is the posts table? is there a posts table?
    By "posts table" do you mean "comments" or "entry"?

    Here is a rundown of my tables. I have:
    exp_members - includes member info; ie member_id = author_id
    exp_comments - includes all comment info for each weblog entry (common columns: entry_id; author_id)
    exp_weblog_titles - includes all entry stats: entry title, entry date, entry_id, recent comment date, etc
    exp_category_posts - relationship table that assigns a each entry to a category: entry_id to category_id
    exp_categories - assigns readable category name to category_id

    I've attached two screen captures to help further explain.
    query_1.gif shows the results from my first query and how I'd like the results to look less the two needed columns.

    query_2.gif shows the results from my second attempt and shows how each comment for entry is displayed.

    I hope this is helpful information.

    Thanks
    Attached Images Attached Images

  6. #6
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm guessing by the lack of response that the information I've provided is not sufficient to help resolve my situation?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sorry, don't know how this slipped past me

    the answer is, your query shows multiple comments per entry

    this is not "going haywire" because it is exactly what you asked for

    if an entry has multiple comments, and you retrieve all of them, then there will be one row in the result set for each comment for each entry

    if, instead, what you want is one row per entry, then you need to display the comment data differently -- perhaps concatenate all comments into one value, or not show all the comments for each entry but instead pick just one, etc.

    does that make sense?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    by the way, the purpose of the query is to show all entries for a specific weblog in a specific category

    your FROM clause should reflect that

    what you have is this --
    Code:
         FROM exp_members AS m 
     LEFT JOIN exp_weblog_titles AS t 
            ON m.member_id = t.author_id 
     LEFT JOIN exp_category_posts AS cp
            ON t.entry_id = cp.entry_id 
     LEFT JOIN exp_categories AS cats 
            ON cp.cat_id = cats.cat_id 
         WHERE t.weblog_id = "6" AND cats.cat_url_title = 'trends'
    basically, this says start with the member table, get all entries every member ever wrote, get all categories for each of those entries, and then throw everything away except for entries in this specific weblog in this specific category

    instead, your FROM clause should work like this: start with the specified category, get all entries in the specified weblog, and get the authors of those entries

    see the difference?
    Code:
      FROM exp_categories AS cats
    INNER
      JOIN exp_category_posts AS cp
        ON cp.cat_id = cats.cat_id
    INNER
      JOIN exp_weblog_titles AS t 
        ON t.entry_id = cp.entry_id 
       AND t.weblog_id = 6
    INNER
      JOIN exp_members AS m 
        ON m.member_id = t.author_id 
     WHERE cats.cat_url_title = 'trends'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey...thank you for getting back to me.

    I suppose the use of "going haywire" was more a reference to the query I had straying from my purpose/goal; fully understanding it was doing what I was telling it to...and that the problem was me.
    the purpose of the query is to show all entries for a specific weblog in a specific category
    Exactly.

    you need to display the comment data differently -- perhaps concatenate all comments into one value, or not show all the comments for each entry but instead pick just one, etc.

    does that make sense?
    It does...but this is obviously where I'm getting lost.

    So...taking your lead...if I were to and in my comments table (initially with no refinements) I'm guessing it should look like this.
    Code:
      FROM exp_categories AS cats
    INNER
      JOIN exp_category_posts AS cp
        ON cp.cat_id = cats.cat_id
    INNER
      JOIN exp_weblog_titles AS t 
        ON t.entry_id = cp.entry_id 
       AND t.weblog_id = 6
    INNER
      JOIN exp_comments AS c 
        ON t.entry_id = c.entry_id 
    INNER
      JOIN exp_members AS m 
        ON m.member_id = c.author_id 
     WHERE cats.cat_url_title = 'trends'
    So...with the comments table added my results set strays from what I want in two ways...one, as before, I'm returning every comment for each weblog entry; and two, I've lost the author information as it relates to the (parent) weblog entry.

    To eliminate all the extra comments, or maybe better described...only retun the most recent comment couldn't I do this...?
    Code:
    INNER
      JOIN exp_comments AS c 
        ON t.entry_id = c.entry_id
       AND c.comment_date = 
             (SELECT MAX(comment_date) // only most recent
                FROM exp_comments
               WHERE entry_id = t.entry_id 
                 AND status = 'open' ) // only open comment
    I think this should leave me with just one issue to resolve...the loss of my weblog entry's author information.
    Code:
      FROM exp_categories AS cats
    INNER
      JOIN exp_category_posts AS cp
        ON cp.cat_id = cats.cat_id
    INNER
      JOIN exp_weblog_titles AS t 
        ON t.entry_id = cp.entry_id 
       AND t.weblog_id = 6
    // Does my comments table have to JOIN to my members table? Or, can I JOIN two different table to two separate columns on one table?
    INNER
      JOIN exp_comments AS c 
        ON t.entry_id = c.entry_id
       AND c.comment_date = 
             (SELECT MAX(comment_date) // only most recent
                FROM exp_comments
               WHERE entry_id = t.entry_id 
                 AND status = 'open' ) // only open comment
    INNER
      JOIN exp_members AS m 
        ON m.member_id = t.author_id 
     WHERE cats.cat_url_title = 'trends'
    Is this heading in the right direction?

    Thanks.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, choosing the latest comment per entry is definitely going to solve the "haywire" problem (see your reply to post #2... it looks like you might have thought he was suggesting the last comment ever made on any entry, which of course would not be right)

    you're doing the MAX subquery right, too

    i didn't understand this --
    // Does my comments table have to JOIN to my members table? Or, can I JOIN two different table to two separate columns on one table?
    the way joins work, each table that you bring into the join must be tied, by column values, to some previous table or tables

    so you're joining the members table correctly, too

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

  11. #11
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well...using the following
    Code:
      SELECT m.username, 
                    m.avatar_filename, 
                    t.title, 
                    t.url_title,
                    t.view_count_four, 
                    t.comment_total, 
                    t.entry_date, 
                    t.recent_comment_date, 
                    IF(t.entry_date > t.recent_comment_date, t.entry_date, t.recent_comment_date) as recent_activity, 
                    cats.cat_url_title
      FROM exp_categories AS cats
    INNER
      JOIN exp_category_posts AS cp
        ON cp.cat_id = cats.cat_id
    INNER
      JOIN exp_weblog_titles AS t 
        ON t.entry_id = cp.entry_id 
       AND t.weblog_id = 6
    INNER
      JOIN exp_comments AS c 
        ON t.entry_id = c.entry_id
       AND c.comment_date = 
             (SELECT MAX(comment_date)
                FROM exp_comments
               WHERE entry_id = t.entry_id 
                 AND status = 'open' )
    INNER
      JOIN exp_members AS m 
        ON m.member_id = t.author_id 
     WHERE cats.cat_url_title = 'trends'
    produces No results. If I remove the subquery on "c.comment_date" my results set returns all comments to all entries within the selected category.

    I tried moving the
    Code:
       AND c.comment_date = 
             (SELECT MAX(comment_date)
                FROM exp_comments
               WHERE entry_id = t.entry_id 
                 AND status = 'open' )
    to the WHERE statement but that too produced no results.

    Additionally, I'm missing (and I should have noticed this sooner) entries without comments.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    entries without comments? change INNER to LEFT OUTER

    no results? try taking the status check off
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok...

    first...thanks for staying with me.

    So...this query is now returning the correct number of rows (based on entries) and ordering my results correctly but with one issue
    Code:
    SELECT
      c.comment_id,
      c.name,
      m.username, 
      m.avatar_filename, 
      t.title, 
      t.url_title,
      t.view_count_four, 
      t.comment_total, 
      t.entry_date, 
      t.recent_comment_date, 
      IF(t.entry_date > t.recent_comment_date, t.entry_date, t.recent_comment_date) as recent_activity, 
      cats.cat_url_title
      FROM exp_categories AS cats
    INNER
      JOIN exp_category_posts AS cp
        ON cp.cat_id = cats.cat_id
    INNER
      JOIN exp_weblog_titles AS t 
        ON t.entry_id = cp.entry_id 
       AND t.weblog_id = 6
    LEFT OUTER
      JOIN exp_comments AS c 
        ON t.entry_id = c.entry_id
       AND c.comment_date = 
             (SELECT MAX(comment_date)
                FROM exp_comments
               WHERE entry_id = t.entry_id 
                 AND status = 'open' )
    INNER
      JOIN exp_members AS m 
        ON m.member_id = t.author_id 
    WHERE cats.cat_url_title = 'trends'
    ORDER BY
      recent_activity DESC
    My columns c.comment_id, and c.name are returning nothing for entries which do have comments.

  14. #14
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    are you sure that they have open comments?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  15. #15
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    are you sure that they have open comments?
    Yes. Results attached.

    Results 1 and 2 are new entries with no comments. Results 3-6 each have at least one comment.
    Attached Images Attached Images

  16. #16
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    add t.entry_id to that query and re-run it. then run this query and compare to these results:
    Code:
    select entry_id
         , count(*) total_count
         , count(case status when 'open' then 1 end) as open_count
      from exp_comments
    group
        by entry_id
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  17. #17
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have to apologize.

    It turns out that I've been using the status values established in exp_weblog_titles for exp_comments. Once I updated "open" to "o" my columns populated.



    Thank you both for your assistance. It is much appreciated.


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
  •