Query builder needs a `union` method to combine similar collections into one

[samwillis]

Union operator

We want a way to union two or more collections so that they can be used as a single source within a live query.

There needs to be both static and dynamic union operators, with the latter mutable.

Use cases:

• Sharding data across multiple collections for smaller sync
• Dynamically creating datasets from multiple sources - such as a feed reader with a collection for each feed and then a union of all of them for a "all" view.

Static union operator

q
  .from({
    source: union(collection1, collection2)
  })
  .where(({ source }) => gt(source.value, 1))

Implementation

This is the simplest to implement, it can map to a concat operator in the D2 graph. However, there is complexity if we want to use any of the indexes in the underlying collections. A first version could just de-optimise the query to use a full scan and then we can add optimizations later.

Dynamic union operator with manual mutation

Often a union may not be static, but something that you want to be able to add and remove sources from without having to restart the live query.

// Union returns a set like object
const sourcesUnion = union(collection1)

q
  .from({
    source: sourcesUnion
  })
  .where(({ source }) => gt(source.value, 1))

sources.add(collection2)

// do something

sources.remove(collection1)

Implementation

This is more complex to implement, we need to be able to mutate the set as the results of the live query change. We would need to maintain some sort of history of what has been sent in order to pass in the deletes when a source is removed.

Dynamic union operator constructed from a live query itself

An additional unionFromLiveQuery function that takes a live query, and a function that takes a results from that live query, and returns a collection. This wraps the union function, mutating the set as the results of the live query change.

const sourcesUnion = unionFromLiveQuery(
  (q) => q
    .from({
      rssFeed: rssFeedsCollection
    }),
  ({ rssFeed }) => createCollection(
    rssFeedsCollectionOptions({
      url: rssFeed.url
    })
  )
)

q
  .from({
    source: sourcesUnion
  })
  .where(({ source }) => gt(source.value, 1))

Implementation

This is a little simpler to implement as its really just a wrapper around the union function with mutations driven from the live query.

[pasha-omni]

A possible workaround is to perform fullJoin() on an always-false condition, with subsequent coalesce() in the select clause.

The union() function would be, of course, more performant and convenient syntax-wise, so let's hope it gets implemented sooner rather than later.

[samwillis]

I've updated the description with a proposed design.

[mhsnook]

Hi I have a use case where I am trying to populate my local db using a number of different react-query collections so that different routes in my app can "subscribe" to different slices of the database, some based on the category of the items, some based on specific item IDs, some based on the user who authored the item. So then I would have different queries with query keys like this:

• ['public', 'phrase', 'lang', language_name]
• ['public', 'phrase', 'id', phrase_id]
• ['public', 'phrase', 'added_by', user_id]

And what I would really like to be able to do is to have a live collection that is defined by the queryKey ['public', 'phrase'], which would include all the other queries under it, (just like we do with invalidateQueries). So then I would just define my phrasesCollection with this "parent" key and not include a queryFn, and in other places in my app I would use useQuery or ensureQueryData or whatever I want from react-query to make sure these different caches get populated, but the phrasesCollection would always include all the items that are present at the time. And the liveQueries wouldn't have to care about staletimes or garbage collection because I can already set those things in the useQuery or ensureQueryData, and maybe my one-phrase-at-a-time caching could have short short staleTimes and refetch on window focus and such, but when i load up an entire language at a time I might set something MUCH longer like a 2 hours staletime. But since all my items have the same Schema and Key, defined in the collection, we can dedupe them based on fetch time, which react-query is already tracking.

This fullJoin() approach seems nice-ish but it will require me to define and then keep track of all the different collections (dynamically, using hooks rather than createLiveCollection) and then grab them and then like reduce that array of collection keys into a big chain of many many fullJoin's... I just think it sounds kind of miserable, but if you let me do it with the queryKey, then I can use useQuery or ensureQueryData the way I normally would, and then just grab and shape the data using these useLiveQuery's... well that would just be gorgeous.

(This approach would also seem to solve a problem that the electric-SQL useShape approach has, which is that manually subscribing to and then aborting subscriptions means that if two components subscribe to the data and one of them aborts the subscription, the other one loses it. useQuery subscriptions don't have this problem.)

[mhsnook]

Just following on the above comment with some sample code for how I'd use this: firstly, my collection would be defined just like this (same as present, but no queryFn)

// lib/collections.ts
export const phrasesCollection = createCollection(
	queryCollectionOptions({
		queryKey: ['public', 'phrase_full'],
		getKey: (item: PhraseFullType) => item.id,
		schema: PhraseFullSchema,
		queryClient,
	})
)

Load up an entire language at once:

// routes/learn/$lang.tsx
loader: async ({ params, context: { queryClient } }) => {
	await queryClient.ensureQueryData({
		queryKey: ['public', 'phrases', 'lang', params.lang],
		queryFn: async () => await supabase.from('phrase').select().eq('lang', params.lang),
		staleTime: 1000 * 60 * 60 * 2, // 2 hrs 
	})
}

Load up one phrase:

// routes/learn/$lang/$phrase_id.tsx
loader: async ({ params, context: { queryClient } }) => {
	// not awaiting this because if the collection already has a copy of this phrase, the useLiveQuery
	// will work fine, and this prefetch will simply mean a revalidation of this one specific phrase.
	queryClient.prefetchQuery({
		queryKey: ['public', 'phrases', 'phrase_id', params.phrase_id],
		queryFn: async () => await supabase.from('phrase').select().eq('id', params.phrase_id),
		staleTime: 1000 * 60, // 1 min
	})
}

// routes/friends/$uid.tsx
loader: async ({ params, context: { queryClient } }) => {
	await queryClient.ensureQueryData({
		queryKey: ['public', 'phrases', 'uid', params.uid],
		queryFn: async () => await supabase.from('phrase').select().eq('added_by', params.uid),
	})
}

There are other edges that the developer will have to look out for, like say I have reason to believe my liveQuery will return partial results because I've loaded up the English flash cards but now I'm looking at my friend's contributions and she has also contributed flash cards in other languages -- it's going to be my job to see if the query is still pending and to adjust accordingly, but a useLiveQuery will still allow me to show partial results, like this:

function FriendProfilePage() {
	const params = useParams()
	const query = useLiveQuery(myFriendQuery(params.uid))
	const loader = useQuery(myFriendLoader(params.uid))
	// query.isLoading is now longer useful because there's no queryFn
	if (loader.isPending && !query.data) return <Loader />
	return (
		{loader.isPending && <p>Showing partial results while the rest load...</p>}
		{!query.data?.length && <NoResults />}
		{query.data.map(p => <PhrasesListItem phrase={p} />)}
	)
}

... So there is a little bit of cognitive complexity overhead here, but a) I think less than with the other solution! and b) this is a type of complexity only introduced by the developer's own decisions to load more and more granular data; it's not inherent to this model, and the queryClient gives us the tools we need to keep track of what's loading and what's settled, and -- crucially -- to trigger refetches of just specific slices of the collection in a granular fashion without clobbering the rest.

