0

I am working on a social network website project. I have created database and everything. The posts table has a preference column which stores the preference value according to the likes and comments that a post gets from the users and also the time at which the post is created. To retrieve posts for a user's home page from the posts table, I am running a query using joins which sorts using preference column . Now, suppose I retrieve 10 posts for a user to be shown on the posts table and user scrolls down and one more request is made from the user to retrieve next 10 posts to the server.

If in between of those requests few other users creates a new post or preference value of posts in the database changes in the between, and now if I the second request is run on the server, all the posts will be resorted for the second request (i.e. to show next 10 posts) but since the database is updated , this means in the second request there will be many chances that few of earlier 10 posts are retrieved along in the second request.

I want to know how to avoid these duplicate requests. How facebook or any other social network solves this problem at the backend when their database is dynamic.

peterdotplus
  • 335
  • 1
  • 11
The Coder
  • 121
  • 1
  • 7
  • Does facebook ever solve it? According to my experience, there is NO ORDER at all in the facebook news feed. You never actually sure of the order of posts, nor if you have been shown certain post at all. – Your Common Sense Oct 02 '13 at 09:01
  • Facebook used to do it and while it defaults to some fairly useless sort order these days there is still an option to sort by post time (not that it always does that particularly well) – Kickstart Oct 02 '13 at 09:13

3 Answers3

0

I would rather avoid such unreliable way of sorting at all.

As a user, I'd rather quit that service. Frankly, I hate such too smart a service which decides which posts I have to see and which not. And even dynamically ordered on top of that.

Make it ordered by date, by tags of interest, by something sensible, reliable and constant.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • But what about the popular things going on and around you. You don't know when you will be active and you want interesting feeds in your homepage. – The Coder Oct 02 '13 at 09:25
  • Well, personally I especially hate "popular" things. Peoples usual interests are vanity and vain. So, I'd prefer things likes **I**, not some 11 year old girl from other side of world. And even my friends interests can be of no use for me. – Your Common Sense Oct 02 '13 at 09:30
  • I really appreciate your needs but a social network is not made just for single person. If we consider in this world, most of the users wants to get more in less time avoiding the waste posts from their homepage. And considering a useful social network, it should always have an option to provide popular posts on their homepage. – The Coder Oct 02 '13 at 09:37
  • that's the problem. With social networks and whole world. Prople share crap, like crap, and make crap spread around. – Your Common Sense Oct 02 '13 at 09:50
-1

In your script store a record of the rows id returned.

For example, using a basic limit and just storing the latest id when the first select is done, and using the page number to determine the limit of records to return.

SELECT id, somefield
FROM SomeTable
WHERE id < $SOMESTOREDVALUE
LIMIT $PAGENUMBERTIMESTEN, 10

or storing the latest id after each page is returned (which you will need to store each time this is run)

SELECT id, somefield
FROM SomeTable
WHERE id < $SOMESTOREDVALUE
LIMIT 0, 10
Kickstart
  • 21,403
  • 2
  • 21
  • 33
  • That's amazing example of usual PHP users cargo cult attitude. Even if post explicitly stated that no id involved, they cannot think of any other field to order by. – Your Common Sense Oct 02 '13 at 09:03
  • Far from it. Storing a single key field requires virtually no storage (an int of some size), and should be efficient and avoids issues with 2 posts having the same timestamp. And where does the post explicitly say that no id is involved? – Kickstart Oct 02 '13 at 09:12
  • As far as I can understand, your solution still wont solve the problem. Instead the read queries will take more time in the mysql database. – The Coder Oct 02 '13 at 09:32
  • That is a potential issue with the first sql (once the number of posts gets high) as your db would have to retrieve all the matching records and order them before limiting the results (although a key on id, or whatever other field you use to order the results would greatly reduce this). The 2nd should reduce this a touch as it will not need to retrieve and sort out and earlier records. I assume you want these records for some type of ajax based continuous scrolling. – Kickstart Oct 02 '13 at 09:45
  • If you want to cope with a complex and calculated sort order (based on preferences, likes, or the like) then the only solution I can see would be to store the viewed post ids by each user, and use a LEFT JOIN between that and your main query (checking that the LEFT JOINed rows are not found). This would be a really hideous amount of storage once you had lots of users, gives you a nightmare of reliably clearing out the viewed posts stores and would not cope easily with dynamically changing preferences well. – Kickstart Oct 02 '13 at 09:56
-1

If you store the time & date when the user first makes a request in a session, you could use that to filter the posts table.

So your SQL for the second page of results would be along the lines of

SELECT <some fields> FROM <sometables> 
WHERE DatePosted <= $timefirstseen LIMIT 10, 10

Where $timefirstseen was loaded from the session variable. This will restrict your results to only posts that existed when the users visit started.

You would of course need to include a feature to allow the user to clear the session or do that automatically when they revisit their homepage to make sure they got to see the new posts eventually!

Clart Tent
  • 1,319
  • 2
  • 9
  • 11
  • But in this case I will loose the popular posts which must come to the top and that is the reason for maintaining a `preference` field in the posts table. – The Coder Oct 02 '13 at 09:20
  • You can still keep the same "ORDER BY" that you already use to sort the posts - in fact you'll have to - but by filtering on the "date posted" field you'll just make sure that you don't display the same set of posts again. – Clart Tent Oct 02 '13 at 09:29
  • obviously it will not. As creation time has nothing to do with sorting order – Your Common Sense Oct 02 '13 at 09:47
  • not to mention **obvious** problems with storing state in a session - as user can have more than one tab opened, etc. – Your Common Sense Oct 02 '13 at 09:52