This post is deprecated in favor of a plugin I wrote which does the exact same thing but in a much less annoying way.

---

I read a lot, and a common problem I have is keeping track of the series I’m currently reading. For a while I’ve toyed with the idea of writing a Calibre plugin that would allow me to see books that belong to a series which I’ve started but haven’t caught up with (I rate books as I finish them, but parsing my library at a glance to see what comes next is difficult). Today I found the Multi-Column Search plugin and it turns out to have exactly what I need to solve this problem: a freeform SQLite field.

select id from (
  select *,
    row_number() over(
      partition by series order by series_index asc
    ) row_num
  from (
    select
      b.id, bsl.series, b.series_index, r.rating
    from books b
    inner join books_series_link bsl on b.id = bsl.book
    left join books_ratings_link brl on b.id = brl.book
    left join ratings r on brl.rating = r.id
  ) where rating is null
)
where row_num = 1 and series_index > 1

This script will filter the library to include the next unread (unrated) book in your library from each series that has at least one book that has been rated. It’s a little uglier than I would have liked, since I had to use one long query instead of CTEs, and I would have liked to be able to return them in order (maybe by overall average score), but due to limitations in the plugin this is as good as it’s going to get. Even so, I can tell it’s going to be super useful.