Final point: It also means we could set up realtime connections for one slice of the database and manually update entries under one cache-key without messing with the rest of having those items clobbered by a refetch of another query.

I think the part of this that's the least thought-out is just how to handle inserts -- do you define which cache-key it'll go under as part of the insert or writeInsert function? I think that would be nice, but I have thought less about your onInsert functions and insert API design.

[JustinGrote]

@mhsnook I could see a big problem here with types in Typescript, unless all the methods to do it were made generic to constrain the types, it'd be really difficult to type-check your collections based on just the key. Probably would be fine for vanilla JS tho. InvalidateQueries doesn't return anything so it's not a concern there.

[guhur]

A possible workaround is to perform fullJoin() on an always-false condition, with subsequent coalesce() in the select clause.

The union() function would be, of course, more performant and convenient syntax-wise, so let's hope it gets implemented sooner rather than later.

How would you perform that?

  const unifiedCollection = createCollection(liveQueryCollectionOptions({
    query: (q) => q
      .from({ quote: quotesUnifiedCollection })
      .fullJoin({ invoice: invoiceUnifiedCollection }, () => false)
[...]

And it returns: Join condition must be an equality expression

EDIT: well.. one way to do that:

      .fullJoin({ invoice: invoiceUnifiedCollection }, ({
        quote, invoice }) => eq(quote.type, invoice.type))

Outch, what a cryptic way to do it!

[JustinGrote]

As a workaround, using GetQueryData to merge similar queries into a single collection worked well for me.

	const collection = useMemo(
		() =>
			createCollection(
				queryCollectionOptions({
					queryKey,
					queryFn: async () => {
						const latestResults = queries.map(query => queryClient.getQueryData<RoleSchedule[]>(query.queryKey) ?? [])
						return latestResults.flat()
					},
					refetchInterval,
					queryClient,
					getKey: role => role.id,
				})
			),
		[accountsHash, queryClient, queryKey, queries]
	)

[mhsnook]

As a workaround, using GetQueryData to merge similar queries into a single collection worked well for me.

@JustinGrote -- where are you getting this array queries?

Actually as I was looking into this I found getQueriesData -- which accepts a QueryFilter as its argument and returns an array of tuples -- a query key and query data. So I guess what I want is just a createCollection(queriesCollectionOptions({ ... }) which would similarly accept a query filter and would load all the results up into little collections and then make a union collection out of them, (loading newest cache results last) for use in my live collections 🙏

But even knowing about getQueriesData... I guess it makes it possible for me to do kind of what you're doing... and load things into query caches... and then "refetch" the collection after the new query cache is populated... that could be good! But it will fully duplicate my cache, almost doubling the size for these collections. And I don't have an immediate way of knowing which ones are newer... but I think I can get it with queryCache.find(queryKey) -- okay thanks for the idea!

(Note: getQueriesData has the same issue with types.)

Another note (sorry I have many thoughts on this) -- since this is a dependency-injection issue, it does make me think of wanting to use the router and routeTree.gen.ts to give us typed db-loading assurances from simple ensureQueryData-style dependency declarations... imagine a function like the loader but it returns an array of tuples which are each a promise or a TQueryFnData, and it awaits and loads everything so I know my react components will have access to the declared data dependencies by the first render 🤤🤤🤤 and with the garbage-collection and cache management of the query cache 🙏🙏🙏 dreamy.

[JustinGrote]

@mhsnook I used useQueries and fed it query configs to generate the queries. In my case, it's separate queries to Azure PIM for both multiple accounts and multiple types of elevatable roles (Entra, AD, Group), all normalized to a standard object type.

Here's the source (WIP so may change but this permalink should stay evergreen): https://github.com/JustinGrote/PIMFast/blob/8f3f4c3fa3fef42758b4c9465a9f6934656a7219/src/db/EligibleRole.db.ts

This is working great for me, all fetching in parallel, and hooking the collection change events to ag grid's transaction system to cleanly CRUD rather than re-render all rows each time.

[mhsnook]

Thank you @JustinGrote -- I see an RFC for loadSubset which seems like it will address this nicely. If you have something working for your use-case, it might be useful feedback on the RFC to share over there 🚀