hello, when scrolling is scrolled down on my site, news is loaded, the offset parameter is passed to the query (the current number of news divs), and at the end of the query there is the DESC LIMIT {offset} 25 parameter, we skip the number of already loaded news, and the limit on the number selected news 25, but there is a problem in that while I scroll through the news of a particular user, if he adds a new news at this time, then the order of his news shifts, so if during upload he adds the news, then the order will move 1 forward and when I request have received the news shown earlier, ie, the offset comes from the 25th news, the person at this time added 2 news, and it turns out in the old 24 and 25 newsgroups that are already loaded 26 and 27 and loaded again, what can be done in this situation?

    1 answer 1

    Do not use offset. Why do you need offset? Not only is the problem described, it is also impossible to implement it quickly at large offset values. Because offset reads offset + limit of records from the sample only to throw out the first offset of records.

    When loading the next piece of data, pass the ID of the last one shown to the user. For example, if your entries are shown here with this sort:

    select ... from tablename where ... order by created_at, id limit 25

    So, to load the following list items, pass the created_at and id values ​​and request the continuation of the list starting right after this line:

     select ... from tablename where ... and created_at >= :lastrowdate and id > :lastrowid order by created_at, id limit 25
    • Thanks, but how do you advise to transfer the last received ID? is it normal to do this by creating a hidden input with the desired value, and transmitting it at the next data load - turik97
    • It all depends on how your front-end is implemented. Inputs, data-attributes, a ready link at once, variables in js are many ways. - Small
    • How do you think the normal way to specify $ var in the request and $ var to assign! empty ($ _ POST ['l_id'])? AND id <$ id: null for there is not always the last id of the uploaded news - turik97
    • Never substitute external data into control SQL. Building SQL dynamically and describing the parts that are really needed is correct and helps to clearly index the indexes. - Small