T
TanStack•2mo ago
other-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)
    )
  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?
3 Replies
genetic-orange
genetic-orange•2mo ago
could you file an issue?
conventional-tan
conventional-tan•2d ago
@mees - did you log a ticket? Did you manage to solve this?
other-emerald
other-emeraldOP•19h ago
I didn't. Isn't it already covered with https://github.com/TanStack/db/issues/288 though? e.g. 
const postsByAuthor = new Query
  .from({ post: postsCollection })
  .select(({ post }) => ({
    post,
    authors: new Query
      .from({ user: usersCollection })
      .where(({ user }) => 
        inArray(user.id, post.authorIds)
      )
  }))
const postsByAuthor = new Query
  .from({ post: postsCollection })
  .select(({ post }) => ({
    post,
    authors: new Query
      .from({ user: usersCollection })
      .where(({ user }) => 
        inArray(user.id, post.authorIds)
      )
  }))
And I didn't solve it. I am now just looping over js arrays without tanstack db. Perhaps there are use cases for the join in array still

Did you find this page helpful?