Error when trying General-collection hasMany records from SQL-collection

English Forum
1

My collection units has-many userRoles.
The collection userRoles belong-to units and belongs-to users, and has fields like roleUid, isActive etc.

I have created a SQL-collection called unitManagers to filter for specific userRoles where the userRoles.roleUid is “unit_manager” and userRoles.isActive is “true”. The SQL-collection is defined with the following SQL:

SELECT
  "userRoles"."id" AS "id",
  "userRoles"."unitId" AS "unitId",
  "userRoles"."userId" AS "userId",
  "users"."nickname" AS "name",
  "users"."username" AS "shortName"
FROM "users"
JOIN "userRoles" ON "userRoles"."userId" = "users"."id"
WHERE
  "userRoles"."roleUid" = 'unit_manager' AND "userRoles"."isActive" = true;

The userManages SQL-collection works fine by itself. I can create a Table Block that shows all unitManagers. Now the problem: I want to add the unitManagers as an association to units, so I can get a list of all active unitManagers for a specific unit. When I go into the units collection and add a field “One to Many” and add the SQL-collection, it does not work. I get this error:

Cannot read properties of undefined (reading ‘name’)

Why does this happen?

Screenshot 2026-03-24 at 19.45.15
Screenshot 2026-03-24 at 19.45.151396×1358 87.3 KB

2

Hi,
SQL tables are not supported as related data tables.

3

Okay. How would I solve this problem then:

Collections:

units
- id
- name

users 
- id
- name

roles
- uid
- name

userRoles
- id
- userId
- roleUid
- unitId
- activeFrom (date)
- activeUntil (date)
- isActive (boolean)

I want to add a relationship to “units” called “managers” that provides the “userRoles” for the unit where userRoles.roleUid = “unit_manager” and userRoles.isActive = “true”.

(Or in general terms: I would like to add conditions to a belongsTo/hasMany relationship.)

How to do that?

4

I am not related with the NocoBase team, but I believe this task cannot be solved directly. Relationships between tables are solely ID based and cannot contain additional filters.

But practically you can achieve that multiple ways:

  • use a workflow to manage units-users relations on a schedule or when any of two tables changes.
  • if you need only to show the list of managers of a unit in a block, just show the list of users with the required scope set.