2

i m working on a notification system just like facebook has .

my database structure is

enter image description here

i have made type_id such as type of notification . [ wall comments, wall post ,etc]

the problem is how do i retrieve data from the notification like using timestamp i can get data in according to updated date or time but still after getting the type . how to match the type with the other tables needed to show outcome like random notification in facebook . can anyone help me out with the database query .

like for type

wall comment -- i have to retrieve data from the wall comment and notification table both

wall post -- i have to retrieve data from the wall post and notification table both

how to combine them according to the timestamp later on and get results according to like this

for example

rahul posted on your wall 2 hours ago

ritu commented on your post 1 hrs ago

can any one help??

Sakshi Sharma
  • 1,414
  • 4
  • 20
  • 39

2 Answers2

3

I would not try to do it all in one query. I'd rather make several small simple queries, one for each type of entity (wall post, comment, etc), then assemble results in PHP code and send to the client.

Don't overcomplicate things, there's plenty of complexity already. :-)

Examples of queries:

SELECT p.*, n.*
FROM notifications n
INNER JOIN wall_posts p ON p.id = n.item_id
WHERE n.type_id = 'wall_post' AND n.is_seen = 0;
ORDER BY time_stamp DESC
LIMIT 10;

SELECT c.*, n.*
FROM notifications n
INNER JOIN wall_comments c ON c.id = n.item_id
WHERE n.type_id = 'wall_post' AND n.is_seen = 0;
ORDER BY time_stamp DESC
LIMIT 10;
Sergio Tulentsev
  • 226,338
  • 43
  • 373
  • 367
  • yes m trying the same thing i have made several small query but still the question is that when all the "type" queries are made then how would i show them to the client according to the timestamp in the database and arrange them the way they were triggered ??? thanks for replyin @sergio – Sakshi Sharma Jan 07 '12 at 09:30
  • Well, you're selecting timestamp, right? Then sort them in your PHP code and give out to the client. – Sergio Tulentsev Jan 07 '12 at 09:36
  • suppose i have the data of all types by making separate query for all and now i want to show them on a div according to the timestamps in database . now how would i do that ,i have used timestamps only in queries such that ORDER BY time_stamp DESC , now when all the queries are already executed how to combine it according to the timestamps in database and show to the client??? – Sakshi Sharma Jan 07 '12 at 09:43
  • @SakshiSharma: You can use code from that topic: http://stackoverflow.com/questions/2699086/php-sort-multidimensional-array-by-value – Sergio Tulentsev Jan 07 '12 at 09:45
  • thanks for the help , will try and tell the results today , well now m bit less confused thanks for the help :) – Sakshi Sharma Jan 07 '12 at 09:50
  • @SakshiSharma: well, come back with questions :-) – Sergio Tulentsev Jan 07 '12 at 09:51
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/6504/discussion-between-sakshi-sharma-and-sergio-tulentsev) – Sakshi Sharma Jan 07 '12 at 11:44
1

It depends on how the data is structured in your other tables. If it's similar (or can at least be summarised in a similar fashion) then you could use a UNION like this

SELECT * FROM
  ( 
    SELECT date,title,summary FROM notifications 
      INNER JOIN wall_comments ON (item_id=item_id AND type_id='wall_comments')
      WHERE user_id=X
  ) UNION
  ( 
    SELECT date,title,summary FROM notifications 
      INNER JOIN wall_posts ON (item_id=item_id AND type_id='wall_posts')
      WHERE user_id=X
  ) UNION 
  ( etc..
  )
ORDER BY date desc

You may want to put other clauses into your WHERE sub-clauses to restrict the sub-queries. If you're really finding performance issues, you might want to look at denormalising your data into one table with the relevant info in.

liquorvicar
  • 6,081
  • 1
  • 16
  • 21