RESTful API Handler to include many-to-many relations

[stevendonorlink]

Description and expected behavior
I am currently using the zenstack Restful API Handler with calling the following endpoint:
http://localhost:4001/api/user?include=offices

Below are my models (I have removed some fields to not overcomplicate things):

model User extends Base {
    externalId                   String
    firstName                    String                @length(1, 256)
    lastName                     String                @length(1, 256)
    email                        String
    username                     String
    offices                      UserOffice[]
}


model UserOffice extends NoIdBase {
    user         User    @relation(fields: [userId], references: [id], onDelete: Restrict)
    userId       String  @db.Uuid
    office       Office  @relation(fields: [officeId], references: [id], onDelete: Restrict)
    officeId     String  @db.Uuid

    @@unique([userId, officeId])
}

model Office extends Base {
    name                         String
    users                        UserOffice[]
    country                      String           @default('Australia')
    state                        String           @default('Victoria')
}

What I have received is as follow (I have removed some fields to not overcomplicate things):

{
    "jsonapi": {
        "version": "1.1"
    },
    "links": {
        "self": "http://localhost:4001/api/user",
        "first": "http://localhost:4001/api/user?filter%5Bid%5D=ae3ae174-d4fe-4869-8218-6a020d6878c5%2C68fb45cf-abac-4894-b6e0-2342911333fc%2C75908cc4-be29-4e1a-868a-3c12efec78be%2Cef95f21b-3e92-4044-a988-65dbd0a88b1b&page%5Blimit%5D=100",
        "last": "http://localhost:4001/api/user?filter%5Bid%5D=ae3ae174-d4fe-4869-8218-6a020d6878c5%2C68fb45cf-abac-4894-b6e0-2342911333fc%2C75908cc4-be29-4e1a-868a-3c12efec78be%2Cef95f21b-3e92-4044-a988-65dbd0a88b1b&page%5Boffset%5D=0",
        "prev": null,
        "next": null
    },
    "data": [
        {
            "type": "user",
            "id": "68fb45cf-abac-4894-b6e0-2342911333fc",
            "attributes": {
                "clientId": "9435cf4c-734c-4ae4-9636-6180b9b1bb41",
                "createdAt": "2025-09-23T03:18:36.229Z",
                "createdBy": "ae3ae174-d4fe-4869-8218-6a020d6878c5",
                "updatedAt": "2025-09-30T01:26:58.867Z",
                "updatedBy": "ae3ae174-d4fe-4869-8218-6a020d6878c5",
                "firstName": "Lauren",
                "lastName": "Admin1",
                "email": "dev+test+fundraiser1@test.com.au",
                "username": "dev+test+fundraiser1@test.com.au",
                "mobile": "0434511817",
                "status": "Active",
            },
            "links": {
                "self": "http://localhost:4001/api/user/68fb45cf-abac-4894-b6e0-2342911333fc"
            },
            "relationships": {
                "offices": {
                    "links": {
                        "self": "http://localhost:4001/api/user/68fb45cf-abac-4894-b6e0-2342911333fc/relationships/offices",
                        "related": "http://localhost:4001/api/user/68fb45cf-abac-4894-b6e0-2342911333fc/offices"
                    },
                    "data": [
                        {
                            "type": "userOffice",
                            "id": "68fb45cf-abac-4894-b6e0-2342911333fc_e282dcd6-ee66-4fe2-8e73-718b2c1b6866"
                        }
                    ]
                },
            }
        },
        {
            "type": "user",
            "id": "75908cc4-be29-4e1a-868a-3c12efec78be",
            "attributes": {
                "clientId": "9435cf4c-734c-4ae4-9636-6180b9b1bb41",
                "createdAt": "2025-09-30T06:15:25.248Z",
                "createdBy": "ae3ae174-d4fe-4869-8218-6a020d6878c5",
                "updatedAt": "2025-09-30T06:15:25.248Z",
                "updatedBy": null,
                "externalId": "993e7488-d061-703b-4102-a9d07a8e1680",
                "firstName": "Steven",
                "lastName": "4",
                "email": "steven+fund4@test.com.au",
                "username": "steven+fund4@test.com.au",
                "mobile": "0434511817",
                "status": "Inactive",
            },
            "links": {
                "self": "http://localhost:4001/api/user/75908cc4-be29-4e1a-868a-3c12efec78be"
            },
            "relationships": {
                "offices": {
                    "links": {
                        "self": "http://localhost:4001/api/user/75908cc4-be29-4e1a-868a-3c12efec78be/relationships/offices",
                        "related": "http://localhost:4001/api/user/75908cc4-be29-4e1a-868a-3c12efec78be/offices"
                    },
                    "data": [
                        {
                            "type": "userOffice",
                            "id": "75908cc4-be29-4e1a-868a-3c12efec78be_94f745fe-e145-49cc-a556-8a578c21962f"
                        },
                        {
                            "type": "userOffice",
                            "id": "75908cc4-be29-4e1a-868a-3c12efec78be_e282dcd6-ee66-4fe2-8e73-718b2c1b6866"
                        }
                    ]
                },
            }
        },
        {
            "type": "user",
            "id": "ef95f21b-3e92-4044-a988-65dbd0a88b1b",
            "attributes": {
                "clientId": "9435cf4c-734c-4ae4-9636-6180b9b1bb41",
                "createdAt": "2025-09-24T05:41:14.067Z",
                "createdBy": "ae3ae174-d4fe-4869-8218-6a020d6878c5",
                "updatedAt": "2025-10-01T05:44:32.206Z",
                "updatedBy": "ae3ae174-d4fe-4869-8218-6a020d6878c5",
                "firstName": "Steven123",
                "lastName": "Admin1",
                "email": "steven+fund10@ test.com.au",
                "username": "steven+fund10@test.com.au",
                "mobile": "0434511817",
                "status": "Active",
            },
            "links": {
                "self": "http://localhost:4001/api/user/ef95f21b-3e92-4044-a988-65dbd0a88b1b"
            },
            "relationships": {
                "offices": {
                    "links": {
                        "self": "http://localhost:4001/api/user/ef95f21b-3e92-4044-a988-65dbd0a88b1b/relationships/offices",
                        "related": "http://localhost:4001/api/user/ef95f21b-3e92-4044-a988-65dbd0a88b1b/offices"
                    },
                    "data": [
                        {
                            "type": "userOffice",
                            "id": "ef95f21b-3e92-4044-a988-65dbd0a88b1b_e282dcd6-ee66-4fe2-8e73-718b2c1b6866"
                        },
                        {
                            "type": "userOffice",
                            "id": "ef95f21b-3e92-4044-a988-65dbd0a88b1b_94f745fe-e145-49cc-a556-8a578c21962f"
                        }
                    ]
                },
            }
        }
    ],
    "included": [
        {
            "type": "userOffice",
            "id": "68fb45cf-abac-4894-b6e0-2342911333fc_e282dcd6-ee66-4fe2-8e73-718b2c1b6866",
            "attributes": {
                "clientId": "9435cf4c-734c-4ae4-9636-6180b9b1bb41",
                "createdAt": "2025-09-23T03:18:36.233Z",
                "createdBy": "ae3ae174-d4fe-4869-8218-6a020d6878c5",
                "updatedAt": "2025-09-23T03:18:36.233Z",
                "updatedBy": null,
                "userId": "68fb45cf-abac-4894-b6e0-2342911333fc",
                "officeId": "e282dcd6-ee66-4fe2-8e73-718b2c1b6866",
                "isHomeOffice": true
            },
            "links": {
                "self": "http://localhost:4001/api/userOffice/68fb45cf-abac-4894-b6e0-2342911333fc_e282dcd6-ee66-4fe2-8e73-718b2c1b6866"
            },
            "relationships": {
                "user": {
                    "links": {
                        "self": "http://localhost:4001/api/userOffice/68fb45cf-abac-4894-b6e0-2342911333fc_e282dcd6-ee66-4fe2-8e73-718b2c1b6866/relationships/user",
                        "related": "http://localhost:4001/api/userOffice/68fb45cf-abac-4894-b6e0-2342911333fc_e282dcd6-ee66-4fe2-8e73-718b2c1b6866/user"
                    }
                },
                "office": {
                    "links": {
                        "self": "http://localhost:4001/api/userOffice/68fb45cf-abac-4894-b6e0-2342911333fc_e282dcd6-ee66-4fe2-8e73-718b2c1b6866/relationships/office",
                        "related": "http://localhost:4001/api/userOffice/68fb45cf-abac-4894-b6e0-2342911333fc_e282dcd6-ee66-4fe2-8e73-718b2c1b6866/office"
                    }
                }
            }
        }
    ],
    "meta": {
        "serialization": {
            "values": {
                "data.0.attributes.createdAt": [
                    "Date"
                ],
                "data.0.attributes.updatedAt": [
                    "Date"
                ],
                "data.1.attributes.createdAt": [
                    "Date"
                ],
                "data.1.attributes.updatedAt": [
                    "Date"
                ],
                "data.2.attributes.createdAt": [
                    "Date"
                ],
                "data.2.attributes.updatedAt": [
                    "Date"
                ],
                "data.3.attributes.createdAt": [
                    "Date"
                ],
                "data.3.attributes.updatedAt": [
                    "Date"
                ],
                "included.0.attributes.createdAt": [
                    "Date"
                ],
                "included.0.attributes.updatedAt": [
                    "Date"
                ]
            }
        },
        "total": 3
    }
}

