Order items by date rather than id

[BernhardPosselt]

This will be harder to implement and will take longer, so next release ;)

Each item has a pubDate which we get as unix timestamp. The problem comes with autopaging. The current algorithm is basically the following:

• Get the 20 items with the highest id
• If scrolled close to the bottom, get the lowest id from the previously loaded items
• Send that to the server and request 20 items with an id lower than that one, order by id descending
• etc.

A timestamp can occur multiple times. If we use the pubdate for querying the following algorithm would be imaginable:

• Get the 20 items with the highest pubdate
• If scrolled close to the bottom, get the lowest pubdate from the previously loaded items
• Send that to the server and request 20 items with an pubdate lower than that one, order by pubdate descending

Now what if all items have the same pubdate and there are 400 of them? the algorithm wouldnt load items at all. If you use <= it will load 400 items at once and that kinda breaks efficient autopaging.
Another problem would occur if there are newly added items which have a lower pubdate than on the client. It may even skip items.

[BernhardPosselt]

K, found the solution, I'll see if i can implement the stuff ASAP over the weekend (since this also affects the API). I want to push back the beta release to implement this cc @cosenal

PS: API draft has already been updated

[IBBoard]

Is anyone else having problems with this still? I've just installed News on OC6.0.1 and I appear to be running a version that has this fix in (I can see "NewestItem" in the JS) but as you can see in the screenshot then folders are showing mixed dates. Prodding the database, it is obviously still ordering by ID, not date.

Couldn't you just order by pub_date and then by ID and then do the LIMIT in the SQL? That'd seem to deal with the 400 items case, because ID would be the distinguishing order factor only when you couldn't do it by pub_date, and it'd be constant so you wouldn't get random shuffles.

[BernhardPosselt]

The problem arises from articles which are added and have a pubdate which does not comply with the date added. You wouldnt get to see those articles at the top but rather at the bottom. So basically the ordering right now is not based on pubdate but based on how new the entry is which is the proper way to do it imo

[BernhardPosselt]

Basically its best to not trust pubdate input at all since theres so much fked up stuff out there. It may still interest you when the author thought the article should be published

[IBBoard]

If an email comes in with a "bad" sent date then either Thunderbird makes an invalid interpretation of invalid data (e.g. 15/12/13 is 15th Dec last year in UK, but 13th Dec next year in ISO and invalid in US) and it lands at a best-guess place in your timeline (which is all that can be expected), or it has no date at all and it treats it as the start of the Epoch (which is fair enough, if you're going to ignore the standards, but not ideal), or "bad" just means "sender clock was out by days/weeks/months" and it appears where it is supposed to - chronologically at the point it was sent.
If a post has no pub_date at all then "now" would seem like the most accurate approximation. It'd give broken feeds a behaviour no worse than the current behaviour, and it'd give properly constructed feeds better behaviour.
As it is now then any feeds with infrequent posts that are added to a folder later than the previous feeds suddenly dump a bunch of posts out of sync, because the IDs are all higher, even if the posts go back a few months and the last post from the existing lot were a few minutes ago.

[BernhardPosselt]

implemented

[BernhardPosselt]

Ok, will probably revert this and go back to ordering by id. The problem is the following:

• If you order by pubdate and id the order is defined
• You autopage by offset
• If you autopage unread items by offset, the offset breaks because youre marking items as read while scrolling which in turn sends a higher offset than you expect

If you dont autopage by offset:

• Order by pubdate and id and the order is defined
• Pubdate is not unique
• What happens if 50 entries have the same pubdate but different id? I dont know a way in sql to skip ahead to entry with id x if the first 50 entries have the same pubdate

[BernhardPosselt]

basically i need something like:

SELECT * FROM items 
WHERE pubdate < ?
SKIP TO id = ? IF FIRST ROWS SAME pubdate

which works at least on postgres and mysql

[BernhardPosselt]

Closing this because there is no solution

[Xefir]

Maybe you can do it in the template part (with angular in JS) ?

In templates/part.items.php, something like this :

<ul>
    <li class="feed_item"

        ng-repeat="item in itemBusinessLayer.getAll() | orderBy:['-pubDate'] "

I use this modification in my ownCloud at the moment and it works great.
I don't investigate further on the other hand.

[BernhardPosselt]

Nope, autopaging will insert the elements in the wrong order then since ordering is defined by id on the server

[BernhardPosselt]

So items will be inserted before your current point and will be marked as read automatically