Seeking advice for Pagination + liveQuery / reactive

[dellagustin]

Hello All,

I have been refactoring an application of mine to use Dexie and I am making use of liveQuery to update the content the user is seeing when the database is updated (e.g. different tabs open, background actions, etc...).

It is a podcast aggregator, so I have implemented paging with offset/limit due to big number of episodes. I load new pages on an "infinite scrolling" manner.

I'd like to have the lists of episodes (for a given podcast, for all episodes or all in progress) to also be reactive and I don't think it is a good idea to simply have a liveQuery with offset 0 and limit to all visible episodes (up to thousands when combining all podcasts), as it may be a big result set, depending on how much the user has scrolled.

I'm considering storing a "timestamp" to the episode's last update and indexing by it, then having a liveQuery that uses this timestamp to keep feeding me with recently updated entries (still maturing this idea).

Is there a simple way for just reacting to any changes to a table? This would be another option (a simpler one), I would then just re-run my queries when some change affects the episodes table.

Any other ideas are welcome.

[dellagustin]

Hello @dfahlander, not sure you saw this question, but I'm pinging you just in case you have any advice for a best practice on this situation.

[dfahlander]

Sorry for missing this out and thanks for the reminder!

To only observe any changes to a certain table, the not-yet-documented event storagemutated can be observed as such:

// Subscribe globally:
Dexie.on.storagemutated.subscribe(onStorageMutated);
// Unsubscribe globally:
Dexie.on.storagemutated.unsubscribe(onStorageMutated);

function onStorageMutated(parts) {
  if (Object.keys(parts).some(part => part === 'all' || part.startsWith(`idb://${dbName}/${tableName}/`))) {
    console.log(`Something changed in database ${dbName}, table ${tableName}`);
  }
}

However, an infinite scroller can also be achieved using liveQuery(). Don't know which framework you are using, but I could examplify it using React and react-infinite-scroll-component.

DB declaration (simplified)

// db declaration
const db = new Dexie('podcastDB') as Dexie & {
  podcasts: Table<PodCast, string>;
  episodes: Table<PodCastEpisode, string>;
};
db.version(1).stores({
  podcasts: 'id',
  episodes: 'id, [podcastId+episodeNumber]' // Assuming that episodes are connected to a podcast and ordered episodeNumber.
    // Could equaly well be [podcastId+name] if they are ordered by name.
});

Infinite scroll component

// Infinite scrolling EpisodeList component
import { db } from '../db';
import InfiniteScroll from 'react-infinite-scroll-component';
import { liveQuery, Observable } from 'dexie';

interface Props {
  podcastId: string;
  pageSize: number
}

