mysql 条件连接依赖于一列

2021-12-17 00:00:00 join sql mysql

我正在尝试实现一个通用的通知系统..我有一个这样的数据结构；

I'm trying to implement a generic notification system.. I have a data structure like this;

notification_base:id,type,object
notificiation_sub:id,user_id,not_base_id,lastNotifyTime
notification_action:id,user_id,not_base_id,action,creationDate

所以基本场景是，用户通过发布状态或上传照片等创建通知基础项目(在这种情况下，照片指的是notification_base表中的类型字段，而object_id是post_id或photo_id取决于类型)然后用户订阅这个notification_base item..(用户3订阅notification_base 5和最后通知时间x)

So basic scenario is, user create a notification base item by post a status, or upload a photo and etc.(ın this case status,photo refers to type field in notification_base table and object_id is post_id or photo_id depends on type) Then user subscribe for this notification_base item..(user 3 subscribe notification_base 5 and last notify time x)

此后，另一个用户触摸了这个 notification_base 项目.(例如评论状态或喜欢照片)此操作记录在 notification_action 表中(用户 5 于 12/02/2011 执行喜欢"操作)..

After that another user touch this notification_base item. (for example commenting a status or like a photo) This action is recorded in notification_action table (user 5 make action 'like' on 12/02/2011)..

如果上次通知时间小于通知操作，我想要的是从用户订阅中获取 notification_base 项目，然后将它们与 notification_action..我可以用这个 sql 成功;

What I want is fetch the notification_base items from user subscription if last notifiytime smaller than a notification action then join them with notification_action.. I can succeed it with this sql ;

对于用户 ID 3;

select * from notification_action
                inner join notification_base on notification_action.not_base_id = notification_base.id
                inner join notification_sub on notification_action.not_base_id = notification_sub.not_base_id
                where notification_sub.user_id = 3 and notification_sub.lastShowDate < notification_action.creationDate ;

结果几乎就是我想要的，例如

result is almost what i want, for example

用户 x 在时间 t 对具有 'type' 和 object_id 的对象执行了操作"

user x did 'action' on your object which has 'type' and object_id at time t

但我也想加入 object_id 取决于类型..所以我实际上可以了解哪个对象触及..但是正如你所看到的类型是动态的，如果 type = post object id 指的是 post table 上的 post_id，如果 type=photo object id 是指 photo table 上的 photo_id 等等.

but I also want to join on object_id depends on type.. So I can actually learn which object touched.. But as you can see type is dynamic, if type = post object id refers post_id on post table, if type=photo object id refers photo_id on photo table and etc..

我尝试这样做，但遇到了一些语法错误；

I try to do it something like this but got some syntax error;

SELECT *
FROM notification_action
INNER JOIN notification_base
    ON notification_action.not_base_id = notification_base.id
INNER JOIN notification_sub
    ON notification_action.not_base_id = notification_sub.not_base_id CASE notification_base.type
            WHEN 'photo'
                THEN (
                        INNER JOIN photo
                            ON photo.id = notification_base.object_id
                        )
            ELSE (
                    INNER JOIN post
                        ON post.id = notification_base.object_id
                    )
            END
WHERE notification_sub.user_id = 3
    AND notification_sub.lastShowDate < notification_action.creationDate;

我知道不对，就像伪代码

I know that it's not correct, it's like pseudo code

推荐答案

您不能有条件地创建跨表的联接.最好的方法是使用 LEFT JOIN 并在 SELECT 语句中使用 CASE.您必须指定要显示的公共列，

you can't conditionally create joins across tables. The best way to do is to use LEFT JOIN and use you CASE in your SELECT statement. You must have specify common columns you want to show,

SELECT *,
        (CASE notification_base.type
            WHEN 'photo'
            THEN photo.columnName1
            ELSE post.ColumnName1
        END) as ColumnName1,
        (CASE notification_base.type
            WHEN 'photo'
            THEN photo.columnName2
            ELSE post.ColumnName2
        END) as ColumnName2
FROM notification_action
    INNER JOIN notification_base
        ON notification_action.not_base_id = notification_base.id
    INNER JOIN notification_sub
        ON notification_action.not_base_id = notification_sub.not_base_id
    INNER JOIN photo
        ON photo.id = notification_base.object_id
    INNER JOIN post
        ON post.id = notification_base.object_id
WHERE notification_sub.user_id = 3
    AND notification_sub.lastShowDate < notification_action.creationDate;

相关文章