How to set up one to many self recursive create?

[Dennis-Zhang-SH]

model location {
    id                   Int             @id @default(autoincrement())
    location_code        String          @unique @db.VarChar(255)
    location_name        String          @db.VarChar(255)
    parent_loaction      AssetLocation?  @relation("parentChildren", fields: [parent_id], references: [id])
    parent_id            Int?
    children_location    AssetLocation[] @relation("parentChildren")
}

I've got a model like this, and in the create function, I have to set up parent_id in the params, but it resulting errors:

an error occured during query execution: Error executing query: P2009 - Failed to validate the query: `Unable to match input value to any allowed input type for the field. Parse errors: [Query parsing/validation error at `Mutation.createOneAssetLocation.data.AssetLocationCreateInput.parent_id`: Field does not exist on enclosing type.

my code:

client
        .asset_location()
        .create(
            payload.location_code,
            payload.location_name,
            vec![db::asset_location::parent_id::set(payload.parent_id)],
        )
        .exec()
        .await?

[Brendonovich]

Hmm, I can't seem to reproduce this using this code:

model location {
    id                Int        @id @default(autoincrement())
    location_code     String     @unique
    location_name     String
    parent_loaction   location?  @relation("parentChildren", fields: [parent_id], references: [id])
    parent_id         Int?
    children_location location[] @relation("parentChildren")
}

let one = client
    .location()
    .create("1".to_string(), "one".to_string(), vec![])
    .exec()
    .await?;

client
    .location()
    .create(
        "2".to_string(),
        "two".to_string(),
        vec![location::parent_id::set(Some(one.id))],
    )
    .exec()
    .await?;

Would you mind creating a small reproduction repo?

[Dennis-Zhang-SH]

Hmm, I can't seem to reproduce this using this code:

model location {
    id                Int        @id @default(autoincrement())
    location_code     String     @unique
    location_name     String
    parent_loaction   location?  @relation("parentChildren", fields: [parent_id], references: [id])
    parent_id         Int?
    children_location location[] @relation("parentChildren")
}

let one = client
    .location()
    .create("1".to_string(), "one".to_string(), vec![])
    .exec()
    .await?;

client
    .location()
    .create(
        "2".to_string(),
        "two".to_string(),
        vec![location::parent_id::set(Some(one.id))],
    )
    .exec()
    .await?;

Would you mind creating a small reproduction repo?

https://github.com/Dennis-Zhang-SH/prisma-error

Hi this is the reproduce repo.

[Brendonovich]

Ahhh are you trying to link the record you're creating to itself? I'm not sure that's possible - you'd need to update the record after creating it. The error you're getting is interesting though - I think it's because you're having to use asset_location::asset::connect (under the hood of create), but are also using asset_location::parent_id::set which isn't possible as you can't mix connect and set on foreign keys in the same query.
Removing the asset field allows the asset_location::parent_id::set to work, but using asset_location::parent_location::connect doesn't and I assume that's because Prisma might be doing a join to create the relation but the join won't return any results.
The only good way to do what you're trying to do is to update the field after creating it from what I can tell.

[Dennis-Zhang-SH]

Ahhh are you trying to link the record you're creating to itself? I'm not sure that's possible - you'd need to update the record after creating it. The error you're getting is interesting though - I think it's because you're having to use asset_location::asset::connect (under the hood of create), but are also using asset_location::parent_id::set which isn't possible as you can't mix connect and set on foreign keys in the same query. Removing the asset field allows the asset_location::parent_id::set to work, but using asset_location::parent_location::connect doesn't and I assume that's because Prisma might be doing a join to create the relation but the join won't return any results. The only good way to do what you're trying to do is to update the field after creating it from what I can tell.

It does not need to link back to itself, but surely this kind of model can not be created, update the data after create may be a alternative way to fix this, but actually this can be done simply in one step(using raw SQL query), will need transaction with 2 steps, I am hoping some improvements on it later:).

[Brendonovich]

Ah if you're not trying to connect the record to itself then the problem is definitely the combination of asset::connect and parent_id::set. That's something I've been thinking about recently and I'll probably separate create into create and create_unchecked which will enforce this at a type-level.