mysql条件连接取决于列 [英] mysql conditional join depends on a column
问题描述
我正在尝试实现通用的通知系统.我有这样的数据结构；
notification_base:id,type,object
notificiation_sub:id,user_id,not_base_id,lastNotifyTime
notification_action:id,user_id,not_base_id,action,creationDate
因此，基本情况是，用户通过发布状态来创建通知基础项，或者上传照片等.(在这种情况下，照片是指notify_base表中的type字段，而object_id是post_id或photo_id取决于类型) 然后，用户订阅此notification_base项.(用户3订阅notification_base 5，最后通知时间x)
此后，另一个用户触摸此notification_base项目. (例如，评论状态或像照片一样) 此操作记录在notification_action表中(用户5在2011年2月2日做出喜欢"操作).
我想要的是，如果上次通知时间小于通知动作，则从用户订阅中获取notification_base项，然后将它们与notification_action一起加入. 我可以使用此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 ;
例如，
结果几乎是我想要的
用户x对您的对象执行了操作"，该对象在时间t具有类型"和object_id
但是我也想加入object_id取决于类型.所以我实际上可以了解所接触的对象..但是如您所见，type是动态的，如果type = post object id引用post_id在post table上，如果type =照片对象ID是指在照片桌子等上的photo_id.
我尝试这样做，但是出现语法错误；
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;
我知道这是不正确的，就像伪代码
您不能有条件地在表之间创建联接.最好的方法是使用LEFT JOIN并在SELECT语句中使用您CASE.您必须指定要显示的公共列，
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;
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
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)
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)..
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 ;
For user 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
user x did 'action' on your object which has 'type' and object_id at time t
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
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;
这篇关于mysql条件连接取决于列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
