Edit Question - KnowledgeHub

Title Body

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

Tags (comma-separated) Cancel