problem with relations

[ember grove]

const recipeRecord = await prisma.recipe.create({
  data: {
    type: recipe.type,
    id: recipe.id,
  }
})

const attributesRecord = await prisma.recipeAttributes.create({
  data: {
    recipeId: recipe.id,
    allergens: { create: recipe.attributes.allergens.map(allergen => allergen) },
    basics: { create: recipe.attributes.basics.map(basic => basic) },
    // box_type: { create: recipe.attributes.box_type },
    core_recipe_id: recipe.attributes.core_recipe_id,
    // country: { create: recipe.attributes.country },
    // country_secondary: recipe.attributes.country_secondary ? { create: recipe.attributes.country_secondary } : undefined }
})

[ember grove]

problem with relations

2. When pushing country and secondary_country, the value of secondary_country ends up the same as country when it should be null.

model Recipe {
  type String @default("recipe")
  id   String @id @unique

  collections Collection[] // Related collections
  attributes  RecipeAttributes?
}

model RecipeAttributes {
  allergens         Allergen[]
  core_recipe_id    String
  country           Country?   @relation(name: "CountryRelation")
  country_secondary Country?   @relation(name: "SecondaryCountryRelation")

  // relation to recipe
  recipe   Recipe? @relation(fields: [recipeId], references: [id], onDelete: Cascade)
  recipeId String  @id @unique // FK to Recipe model
}

model Allergen {
  name         String
  slug         String
  contain_type String

  RecipeAttributes RecipeAttributes? @relation(fields: [recipeId], references: [recipeId], onDelete: Cascade)
  recipeId         String            @id // FK to RecipeAttributes model
}

model Country {
  alpha2 String
  name   String

  recipeAttributesCountry          RecipeAttributes? @relation(name: "CountryRelation", fields: [recipeId], references: [recipeId], map: "Country_CountryRelation_fkey", onDelete: Cascade)
  recipeAttributesSecondaryCountry RecipeAttributes? @relation(name: "SecondaryCountryRelation", fields: [recipeId], references: [recipeId], map: "Country_SecondaryCountryRelation_fkey", onDelete: Cascade)
  recipeId                         String            @id // FK to RecipeAttributes model
}

for (const [i, recipe] of recipes.entries()) {
        if (i === 0) {
            try {
                await prisma.recipe.delete({where: {id: recipe.id}})
            } catch (error) {
                console.log('error deleting', error);
            }

            console.log({
                country: recipe.attributes.country,
                secondary: recipe.attributes.country_secondary,
            });

            const recipeRecord = await prisma.recipe.create({
                data: {
                    type: recipe.type,
                    id: recipe.id,
                }
            })
            // recipeRecords.push(recipeRecord)

            
            const attributesRecord = await prisma.recipeAttributes.create({
                data: {
                    recipeId: recipe.id,
                    allergens: { create: recipe.attributes.allergens.map(allergen => allergen) },
                    // basics: { create: recipe.attributes.basics.map(basic => basic) },
                    // box_type: { create: recipe.attributes.box_type },
                    core_recipe_id: recipe.attributes.core_recipe_id,
                    country: { create: recipe.attributes.country },
                    country_secondary: recipe.attributes.country_secondary ? { create: recipe.attributes.country_secondary } : undefined
                }
            })
            // attributesRecords.push(attributesRecord)
        }
    }

[gloomy latch]

Hi Try this instead:

export const formatGoustoMenu = async (data: Data) => {
    // ... rest of your code ...

    // Ensure Menu record exists
    const menuRecord = await prisma.menu.upsert({
        where: { core_menu_id: menu.core_menu_id },
        update: { core_period_id: menu.core_period_id, starts_at: menu.starts_at, ends_at: menu.ends_at },
        create: { core_menu_id: menu.core_menu_id, core_period_id: menu.core_period_id, starts_at: menu.starts_at, ends_at: menu.ends_at },
    });

    for await (const collectionData of data.data[0].relationships.collections.data) {
        // ... rest of your code ...
        const collectionRecord = await prisma.collection.create({
            data: {
                // ... rest of your data ...
                core_menu_id: menuRecord.core_menu_id,
            },
        });
        // ... rest of your code ...
    }

    // ... rest of your code ...
    return { menuRecord };
}

[ember grove]

Hold on, are you telling me to create the menu and the collections or should I just look at the upsert?

[gloomy latch]

Nevermind. Actually you have a lot of different issues. Here is how you could fix them:

About your first issue, unique constraint error on recipeId:

• Ensure that the recipeId you are using for each RecipeAttributes record is unique and does not already exist in the database.
• If you intend to have multiple RecipeAttributes records associated with a single Recipe record, you'll need to adjust your schema to remove the @unique directive from the recipeId field in the RecipeAttributes model.

About your 2nd issue, pushing basics and box_type data:

• Ensure the relations between your models are defined correctly, and the foreign key references are set up correctly.
• Check the data you are trying to push to ensure it conforms to the structure expected by your Prisma schema.
• You may need to adjust the way you are creating related records in your prisma.recipeAttributes.create invocation.

About your third issue, the country_secondary value Issue:

• Ensure the data you are pushing for country and country_secondary is correct.
• Check your prisma.recipeAttributes.create invocation to ensure you are handling the country_secondary field correctly.
• It might be helpful to log the data you are working with to see if there are any discrepancies in the values being used for country and country_secondary.

[ember grove]

1 RecipeAttributes per 1 recipe. The data types are definitely correct and as you can see in the log in the image i attached, secondary is null when passed to create

[gloomy latch]

Your issues are debugging issues. You need to give it a try and ensure all data are correct

[ember grove]

I'm only connecting 1 attributes per 1 recipe.

basics and box_type I think the relations are not done correctly but i'm not sure. Do you see anything on my schema?

country_secondary - as you can see on the screenshot attached, country is an object with data and secondary is null. I think the relation is also not done correctly on that one because when fetching the data for that recipe in client, both country and secondary have the value so it's not differentiating.

[ember grove]

In fact, even when I only create country and leave country_secondary commented out and then fetch the recipe, it still shows both as defined.

and if I comment both out, only then are the properties null

[ember grove]

Separating Country and CountrySecondary into two separate models did the trick which proves that the initial relations reusing the same Country model were not set up correctly. If it's not possible to relate the same model more than once then I have a big problem because the Basic model is reused many times throughout my RecipeAttributes properties. Surely, it must be possible to reference to the same model more than once no?

and now back to the previous approach - does not work again. Can somebody please tell me what I did wrong with this named relation?

[ember grove]

@gloomy latch

[gloomy latch]

Have you removed the @unique attribute from RecipeAttributes model for the recipeId field as i suggested above?

Because looking at the screenshot of your code, you didn't

[ember grove]

But there is only going to be 1 recipeAttributes with that id because it will only ever be related to 1 recipe so that should not be the issue

[gloomy latch]

So a one to one relation?

[ember grove]

recipe and recipeattributes, yes

but that's not the issue i'm dealing with right now. That relation works fine

[gloomy latch]

Okay, so what's the issue?

[ember grove]

I described is above in great detail

[gloomy latch]

I really want to help you out, but I'm having difficulties to understand the issue you've described due to the multiple points mentioned at the start. Could you please provide a clearer explanation or perhaps focus on one main concern?

[ember grove]

Let's focus on this one for now

[ember grove]

starting at this message

[ember grove]

@gloomy latch

[gloomy latch]

@ember grove which version of Prisma are you using?

[gloomy latch]

Also, it could be a good idea to have a reproduction repo

[ember grove]

5.5.2

[cedar stone]

Whats your problem

[ember grove]

Hello, I'm having a problemwith my relations.

[ember grove]

This doesn't work

[ember grove]

but this does

but I need the first method to work as my my RecipeAttributes model is full of properties that will require me to reuse the same models.

[cedar stone]

Im on my phone now so i cant look in depth in ur code, ur problem is thst you are unable to create records with relations?

There is lots of confusion here, can u send a clean snippet pls

[ember grove]

No, the relations seem to not work correctly because fetching the recipe fetches both country and country_secondary with the same value of country rather than country and null

[cedar stone]

Country_secondary is null?

[ember grove]

This proves that the data is passed correctly as separating that relation to the same model into two separate models works as intended

[ember grove]

this proves that the relation is not set up correctly as trying to reuse the same Country model in country and country_secondary has the effoect of the value of both being that of country

[ember grove]

No, it should be null - but it isn't.

it's connecting country and country_secondary to the value of country.

[cedar stone]

I see null here

There is too much confusion

[ember grove]

No, there is no confusion. You just haven't read the message. This is correct, but only because i separated the country model into model country and model country_secondary

tryign to do the same thing with just one model, coutry, doesn't work. When fetching the recipe, both properties have the value of country.

I could leave it as separate models, but it's going to be problematic because the other properties in this RecipeAttributes model will reuse many of the different models like the Basic model which is supposed to be reused 4 or 5 times throughout the schema.

So there is clearly something about my method of relating to the same model that doesn't work.

Look

Now doing it the crappy, non-programmatic way...

All the resources I read through tell me that I've related them correctly, yet it still refuses to work

[cedar stone]

I'll check this later today if you arent in a rush

And try to help u

Im at work now xdd

[ember grove]

Thank you, that would be really nice. I've been stuck on this for days and I can't progress with my project

I think the issue is that each relation should have its own key and both mine use recipeId

[cedar stone]

I still have to go home but

Country and countrysecondary are basically the same thing?

[ember grove]

Yes, that's why I use the same model. But the values could be different. It will either have a value or be null

[cedar stone]

Alright ima be home in a hour

[ember grove]

thanks

[cedar stone]

So i didnt have time to get on my computer.

Basically what I wanted to suggest to you is to re-think about your current schema and only have three models (RecipeAttributes, Allergeni, Countries).

To make it simple you need a field that as type has the model as array.

kind-of snippet (im still on mobile)
model Recipes

• name string @unique
• author string @unique
• allergenes Allergeni[]

model Allergeni
type Number
desc String

If you launch prisma studio (npx prisma studio) you will have a better idea about my solution

Or wait for me to get home later today, i will give u some code

Or better, check my profile, my Github then click on ac-livery-manager and check my schema

[cedar stone]

Same thing for the field country in RecipeAttributes

Only 1 field

Countries Country[]

This way countries can have more than 1 country that are in the database or NULL if you want, or 0, i dont remember what It gonna return

@ember grove

[ember grove]

The thing is, I can't edit my data sturcture as I'm mapping another api and formatting for my front end. My actual schema is a lot more complex

[ember grove]

@cedar stone @gloomy latch so even after splitting everything into separate models, I'm still somehow having issues with relations. In this case, the creation breaks when I try to create Basics. Is there anything in the schema or the create for recipeAttributes that could cause the error in the console? it is related the same way the Allergen is, and allergens work just fine, so I really have no idea what could be causing Basics to come up with this constraint failed error.

And this is what the data being passed to basics looks like for this particular recipe

[
  {
    name: "Butter",
    slug: "butter"
  }, 
  {
    name: "Olive oil",
    slug: "olive-oil"
  }, 
  {
    name: "Pepper",
    slug: "pepper"
  }, 
  {
    name: "Salt",
    slug: "salt"
  }
]

[ember grove]

the recipeID in RecipeAttributes is unique, but that's only because there is only 1 recipe attributes per recipe. So I can't imagine using recipeId in Allergen or Basic breaking that constraint as they are both have their own tables. What do you guys think?

[ember grove]

Interestingly enough, it works if I pass it an array with just a single basic like so

basics: { create: 
                [{ name: "Butter", slug: "butter" }]
            // recipe.attributes.basics.map(basic => basic) 
    },

But the recipeId I set on the Basic model is marked with @id but it is not unique, so why would I not be allowed to create multiple relating to the same recipeAttributes?

EDIT: I removed the @unique attribute from the id on recipe and the recipeId on RecipeAttributes and it didn't make a difference. I still get the same error

[cedar stone]

i dont know how to help then

i hope you'll find a solution as soon as possible

[ember grove]

How do I get a staff member to help? I messaged them all and nobody responded. This is really messing with my timeline

@shadow lake @runic ivy @wanton storm @chrome sinew

[cedar stone]

i dont think they will help

[ember grove]

I mean, I read the docs. And even the moderator told me that I am doing it how I should be. So how do I get help?

It's too late to drop Prisma at this point. There must be some key information we are all missing

[shadow lake]

We help where we can, but this is a community support channel first and foremost. If you’re seeing an issue that you believe is a bug, you should file a GitHub issue.

From glancing over this thread, I think I’m seeing that not all information is being shared. That makes it difficult to understand what’s going on.

[ember grove]

Just read from my message at 11:55 all the relevant code is included

The schema, the creation code, the terminal

the data structure

the test case

[ember grove]

starting here

[shadow lake]

What database are you using?

[ember grove]

i use supabase and sync the db and schema on each edit

[wanton storm]

@ember grove Apologies I just checked my dms and you did send me a direct message. I missed it as it was on sunday. Let me try to catch up on this chat as well.

[ember grove]

Thank you. Starting that the message I just mentioned will do as I was trying a different approach earlier on in the thread. I was trying to reuse models to reduce the number of models in my schema but I decided to just separate everything to get it done as quickly as possible

[shadow lake]

Why is recipeId marked as an id everywhere

If it’s an FK it should be treated as a relation

https://www.prisma.io/docs/concepts/components/prisma-schema/relations

[ember grove]

that is the id I use to relate the model to the RecipeAttributes for that recipe and each model should have an id

and it works, 'm just unable to create more than I Basic or Allergen for that recipe

[shadow lake]

You’ve marked the column recipeId as an id for the Basic table, for example. Meaning only one Basic can have that recipe ID

[ember grove]

passing a single Basic works, but more than 1 breaks it even though it's not marked as unique for that table

[shadow lake]

IDs are by definition unique

[ember grove]

That makes sense, let me try adding a separate id

[ember grove]

That did it! Okay, and could you tell me why this approach didn't work? (country and country_secondary always fetched the same value of country but country should have a value and secondary should be null for this particular recipe)

[shadow lake]

You’ll need to check the migrations to confirm this, but @id makes the column a primary key. Primary keys are by definition non null and unique. That is why you were seeing failed unique constraints.

If you’re looking to get FK constraints, id recommend looking over the docs I pasted above which goes over modeling that in depth.

[ember grove]

Here it's asking me to "Error parsing attribute "@relation": A one-to-one relation must use unique fields on the defining side. Either add an @unique attribute to the field recipeId, or change the relation to one-to-many." but these are named relations and adding unique to recipeId would lead to the same issue we had earlier. What do you suggest?

[shadow lake]

What are you trying to relate there?

[ember grove]

basics and box_type use the same type, and this happens multiple times throughout my data structure. So in this case, I wanted to simply reuse the Basic model and make it nullable on box_type as that one can sometimes be null. So I related Basic to RecipeAttributes via recipeId and added names to the relations

[shadow lake]

So can RecipeAttributes have one or more Basic or the other way around?

[ember grove]

RecipeAttributes is the parent of Basics, Allergens, BoxType and so on. However, basics uses an array of Basic and box_type uses one of Basic but it can also be null. I can get rid of 9 models just by being able to reuse

take a look at this

[shadow lake]

Polymorphism like that isn’t directly supported by Prisma. Since a Basic can be related to a RecipeAttribute, then you will need a column recipeAttributeID on Basic that contains the primary key of the RecipeAttribute

[ember grove]

That's the recipeId pretty much, that's what links it to the RecipeAttributes. So are you saying that I need to add a key in the Basic model for every property I plan to use Basic with?

[shadow lake]

Well, you mentioned above that RecipeAttribute can have one or more of a number of different models. You will need to put foreign keys on all of those models so they relate back to their parent RecipeAttribute

If Basic is related to other things, then it will need a column for those other things as well

[ember grove]

So in addition to this, I would also need to add the same keys to the RecipeAttributes? is there no way to link them all to RecipeAttributes just by the recipeId since they're names relations? I mean, map on the child model side since they all belong to the same RecipeAttributes, just different property

[shadow lake]

This appears to be really complex, so I’m not sure. Basically for any child/parent relationship you need an array of children on the parent and then the primary key of the parent on the child.

[ember grove]

and making the basicsKey unique would again prevent me from using Basic[]

[shadow lake]

https://www.prisma.io/docs/concepts/components/prisma-schema/relations/one-to-many-relations#overview

In this case RecipeAttribute == User and Basic == Post

[ember grove]

Yes, I've reread through and I'm really struggling to understand why my case is not working. I am relating the basics and boxtype to the recipeattributes by their own ids referencing the id of the recipeattributes but it's still asking me to make one of them unique. In the example, they're mapping to the correct user by email. in mine, i'm mapping to the correct parent by recipeId

The difference is that they are referencing multiple of the same item whereas I'm trying to use the same model for the same type items but under different properties

[shadow lake]

boxTypeKey must be unique

[ember grove]

Yep, I have to make every key unique which leads to the initial issue of not being able to use that model as an array of that model

[ember grove]

I tried testing it anyway but I am now unable to create. Since you said that modelling data in this way isn't directly supported by Prisma, it looks like i'm trying to make it do something it's not supposed to so I think I will just leave everything separated to save us all the headache

[ember grove]

I'm really sorry but even separating the models will not work when marking the recipeId as unique. As soon as it tries to push more than 1 Basic to the Basic[], it hits the constraint again. What should I do?

[shadow lake]

recipeId on Basic can’t be unique

[ember grove]

Alrighty, so only mark it as unique when it is not being used as an array?

[shadow lake]

If multiple rows can have the same value in that column, it can’t be unique. So in this case multiple Basic can have the same recipeId, so it’s not unique

[ember grove]

Thanks a lot, that did it. I held on with a response until I finished testing. I think i'm good now. Thanks again for all your help

[ember grove]

Sorry, quick question. How would I set this up

model Surcharges {
for1
for2
for3
for4
for5

rowId Int @id @default(autoincrement())
RecipeAttributes RecipeAttributes? @relation(fields: [recipeId], references: [recipeId], onDelete: Cascade)
recipeId String @unique // FK to RecipeAttributes model
surchargesId String @unique @default(cuid())
}

model Surcharge {
name String
price Price @relation(fields: [priceRowId], references: [rowId])

rowId Int @id @default(autoincrement())
Surcharge Surcharges?
surchargeId String @unique @default(cuid())
}

model Price {
currency String
value Int

rowId Int @id @default(autoincrement())
}

to achieve this data stricture without defining 5 separate models for surcharge1,2,3,4,5?

type Surcharges = {
  for1: { name: string; price: { currency: string; value: number } } | null;
  for2: { name: string; price: { currency: string; value: number } } | null;
  for3: { name: string; price: { currency: string; value: number } } | null;
  for4: { name: string; price: { currency: string; value: number } } | null;
  for5: { name: string; price: { currency: string; value: number } } | null;
};

@shadow lake do you reckon it's possible?

[ember grove]

I tried
model Surcharges {
for1 SurchargeFor1? @relation(fields: [surchargeFor1Id], references: [rowId])
for2 SurchargeFor2? @relation(fields: [surchargeFor2Id], references: [rowId])
// same for 3,4,5
rowId Int @id @default(autoincrement())
RecipeAttributes RecipeAttributes? @relation(fields: [recipeId], references: [recipeId], onDelete: Cascade)
recipeId String @unique // FK to RecipeAttributes model
surchargeFor1Id Int
surchargeFor2Id Int
// same for 3,4,5
}

model SurchargeFor1 {
name String
price Price @relation(fields: [priceRowId], references: [rowId])

rowId Int @id @default(autoincrement())
priceRowId Int @unique
Surcharges Surcharges[]
}

model SurchargeFor2 {
name String
price Price @relation(fields: [priceRowId], references: [rowId])

rowId Int @id @default(autoincrement())
priceRowId Int @unique
Surcharges Surcharges[]
}

// same for 3,4,5

model Price {
currency String
value Int

rowId Int @id @default(autoincrement())
SurchargeFor1 SurchargeFor1?
SurchargeFor2 SurchargeFor2?
// same for 3,4,5
}

surcharges: { 
  for1: recipe.attributes.surcharges.for1 ? { create: recipe.attributes.surcharges.for1 }: undefined,
  for2: recipe.attributes.surcharges.for2 ? { create: recipe.attributes.surcharges.for2 }: undefined,
  for3: recipe.attributes.surcharges.for3 ? { create: recipe.attributes.surcharges.for3 }: undefined,
  for4: recipe.attributes.surcharges.for4 ? { create: recipe.attributes.surcharges.for4 }: undefined,
  for5: recipe.attributes.surcharges.for5 ? { create: recipe.attributes.surcharges.for5 }: undefined,
},

[ember grove]

hey @shadow lake really sorry to bother you, what do you think about the above?

[shadow lake]

I'm not sure what you're trying to do. What does Surcharge relate to?

[ember grove]

Basically, this is the data structure I'm trying to achieve

type Surcharges = {
  for1: { name: string; price: { currency: string; value: number } } | null;
  for2: { name: string; price: { currency: string; value: number } } | null;
  for3: { name: string; price: { currency: string; value: number } } | null;
  for4: { name: string; price: { currency: string; value: number } } | null;
  for5: { name: string; price: { currency: string; value: number } } | null;
};

Surcharges is the parent object
surcharge is each of the for1,2,3,4,5
price is the object of price

Is there a simpler way to do it than defining a model for each Surcharge and each Price?

that's 10 extra models relating to surcharges if I was to do them 1 by 1

[shadow lake]

Hard coding things like this seems like a bad problem waiting to happen. But, if you need to do this, then I would make each "for1" "for2" etc to be JSON instead of models

[ember grove]

I thought about doing that way, but that would mean that surcharges would not have types when working in client

[shadow lake]

you can just type them in TS

[ember grove]

could do but it kind of defeats the purpose of having a fully typed client. How would you approach this problem?

[shadow lake]

What is "Surcharges" related to?

[ember grove]

surcharges is a property of the RecipeAttributes

[shadow lake]

Then I would model Surcharge like the following:

model Surcharge {
  id
  name
  currency
  value
  recipeAttributeId
}

and then RecipeAttribute would have something like this

model RecipeAttribute {
  .
  .
  .
  surcharges Surcharge[]
}

[ember grove]

This is the shape of the data the api will pass to the create method

{
  for1: { name: string; price: { currency: string; value: number } } | null;
  for2: { name: string; price: { currency: string; value: number } } | null;
  for3: { name: string; price: { currency: string; value: number } } | null;
  for4: { name: string; price: { currency: string; value: number } } | null;
  for5: { name: string; price: { currency: string; value: number } } | null;
}

How would I then make it so it distributes each of those fors to create 5 surcharges?

[shadow lake]

I don't understand. You are receiving data via an api, correct? Then you can just modify the data in your business logic

[ember grove]

sorry, following the model structure you've suggested, is this how I create 5 surcharges connected to the array?

[shadow lake]

I would not do a nested create.

Create the RecipeAttribute, then after create each surcharge

Regardless, we're quite a bit removed from your original question. Could you try this out on your own and if you're continuing to run into issues, open a new thread?

[ember grove]

or perhaps, forEach recipe.attributes.surcharges?

Object.entries(surcharges).forEach(([key, surcharge]) => {
  if(surcharge) { // check if surcharge is not null
      create: {
        name: surcharge.name,
        currency: surcharge.price.currency,
        value: surcharge.price.value,
      }
  }
})

Believe me, I tried many things. I wouldn't be asking if I wasn't really stuck