TanStackT
TanStack•5mo ago•
5 replies
broad-emerald

Joining on array column

in this example posts can have multiple authors which are saved in an array field in the database
  q
    .from({ post: postsCollection })
    .join({ user: usersCollection }, ({ post, user }) => 
      inArray(user.id, post.userIds)
    )

The above example does not work because join only supports eq function; but I also do not see any other option in the functional variants. I would imagine something like this could work;
q.from({ post: postCollection }).fn.select((postRow) => ({
  id: postRow.id,
  users: q.from({ user: usersCollection }).where((userRow) => {
    return inArray(userRow.id, postRow.userIds);
  }),
}));

The first part would need to use the fn I suppose to "materialize" the post.userIds array, but then the users query should be able to fairly efficiently do a lookup.

Is something like this possible already?
Was this page helpful?