Idea For Writing A Mysql Query

I've a table named wp_bp_activity with many columns that I think I should work with 4 of them for this issue: id, type, item_id and date_recorded. 1 - When someone post a new activ

Solution 1:

I am going to assume that you are looking for "recent entries" (WHERE type = 'activity_update' AND date_recorded > [threshold]) and "entries having a recent reply, regardless of the entry's age" (WHERE reply.type = 'activity_comment' AND reply.date_recorded > [threshold]).

The first set is straightforward:

SELECT entries.*
FROM activity AS entries
WHERE type = 'activity_update' AND date_recorded > [threshold]

The second set is a bit less obvious:

SELECT entries.*
FROM activity AS entries
JOIN activity AS replies
    ON replies.item_id = entries.id
    AND replies.type = 'activity_comment'WHERE
    entries.type = 'activity_update'AND replies.date_recorded > [threshold]

Putting it all together:

SELECT entries.*
FROM activity AS entries
LEFT JOIN activity AS replies -- LEFT JOIN, because an INNER JOIN would filter out entries without any reply
    ON  replies.item_id = entries.id
    AND replies.type = 'activity_comment'WHERE
    entries.type = 'activity_update'AND (
        entries.date_recorded > [threshold]
        OR replies.date_recorded > [threshold] -- evaluates asFALSEif replies.date_recorded is NULL
    )
ORDERBY IFNULL(replies.date_recorded, entries.date_recorded) -- replies ifnot null, entries otherwise

I am not proud of my poorly performing ORDER BY clause, I hope someone can suggest a better idea

Solution 2:

You should add one more field

id, type, item_id, commented_on, date_recorded

::->> commented_on will justifies that item_id comments on commented_on, here 12079 comments on 12080, so it may easy for you to get what you want

So your structure should look like :

   id             type            commented_on   item_id         date_recorded
|---------|-----------------------|-----------|-----------|--------------------------
|  12081  |   activity_comment    |   12080   |   12079   |    2013-10-1807:27:01
|---------|-----------------------|-----------|-----------|--------------------------
|  12080  |   activity_update     |     0     |     0     |    2013-10-1807:26:40
|---------|-----------------------|-----------|-----------|--------------------------
|  12079  |   activity_update     |     0     |     0     |    2013-10-1705:15:43

Solution 3:

Maybe I'm missing something huge but what's wrong with just?:

SELECT*FROM `wp_bp_activity`
WHERE `type`='activity_update'ORDERBY `date_recorded` ASC
LIMIT 0, 20;

Solution 4:

You have your JOIN condition in an OR clause. This is an excellent example of why you should not use that syntax.

SELECT  a.*, b.*
FROM wp_bp_activity as a JOIN wp_bp_activity as b ON b.item_id = a.id
WHERE
  ((b.type = 'activity_update') OR (b.type = 'activity_comment'))orderby cast(a.date_recorded as datetime) desc
limit 0,20