Is it possible to sort kobo collection by series?

[davidfor]

Originally Posted by Stevex

If anyone is still interested I posted this before in Series Order thread but again here.

If you don't mind to lose the "recently read" sort order (which I have personally exactly zero use for)

Just to be clear, there isn't a "recently read" sort order. The "Recent" order is based on the last read date AND the added to the device date. From testing ages ago, if the last read date is null, then it uses the added date. But, it might have changed to use the maximum of these two dates.

Personally, the "Recent" order is the one I use most. If I am looking for a book to read, I use the "Unread" filter with "Recent" sort as that shows the books I have added most recently. Then I scroll until something catches my eye. And "Recent" works best when I am looking through books I have started but for some reason stopped reading for a while or when I open books for testing something and push the real current read off the home screen.

you can use this for series order instead by adding the following stored procedures.

The pedant in me has to point out that these are "triggers" not "stored procedures".

(The reason for *10 is that I have a few 1.5, 1.7 and suchlike, if you have 1.66 you can always make times and divide by 100 or whatever)

I have to say, it's actually brilliant to see my collections come up spontaneously in the right order without having to mangle titles and suchlike.

One caveat: If you read a book, this will of course become out of order till you restart the machine or similar.

CREATE TRIGGER Activity_Reset_Collection_1 AFTER INSERT ON Activity BEGIN UPDATE Content SET DateLastRead = date((julianday('Now')*10-SeriesNumber*10)/10) WHERE SeriesNumber not null and ContentType=6; END

CREATE TRIGGER Activity_Reset_Collection_2 AFTER UPDATE ON Activity BEGIN UPDATE Content SET DateLastRead = date((julianday('Now')*10-SeriesNumber*10)/10) WHERE SeriesNumber not null and ContentType=6; END

Wow, that seems like over kill. Every single time a tile is changed on the home screen, you are updating the last date read for all books that have a series number. For me, that's currently 1091 books. Why don't you put the triggers on the content table? You can choose some constant initial date for the calculation and redo the calculation just for the book that is being updated. And even if you trigger it from the Activity table, you will have the content id of the book being updated, so should be able to do the update just for that book.

Changing the date read like this will probably cause problems for syncing any purchased books. I'm guessing you don't do that, but it is a caution for anyone who does.

[Stevex]

(1) arrgh you are right it's a trigger not an sp embarrassing
(2) I also have 000's of books on the device but it doesn't have any noticeable performance effect whatever*. I know what you mean about overkill but I just wanted to be sure it runs and the tile thing is guaranteed updated.
(3) I can think of quite a few improvements, and in fact last night I just did one last night after I posted because it was in my head, but it's just not worth it to beautify it much the thing is simple took me about a minute to do and works
(4) you are right I don't have any purchased books only sideloads but I don't -think- pushing dateread into the past (which is all this does) would impact anything?

* PS: To me, on my aura glo hd, in my use case, caveat caveat etc etc ;-)

PPS It's an easy thing to try, it's brilliant for me, and for anyone else who wants to try it it's at the worst a bit of fun really as long as you back the db up first

[davidfor]

Originally Posted by Stevex

(1) arrgh you are right it's a trigger not an sp embarrassing
(2) I also have 000's of books on the device but it doesn't have any noticeable performance effect whatever*. I know what you mean about overkill but I just wanted to be sure it runs and the tile thing is guaranteed updated.

If it performing OK, then good. But, updating a lot of extra rows when not needed is something I really try to avoid.

(3) I can think of quite a few improvements, and in fact last night I just did one last night after I posted because it was in my head, but it's just not worth it to beautify it much the thing is simple took me about a minute to do and works
(4) you are right I don't have any purchased books only sideloads but I don't -think- pushing dateread into the past (which is all this does) would impact anything?

I didn't think it through yesterday, it just gave me an uncomfortable feeling.

The main affect will be for people using multiple devices or the apps. When you open a purchased book, it contacts the server to get the latest reading position. If you have been reading on another device, you get a prompt to ask if you want to go to the new position. The check is based on the timestamp, not the actual position in the book. And if a sync is done, the position from the later timestamp is taken without any prompts.

And I think it will mean a sync takes a lot longer. Every purchased book with a series number will trigger a change when they haven't really changed.

And of course, for anyone who is using my Kobo Utilities to store the reading status, there are going to be some consequences.

* PS: To me, on my aura glo hd, in my use case, caveat caveat etc etc ;-)

PPS It's an easy thing to try, it's brilliant for me, and for anyone else who wants to try it it's at the worst a bit of fun really as long as you back the db up first

Yes, everyone, back up your databases before messing with them.