// The component:
export function EpisodeList ({podcastId, pageSize}: Props) {
  const [limit, setLimit] = useState(pageSize);
  const episodes = useLiveQuery(
    () => db.episodes
      .where('[podcastId+episodeNumber]'
      .between([podcastId, 0], [podcastId, Infinity]) // podcastId=podcastId orderBy episodeNumber
      .limit(limit)
      .toArray(),
      [podcastId, limit]
    );

  const fetchMoreData = () => {
    setLimit(currentLimit => currentLimit + pageSize);
  };

  if (!episodes) return <h4>Loading...</h4>; // Loading initial page

  // Use InfiniteScroll to help out with infinite scrolling...
  return (
    <InfiniteScroll
      dataLength={episodes.length}
      next={fetchMoreData}
      hasMore={resultArrays.at(-1)?.length === pageSize}
      loader={<h4>Loading...</h4>}
    >
      {episodes.map((episode) => (
        <Episode key={episode.id} episode={episode} />
      )}
  </InfiniteScroll>;
}

The above component would be the perfect solution for inital pages but in dexie@3 and 4, it would slow down as the user continues to scroll down. In Dexie 5 (still on the drawing board), we will optimize limit() queries using the cache that we introduced in dexie@4 so that it will be able to reuse previous results from the cache under the hood so the 100th page will load equally fast as the first one.

I'll give a version that would be optimized for dexie@4 below (but it is a bit more verbose, and it would not be as fast as the above one with dexie@5):

// Infinite scrolling EpisodeList component
import { db } from '../db';
import InfiniteScroll from 'react-infinite-scroll-component';
import { liveQuery, Observable } from 'dexie';

interface Props {
  podcastId: string;
  pageSize: number
}

// The component:
export function EpisodeList ({podcastId, pageSize}: Props) {
  // Function to create a live query for a certain page
  const createLiveQuery = (pageNo: number) => liveQuery(
    () => db.episodes
      .where('[podcastId+episodeNumber]'
      .between([podcastId, 0], [podcastId, Infinity]) // podcastId=podcastId orderBy episodeNumber
      .offset(pageNo * pageSize) // Fetch given page
      .limit(pageSize)
      .toArray()
  );

  // Current ongoing queries (one per "page")
  const [liveQueries, setLiveQueries] = useState(() => createLiveQuery(0));
  // Current set of result sets (one result set per page)
  const [resultArrays, setResultArrays] = useState<PodCastEpisode[][]>([]);

  // Emulate useLiveQuery() with a useEffect that subscribes to vanilla liveQuery()
  // Reason: React wouldn't allow a dynamic number of hooks so we need to punch it into a single effect
  // and do a similar thing to what is done in the source of useLiveQuery():
  useEffect(() => {
    const subscriptions = liveQueries.map((q, i) => q.subscribe(
      results => setResultArrays(resultArrays => {
        const arrayClone = [...resultArrays];
        arrayClone[i] = results;
        return arrayClone;
      })
    ));
    return () => {
      // Unsubscribe all queries. Note: Dexie 4.x has a cache and will be fast to resubscribe the same query.
      for (const s of subscriptions) {
        s.unsubscribe();
      }
    };
  }, [liveQueries, podcastId, pageSize]);

  const fetchMoreData = () => {
    // Append another liveQuery now that user has scrolled to the bottom:
    const nextPageNo = liveQueries.length;
    setLiveQueries(currentLiveQueries => [...currentLiveQueries, createLiveQuery(nextPageNo)])
  };

  const episodes = resultArrays.flat(1); // flatten the set of result sets

  // Use InfinteScroll to help out with infinite scrolling...
  return (
    <InfiniteScroll
      dataLength={episodes.length}
      next={fetchMoreData}
      hasMore={resultArrays.at(-1)?.length === pageSize}
      loader={<h4>Loading...</h4>}
    >
      {episodes.map((episode) => (
        <Episode key={episode.id} episode={episode} />
      )}
  </InfiniteScroll>;
}

NOTE: All this is dry coded. Please reply if finding bugs in these snippets

[dellagustin]

wow, I have not expected such a detailed answer, thank you very for your time.
Believe it or not, I'm still using AngularJS, it is an old project.
I was considering an approach similar to your example optimized for dexie@4, but now I'm thinking about starting with the non-optimized version and do some tests to see if it is worth optimizing.

[dfahlander]

Sounds great! Hopefully it's good enough and when there's a beta with this feature out you should see it behave better.

[dellagustin]

So, I tried it without the optimization, but that had a noticeable degradation on the performance after a few pages, so I implemented the optimized approach, creating separate subscriptions for each page.
That worked pretty well so far. It might hang a bit if a new episode comes, as that will change every page at the same time, but that's an exceptional case.
Here is the code: https://github.com/podStation/podStation/blob/530addcfc1ac5b4c248725ad7c7a810af8315b23/src/ui/ng/controllers/episodesController.ts
Note that I have hidden the queries behind an engine. That part is still very rough.
Thanks again for your help!

[dfahlander]

I see! The implementation in dexie@5 would actually not use offset but instead pick the last pubDate and change the next query from orderBy('pubDate') to where('pubDate').below(prevLowestPubDate) (actually it uses belowOrEqual and then iterates until it come by the last primary key, but I suppose you wouldn't have two episodes with exact same pubDate anyway (since it represents millisecond level).

If you could do this, instead of offset(), only the topmost query would update when a new episode arrives.

[dellagustin]

but I suppose you wouldn't have two episodes with exact same pubDate anyway (since it represents millisecond level).

I don't expect that to happen, but the episodes list comes from 3rd party data (RSS feeds), I cannot rule that out.

Anyway, I will keep the offset/limit for now and leave further optimizations for later.
Thanks again for your comments.