Thread: Is it possible to sort kobo collection by series?
View Single Post
Old 11-09-2016, 07:50 PM   #16
davidfor
Grand Sorcerer
davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.
 
Posts: 24,905
Karma: 47303824
Join Date: Jul 2011
Location: Sydney, Australia
Device: Kobo:Touch,Glo, AuraH2O, GloHD,AuraONE, ClaraHD, Libra H2O; tolinoepos
Quote:
Originally Posted by Stevex View Post
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.
Quote:
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".
Quote:
(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.
davidfor is offline   Reply With Quote