Please tell me there’s some way to do this:
I have (say) an order document with a foreign key to a customer. I need to get page worth’s of customers from the orders. I have indexing in place to quickly retrieve customer ids given the ‘order’ predicates.
I need to return the customers ordered by a field on the ‘order’
the problem is that I need to filter by a field or two on the ‘customer’.
So I can’t do a join because that will fetch all the customers in the db before the filtering.
So I’m looking to use intersect like this:
Select raw customerId from (Select customerId from default where type = ‘order’ order by createDate desc)
Select raw customerId from default where type = ‘customer’ and predicate 1…n (using covering indexes)
then I could apply my offset + limit + join to the customer doc.
Unfortunately it does not look like ordering is preserved upon exiting the intersect/except
And I don’t think I can include the ordering field in the projection for the first query (because then Intersect wont work)