As you can see I want to include the many-to-many relations (in our case offices/userOffice). The data we have received back shows all the relationships (with just the Ids). However, the included part is only returning 1 single userOffice even though there should be multiple (2 offices in our case).

When I include One-to-Many relationships, for example one user have one level, (or one level have many user) it would then show the multiple level based on the users returned, which is good.

The problem here is that when it is many-to-many relationship, it is not returning all the relations. Rather, it only return one (from what I can see, which is the first user's userOffice).

Please any assistant would be great, otherwise, it would be painful to keep calling multiple endpoints to list down all the relationships.

Environment:

• ZenStack version: 2.19.1
• Prisma version: 6.16.2
• Database type: Postgresql

[lsmith77]

This looks similar to my issue. On different relations at times I see only one entity included in the includes, despite all of the relations properly being listed in data. I will compare your case with mine to try and see if there are similarities.

One thing I have discovered is that if I enable info logging on prisma I am getting errors like the following for the entities that end up missing:

prisma:info [policy] dropping unreadable field id
prisma:info [policy] dropping unreadable field foo.bar_id
prisma:info [policy] dropping unreadable field foo.bar_id

Can you check if you also get these info logs?

[lsmith77]

This looks similar to my issue. On different relations at times I see only one entity included in the includes, despite all of the relations properly being listed in data. I will compare your case with mine to try and see if there are similarities.

One thing I have discovered is that if I enable info logging on prisma I am getting errors like the following for the entities that end up missing:

prisma:info [policy] dropping unreadable field id
prisma:info [policy] dropping unreadable field foo.bar_id
prisma:info [policy] dropping unreadable field foo.bar_id

Can you check if you also get these info logs?

OK nevermind these log messages are caused by the use of @deny('all', true). But removing this doesn't fix the issue, so its unrelated.

[lsmith77]

For the record I have debug this and it appears that the entities are "lost" during the serialization const serialized = await serializer.serialize(itemsWithId, options);

https://github.com/zenstackhq/zenstack/blob/main/packages/server/src/api/rest/index.ts#L1423C9-L1423C77

So the data is contained in itemsWithId but is then missing in serialized.

I have not identified a pattern but I have a model with 6 relations and only half of them seem to be affected:

model Project {
  ..
  tender_invitations       TenderInvitation[]
  auth_projects            AuthProject[]
  others                   Other[]
  simap_notices            SimapNotice[]
  awards                   Award[]
  award_specifications     AwardSpecification[]
}

# always only 1 entity in the includes
model TenderInvitation {
  ..
  project              Project?    @relation(fields: [project_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
}

# returned all includes as expected
model AuthProject {
  project              Project?  @relation(fields: [project_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
}

[lsmith77]

I have further debugged this and I believe the issue is in this call:
https://github.com/mathematic-inc/ts-japi/blob/main/src/classes/serializer.ts#L312

ie. in the code for recurseRelators:
https://github.com/mathematic-inc/ts-japi/blob/main/src/utils/serializer.utils.ts#L5

Unfortunately, VSCode crashes when I try to debug this using the step by step debugger. I have opened an issue there, though without a reproducible test case, it's obviously hard to fix.

[lsmith77]

OK, I have figured out the root issue. The issue is caused if the @id is not called id. This breaks ts-japi's relatorCache as it then uses undefined for the cache key, hence only including one entity for the given model.

[lsmith77]

I have submitted a PR with a fix mathematic-inc/ts-japi#105
The work-around is of course to rename all @id field names to id.

[stevendonorlink]

OK, I have figured out the root issue. The issue is caused if the @id is not called id. This breaks ts-japi's relatorCache as it then uses undefined for the cache key, hence only including one entity for the given model.

Hi mate, Thank you very much for the PR you have opened, definitely helped out alot!

I have tried adding the changes you have made to the respective package file on my repo and it works! Your PR should be approved by the team!

[stevendonorlink]

I have submitted a PR with a fix mathematic-inc/ts-japi#105 The work-around is of course to rename all @id field names to id.

As for this, I do not think it is a good idea, Since the relationship table does not really need an id, it just needs the reference ids for example, the userId and the officeId. But yeah I understand your point.

[lsmith77]

OK, I have figured out the root issue. The issue is caused if the @id is not called id. This breaks ts-japi's relatorCache as it then uses undefined for the cache key, hence only including one entity for the given model.

Hi mate, Thank you very much for the PR you have opened, definitely helped out alot!

I have tried adding the changes you have made to the respective package file on my repo and it works! Your PR should be approved by the team!

could you please also comment in mathematic-inc/ts-japi#105 to let those maintainers know that the fix also works for you? thx

[lsmith77]

thx. the PR has been merged. lets see how quickly a new release is made.

[lsmith77]

They have not made a release yet and did not respond to my question about making a release. Since I "fixed" my issues in my project by renaming the relevant fields to id, I thought I was ok without a release.

Turns out that this issue also affects composite id's and so I do need an updated version after all. I guess for now I will point to their repository.

/cc @ymc9

[stevendonorlink]

I need an updated version as well. This is currently breaking things and I am unable to use this library. Do you have any updated from the team? @ymc9

[ymc9]

I just saw a release was PUBLISHED yesterday. Will make an update this week :D.

[ymc9]

Or if any of you have time for a PR would be great too ^_^