Pagination

[simonw]

Useful for #16 (timeline view) since you can now filter to just the items on a specific day - but if there are more than 50 items you can't see them all.

[simonw]

Should I do this with offset/limit or should I do proper keyset pagination?

I think keyset because then it will work well for the full search interface with no filters or search string.

[simonw]

It feels a bit weird to implement keyset pagination against results sorted by rank because the ranks could change substantially if the search index gets updated while the user is paginating.

I may just ignore that though. If you want reliable pagination you can get it by sorting by date. Maybe it doesn't even make sense to offer pagination if you sort by relevance?

[simonw]

I'm going to try for keyset pagination sorted by relevance just as a learning exercise.

[simonw]

It's a shame Datasette doesn't currently have an easy way to implement sorted-by-rank keyset-paginated using a TableView or QueryView. I'll have to do this using the custom SQL query constructed in the plugin:

dogsheep-beta/dogsheep_beta/__init__.py

Lines 8 to 43 in bed9df2

	TIMELINE_SQL = """
	select
	search_index.rowid,
	search_index.type,
	search_index.key,
	search_index.title,
	search_index.category,
	search_index.timestamp,
	search_index.search_1
	from
	search_index
	{where}
	{where_clauses}
	order by
	{order_by}
	limit 40
	"""
	SEARCH_SQL = """
	select
	search_index_fts.rank,
	search_index.rowid,
	search_index.type,
	search_index.key,
	search_index.title,
	search_index.category,
	search_index.timestamp,
	search_index.search_1
	from
	search_index join search_index_fts on search_index.rowid = search_index_fts.rowid
	{where}
	{where_clauses}
	order by
	{order_by}
	limit 100
	"""

[simonw]

Datasette's implementation is complex because it has to support compound primary keys: https://github.com/simonw/datasette/blob/a258339a935d8d29a95940ef1db01e98bb85ae63/datasette/utils/__init__.py#L88-L114 - but that's not something that's needed for dogsheep-beta.

[simonw]

If previous page ended at 2018-02-11T16:32:53+00:00:

select
  search_index.rowid,
  search_index.type,
  search_index.key,
  search_index.title,
  search_index.category,
  search_index.timestamp,
  search_index.search_1
from
  search_index
 where 
  date("timestamp") = '2018-02-11'
 and timestamp < '2018-02-11T16:32:53+00:00'
order by
  search_index.timestamp desc, rowid
limit 41

[simonw]

Actually for the tie-breaker it should be something like https://latest.datasette.io/fixtures?sql=select+pk%2C+created%2C+planet_int%2C+on_earth%2C+state%2C+city_id%2C+neighborhood%2C+tags%2C+complex_array%2C+distinct_some_null+from+facetable+where+%28created+%3E+%3Ap1+or+%28created+%3D+%3Ap1+and+%28%28pk+%3E+%3Ap0%29%29%29%29+order+by+created%2C+pk+limit+11&p0=10&p1=2019-01-16+08%3A00%3A00

where
  (
    created > :p1
    or (
      created = :p1
      and ((pk > :p0))
    )
  )

But with rowid and timestamp in place of pk and created.