T
TanStack•3d ago
plain-purple

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)
    )
  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);
  }),
}));
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?
1 Reply
conscious-sapphire
conscious-sapphire•3d ago
could you file an issue?

Did you find this page helpful?