编写 mysql 查询的想法 [英] Idea for writing a mysql query

问题描述

我有一个名为 wp_bp_activity 的表，其中包含许多列，我认为我应该使用其中的 4 个来解决这个问题:id, type、item_id 和 date_recorded.

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 - 当有人发布新活动时，type 是 activity_update 而 item_id 是 0

1 - When someone post a new activity the type is activity_update and item_id is 0

2 - 当有人对活动发表新评论时，其 type 是 activity_comment 并且 item_id 存在id

2 - When someone post a new comment on an activity its type is activity_comment and item_id is existed activity id in column id

3 - 在两者中，date_recorded 是插入数据的日期.

3 - In both, date_recorded is the date of when data is inserted.

表中还有更多type.

但我只想获取最近有人回复或的type的activity_update的行是新的(基于date_recorded 我认为)

But I wanna fetch only rows with type of activity_update that someone is recently replied to or are new (based on date_recorded I think)

我试过的是:

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

执行时间过长，并以内存不足错误结束.

That takes too long to be executed and ends with memory insufficient error.

对此类查询的任何帮助表示赞赏.

Any help on this kind of query is appreciated.

更新 #1

                        wp_bp_activity table

    id              type             item_id         date_recorded
|---------|-----------------------|-----------|--------------------------
|  12081  |   activity_comment    |   12079   |    2013-10-18 07:27:01
|---------|-----------------------|-----------|--------------------------
|  12080  |   activity_update     |     0     |    2013-10-18 07:26:40
|---------|-----------------------|-----------|--------------------------
|  12079  |   activity_update     |     0     |    2013-10-17 05:15:43

我想从这个表中得到哪些行是这些 id 的顺序:

12079
12080

我不想得到的:

activity_comment 类型

应该以什么顺序获取行?

如您所见，activity_comment 类型的行有一个 item_id，其值为 12079.所以12079是最近有人评论它的最新活动，12080没有评论只是发布.所以我想要两个，但按这个顺序:

As you can see the row with activity_comment type has an item_id with the value of 12079. so 12079 is the latest activity that recently someone made a comment on it, and 12080 has no comments but is just posted. So I want both but at this order:

12079
12080

推荐答案

我假设您正在寻找最近的条目"(WHERE type = 'activity_update' AND date_recorded > [threshold]) 和具有最近回复的条目，无论条目的年龄如何"(WHERE reply.type = 'activity_comment' AND reply.date_recorded > [threshold]).

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

第一组很简单:

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

第二组不太明显:

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]

综合起来:

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 as FALSE if replies.date_recorded is NULL
    )
ORDER BY IFNULL(replies.date_recorded, entries.date_recorded) -- replies if not null, entries otherwise

我并不为我的 ORDER BY 子句表现不佳而自豪，我希望有人能提出更好的想法

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

这篇关于编写 mysql 查询的想法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！