Error on filtering media within folders

[WilcoJump]

Describe the Bug

In our application, we've linked user groups to both media and folders. If we try to filter media by user groups within a folder, we get the error below via the API. In the collection in the CMS admin, we simply don't get a result. The error below indicates that an ID is apparently empty somewhere. Is this something we can fix ourselves, or is it a payload error?

This likely has to do with the fact that both media and folders have a multi-user relationship with user groups (media and folders can have multiple user groups).

{ "errors": [ { "message": "Failed query: select distinct "media"."id", "media"."created_at", "media"."created_at" from "media" left join "media_rels" "f5ed098e_62d6_49f3_8fa2_adc1c83aa27a" on ("media"."id" = "f5ed098e_62d6_49f3_8fa2_adc1c83aa27a"."parent_id" and "f5ed098e_62d6_49f3_8fa2_adc1c83aa27a"."path" like $1) left join "user_groups" "cfaf13ba_af5f_449d_8c75_de5f084a388a" on "cfaf13ba_af5f_449d_8c75_de5f084a388a"."id" = "f5ed098e_62d6_49f3_8fa2_adc1c83aa27a"."user_groups_id" left join "media_rels" "7e69adaa_3b63_482b_9046_90ee4a192d7a" on ("media"."id" = "7e69adaa_3b63_482b_9046_90ee4a192d7a"."parent_id" and "7e69adaa_3b63_482b_9046_90ee4a192d7a"."path" like $2) left join "user_groups" "fcca9874_32f7_41e5_96d8_030166aff871" on "fcca9874_32f7_41e5_96d8_030166aff871"."id" = "7e69adaa_3b63_482b_9046_90ee4a192d7a"."user_groups_id" where ("media"."id" = $3 and ("media"."tenant_id" = $4 and ("f5ed098e_62d6_49f3_8fa2_adc1c83aa27a"."user_groups_id" is null or "7e69adaa_3b63_482b_9046_90ee4a192d7a"."user_groups_id" = $5))) order by "media"."created_at" desc limit $6\nparams: userGroups,userGroups,NaN,1,7,1" } ],

Link to the code that reproduces this issue

https://github.com/payloadcms/payload/tree/main/templates/website

Reproduction Steps

Create a new project with payload 3.68.1

Create a collection where you link it to both media and folders. You want to filter the media in a folder by the relevant collection.

Which area(s) are affected?

area: core

Environment Info

payload: 3.67.0
  next: 15.5.7
  @payloadcms/db-postgres: 3.68.1
  @payloadcms/drizzle: 3.68.1
  @payloadcms/email-resend: 3.68.1
  @payloadcms/graphql: 3.68.1
  @payloadcms/next/utilities: 3.68.1   
  @payloadcms/plugin-cloud-storage: 3.68.1
  @payloadcms/plugin-nested-docs: 3.68.1
  @payloadcms/richtext-lexical: 3.68.1 
  @payloadcms/storage-azure: 3.68.1    
  @payloadcms/translations: 3.68.1     
  @payloadcms/ui/shared: 3.68.1        
  react: 19.1.1
  react-dom: 19.1.1
Operating System:
  Platform: win32
  Arch: x64
  Version: Windows 11 Pro
  Available memory (MB): 32211
  Available CPU cores: 22

[WilcoJump]

Just to add: In the error in the JSON I mentioned in the issue above, it's noticeable that with the api/media/folders endpoint, the media ID for the second left join on usergroups is always NaN. This might be why filtering on a collection like usergroups doesn't work.

{ "errors": [ { "message": "Failed query: select distinct "media"."id", "media"."created_at", "media"."created_at" from "media" left join "media_rels" "f5ed098e_62d6_49f3_8fa2_adc1c83aa27a" on ("media"."id" = "f5ed098e_62d6_49f3_8fa2_adc1c83aa27a"."parent_id" and "f5ed098e_62d6_49f3_8fa2_adc1c83aa27a"."path" like $1) left join "user_groups" "cfaf13ba_af5f_449d_8c75_de5f084a388a" on "cfaf13ba_af5f_449d_8c75_de5f084a388a"."id" = "f5ed098e_62d6_49f3_8fa2_adc1c83aa27a"."user_groups_id" left join "media_rels" "7e69adaa_3b63_482b_9046_90ee4a192d7a" on ("media"."id" = "7e69adaa_3b63_482b_9046_90ee4a192d7a"."parent_id" and "7e69adaa_3b63_482b_9046_90ee4a192d7a"."path" like $2) left join "user_groups" "fcca9874_32f7_41e5_96d8_030166aff871" on "fcca9874_32f7_41e5_96d8_030166aff871"."id" = "7e69adaa_3b63_482b_9046_90ee4a192d7a"."user_groups_id" where ("media"."id" = $3 and ("media"."tenant_id" = $4 and ("f5ed098e_62d6_49f3_8fa2_adc1c83aa27a"."user_groups_id" is null or "7e69adaa_3b63_482b_9046_90ee4a192d7a"."user_groups_id" = $5))) order by "media"."created_at" desc limit $6\nparams: userGroups,userGroups,NaN,1,7,1" } ],