Query going haywire when table added.

[hothousegraphix]

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

    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.

    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

[longneck]

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?

[hothousegraphix]

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?

[r937]

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)

[hothousegraphix]

...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.

    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

[hothousegraphix]

I'm guessing by the lack of response that the information I've provided is not sufficient to help resolve my situation?

[r937]

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?

[r937]

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 --

     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?

  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'

[hothousegraphix]

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.

  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...?

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.

  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.

[r937]

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

[hothousegraphix]

Well...using the following

  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

   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.

[r937]

entries without comments? change INNER to LEFT OUTER

no results? try taking the status check off

[hothousegraphix]

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

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.

[longneck]

are you sure that they have open comments?

[hothousegraphix]

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.

[longneck]

add t.entry_id to that query and re-run it. then run this query and compare to these results:

select entry_id
     , count(*) total_count
     , count(case status when 'open' then 1 end) as open_count
  from exp_comments
group
    by entry_id

[hothousegraphix]

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.