CODEWITHSUNDEEP
mysqlselectinner-join
Andre Chenier
Andre Chenier

Reputation: 1186

MySQL Select Newest Approval Date among article date & comment date

My aim is to write the last modified date (lastmod) of my approved articles for my sitemap.xml file. There are 2 possibilities for this data.

  1. If article has no approved comment, then lastmod is the approval date of the article.
  2. If article has at least 1 approved comment, then lastmod is the approval date of the last approved comment of related article.

after asking php unsuccessful while loop within prepared statements question and reading answers, I decided

  1. not to use loop within prepared for this case
  2. add col_article_id column to my comments table which shows the related article's id in article table
  3. and try to solve my case with a smarter mySQL query

I have 2 tables:

  1. articles
  2. comments (comments.col_article_id links the comment to the related article)

After I tried query below,

select tb_articles.col_approvaldate, tb_comments.col_approvaldate
from tb_articles, tb_comments
where tb_articles.col_status ='approved' AND tb_comments.col_status ='approved' AND tb_articles.col_id=tb_comments.col_article_id

my problems are:

1 - I need someway as if it was allowed in mysql syntax that select max( tb_articles.col_approvaldate, tb_comments.col_approvaldate)

2 - If I have n approved articles without any approved comment and m approved articles with approved comment(s), then I should have n+m result rows with 1 column at each row. But currently, I have m rows with 2 columns at each row.

So I'm aware that I'm terribly on wrong way.

I also searched "mysql newest row per group" keywords. But this the point I could arrived after all.

this is my 1st join experience. can you please correct me? best regards

Upvotes: 0

Views: 184

Answers (2)

Bohemian
Bohemian

Reputation: 425198

Try this:

select
    tb_articles.id,
    ifnull(
        max( tb_comments.col_approvaldate),
        tb_articles.col_approvaldate
    ) as last_approved
from tb_articles
left join tb_comments
    on tb_articles.col_id=tb_comments.col_article_id
    and tb_comments.col_status ='approved'
where tb_articles.col_status ='approved'
group by 1;

Upvotes: 1

sashkello
sashkello

Reputation: 17871

Do I understand correctly that tb_comments record for an article is created by default? This should not be the case - if there are no comments, there shouldn't be a record there. If there is, what is the default date you are putting in? NULL? Also tb_comments.col_status seems redundant to me - if you have tb_comments.col_approvaldate then it is approved on that date and you don't really need status at all.

This query is probably what would work for you (commented AND shouldn't change things if I understand your table structure properly):

SELECT 
    a.col_id AS 'article_id',
    IFNULL(MAX(c.col_approvaldate), a.col_approvaldate)
FROM 
    tb_articles a 
    LEFT JOIN tb_comments c ON a.col_id = c.col_article_id #AND c.col_status ='approved'
WHERE 
    a.col_status ='approved'
GROUP BY a.col_id

Upvotes: 0

Related Questions