Query on Join Fields

[daohall]

Hi, is there any way to query a collection by filtering on the join field?
For example, I have a bi-directional relationship for category and posts

export const MyJoinField: Field = {
  name: 'relatedPosts',
  type: 'join',
  collection: 'posts',
  on: 'category',
}

// relationship field in another collection:
export const MyRelationshipField: Field = {
  name: 'category',
  type: 'relationship',
  relationTo: 'categories',
}

How do I query for categories that the "relatedPosts" is not empty?
I tried something like

const categoriesWithPosts = await payload.find({
  collection: 'categories',
  depth: 1,
  limit,
  where: { relatedPosts: { exists: true } },
});

but this is giving error Error: syntax error at or near "not". None of other filters like equals, in etc works.

If I try with filters like "relatedPosts.doc", it is giving the error Error: The following path cannot be queried: relatedPosts.docs

[DanRibbens]

The join field doesn't support querying yet. We should mention this in the docs until this can be added. For now you will need to first query the related collection and use the relationship IDs from the first query like this:

const posts = await payload.find({
  collection: 'posts',
  depth: 0,
  limit: 0,
  paginaction: false
  where: {
    category: { exists: true }
  },
  select: { category: true }
})

const categoriesHavingPosts = await payload.find({
  collection: 'categories',
  where: {
    id: { in: posts.map(({ category }) => category ) }
  }
})

I want to be able to support this in one request in the future.

[daohall]

I see. Got it. Thanks for the clarification

[matthijs166]

Plus one for this feature!

Got also this case for access control.

Suppose we got students assigned to classrooms.
For a certain type of user, they only need to see and edit the rooms that are empty with no students.

Now we need to do a big fetch of every student before even querying the rooms..

[SimonVreman]

This seems to be quite a limiting issue for the join field. For example, the following use case should be a common one, but the (to me) obvious approach does not work.

Situation
We have three collections, Users, Groups and a relation collection GroupMembers. This last collection has a relation field to both Groups and Users. These in turn have join fields to the members collection.

Problem
For a user, we want to show an overview of the groups they are members of. We query the Groups collection for this. We write the following access control function:

export const groupMember: Access<Group> = async ({ req: { user } }) => {
  if (!user) return false
  return { 'members.user': { equals: user.id } }
}

Where members is the join field to the GroupMembers collection. This throws some vague drizzle errors, but if I interpret this discussion correctly, it's simply not supported.

Horrible workaround
Best I could figure out is introducing an N+1 query, which is frankly terrible and not a realistic option for a production environment.

export const groupMember: Access<Group> = async ({ req: { user, payload } }) => {
  if (!user) return false

  const groupIds = await payload.find({
    collection: 'group-members',
    select: { group: true },
    where: { user: { equals: user.id } },
    limit: 100,
    depth: 0,
  })

  return { id: { in: groupIds.docs.map(({ group }) => group) } }
}

Result could (should! for the reader thinking about using this...) be set to context of course to make it less painful, but it's not what I'd like to see.

@DanRibbens is it correct that this is indeed not supported, and am I correct that it cannot be avoided when using a join field here?

[matthijs166]

Also, group-member 101 wouldn't show here because of the limit and in the /api/access all ids are exposed.

Not really a scalable good option...

[SimonVreman]

Yeah not scalable for sure. Id's being exposed shouldn't be an issue, right? They are user specific, so it's just your own memberships.

[matthijs166]

Not the best practise to expose database ID's like this. Its unexpected behaviour.

[SimonVreman]

Suppose it depends. I can see why you think so. In my case, you can query your memberships anyways so it doesn't matter. Though I still don't want to use this 'fix' :,)

[r1tsuu]

This was implemented here #12075 and released in v3.34.0!

[matthijs166]

Awesome!! Will try it this weekend, thx!