Is there any way to query the posts that user not-seen yet without using raw SQL ?

[anhdd-kuro]

Let's say I have millions of posts and a feature to filter only posts not seen yet.

So directly using relationship fields in the Users collection does not seem efficient.

So I created a separate collection for that and then use it as a join field in user. It's easy to select posts that the user has seen by using

where: { "seens.user": { equals: userId } }

but I struggle to get the reverse.

With raw SQL, I can use an anti-join to get it, but the system is multi-tenant, custom access logic and I also have custom publishing logic (for example, publishing posts only for a group of users). So it's hard to maintain raw SQL in the long way when it combines too many pieces of logic without owning the database structure (I mean, the database schema is auto-generated by Payload, columns name and structure can be changed depend on the settings ).

Getting all seen posts and then excluding their IDs doesn't seem efficient either in term of memories...

So, is raw SQL the only choice here?