0

I have a MySQL database with thousand of articles. Each article can be flagged as READ or UNREAD (bit). In a web interface a user can choose how many articles to display and whether to view all of them, or just the UNREAD ones. In Javascript side I store a limitStart value that I use to mount the LIMIT for the next query. For example in the first call we get the first 3 articles, I store limitStart=3 and in the next call I ask for more starting from 3.

In any moment the user can mark an article as READ, and here is the problem. If a user has the UNREAD filter on, then marks an article as READ and asks for more items, he will miss an article because the limitStart is not anymore 3 (but 2) because he has actually mark one as read.

Imagine we have this list of articles in the database, flagged as follows:

1 UNREAD
2 READ
3 UNREAD
4 READ
5 UNREAD
6 UNREAD
7 UNREAD
8 UNREAD
9 UNREAD

In the first call with the UNREAD filter on, a user gets the articles 1,3,5 (SELECT * WHERE UNREAD=1 LIMIT 0,3).

Now he marks article 3 as READ and asks for more with: SELECT * WHERE UNREAD=1 LIMIT 3,3

1 UNREAD
2 READ
3 *READ
4 READ
5 UNREAD
6 UNREAD
7 UNREAD
8 UNREAD
9 UNREAD

He gets articles: 7,8,9. He missed article 6!!

I can not figure out how to solve this, apparently, simple problem.

I have already tried to:

  • Decrement the counter: no way because I have more flags with more states (like favs.) and this behavior happens with all of them.
  • Use a timestamp and start from that timestamp: failed because items can have the same timestamp.

Thanks for your time.

AitorF
  • 1,360
  • 1
  • 9
  • 19

2 Answers2

0

Rather than querying based on WHERE UNREAD=1 LIMIT ?,3, use the current article ID. You want something like:

WHERE UNREAD=1
AND ID > ?
LIMIT 0,3

where ? is the last read ID. This way it doesn't matter if the status switches, it will just give the next 3 articles based on id.

This method would also make it possible to use the timestamp(even if two are equal), this way:

WHERE UNREAD = 1
AND timestamp >= (SELECT TIMESTAMP FROM tbl WHERE ID = ?)
AND ID > ?
Digital Chris
  • 6,177
  • 1
  • 20
  • 29
  • Using IDs is not the solution because in fact the order is not by ID but BY ARTICLE_DATE. I tried to use something similar to your proposal: "AND ARTICLE_DATE > ?", but there can be articles with the same date. As you say, I need to get rid of LIMITs and use some "FIELD > ?" but can not figure out what should be that field. It must be something related to DATEs as it's my order field, maybe a timestamp with miliseconds?? – AitorF Feb 04 '14 at 16:03
  • Did your read the second part of my solution? It doesn't matter if it's ordered by ID, you just use the ID to get the timestamp, then get all records with that timestamp or older (or is it newer? You never say the order). In either case, this will include articles with the same timestamp, while excluding the article just viewed. – Digital Chris Feb 04 '14 at 16:05
  • No sorry, I didn't refreshed the page. Anyway it wouldn't work either. Suppose every item has the same timestamp, the query will return already displayed items. – AitorF Feb 04 '14 at 16:20
  • Coud this help?? Unique timestamps for MySQL: http://stackoverflow.com/questions/10320667/can-i-get-a-unique-timestamp-for-every-record-in-mysql – AitorF Feb 04 '14 at 16:20
  • Your basic problem is you are trying to return a resultset and you apparently have no way to uniquely order that result set. My solution attempts to use timestamp + ID to uniquely identify one article in the order. I updated it to include the case you describe. – Digital Chris Feb 04 '14 at 16:24
0

You could also keep track of the number of documents that were flagged during the session and add count to your starting index of your query.

WHERE UNREAD = 1 LIMIT lastPosition + Flagged, 3

Alain
  • 109
  • 2