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

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

fn

I suppose to "materialize" the

post.userIds

post.userIds

array, but then the users query should be able to fairly efficiently do a lookup.

Is something like this possible already?

TanStack•7mo 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)
    )

  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

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

fn

I suppose to "materialize" the

post.userIds

post.userIds

array, but then the users query should be able to fairly efficiently do a lookup.

Is something like this possible already?

Joining on array column

Similar Threads

Joining on array column

Similar Threads

Similar Threads

Similar Threads