Gadget Developers•7mo ago

Upserting limit

When doing an upsert we pass something like on: ['shop', 'date'], so in this case its for daily records against a shop. I'm now introducing another model which will be for a location for a shop daily, so I'll need to do something like on: ['shop', 'date', 'location] Is it acceptable to do more than 2 fields in an upsert? Or is there a proper way to handle this?

26 Replies

Chocci_Milk•7mo ago

You can upsert on as many fields as you need. You need to make sure that the fields have a uniqueness validation in order to upsert on them though

ljspoor94OP•7mo ago

I believe the UI only allows to select one field against a field currently?

Chocci_Milk•7mo ago

Sorry, what do you mean? What UI are you referring to?

ljspoor94OP•7mo ago

Where you edit a field in the gadget editor, you select uniqueness then it allows you to select one other field.

Chocci_Milk•7mo ago

Can you please show me? I'm still not following. You can add uniqueness to almost any field in the database. You can scope to multiple fields if I'm not mistaken

ljspoor94OP•7mo ago

Away from my computer currently but will share a screenshot as soon as I can

Chocci_Milk•7mo ago

I was wrong about being able to scope by multiple fields. You don't necessarily need to scope by more than one field to make the upsert work. Could you not simply have location (belongsTo relationship) unique?

ljspoor94OP•7mo ago

I already have a location relationship setup too. It’s just ensuring the correct record is upserted. I’ll share some more info once back at my computer!

Chocci_Milk•7mo ago

Yes, what I'm saying is that if the location field is set to unique, you can upsert with it The fields that you pass to the on aren't determined solely by the scoped by fields

ljspoor94OP•7mo ago

Hey Antoine, just getting back around to this, so I've setup 3 new models. So to explain these, lets look at the locationVariantStock model, this will contain a record per day for each variant for each location in a store. The same goes with the others, but instead for product and shop. Having a bit of a brain fog and not totally certain on what the correct way to setup unique for this would be? If I'm understanding correctly I have 3 fields I'd need to look at here: - date - variant - location As we discussed, the UI only allows us to scope by a second field. I've attached a screenshot showing the structure for locationVariantStock. So should I still be applying 'Uniqueness' to a field, and then in my code should I be doing:

api.locationVariantStock.upsert({
  ...data,
  on: ['date', 'variant', 'location']
});

api.locationVariantStock.upsert({
  ...data,
  on: ['date', 'variant', 'location']
});

ljspoor94OP•7mo ago

Following on, I am using the internal api for these too so I'm not sure if the uniqueness validation will even matter..so on that assumption, can I just pass those 3 fields in on and I should be good to go?

ljspoor94OP•7mo ago

So looking at locationProductStock for now, which requires on: ['product', 'location', 'date'].

await api.internal.locationProductStock.upsert({
  ...locationProductStock,
  date: location.date,
  on: ['product', 'location', 'date']
});

await api.internal.locationProductStock.upsert({
  ...locationProductStock,
  date: location.date,
  on: ['product', 'location', 'date']
});

I've only selected Uniqueness against the product field but it doesn't seem to like that. And based off that error its only expecting product to be the unique field. But with the UI options we have we can only select 2 fields? So what can I do here?

ljspoor94OP•7mo ago

If you want to take a look at my setup, its the locationProductStock on the app stock-monitor in the multi-location environment.

Chocci_Milk•7mo ago

Its a belongsTo relationship so you need to use the id field (productId)

ljspoor94OP•7mo ago

It belongs to 3 models though And as mentioned, I have a record per shop, per location, per day Either way, I'm going to take another approach suggested by @Aurélien (quasar.work) And add a new field called key and do something like ${locationId}-${variantId}-${date} for it, then I can just do on: ['key']

ljspoor94OP•7mo ago

Not quite sure what this means? I'm just using the key field now, and I've set the uniqueness validation on that field too

const locationProductStock = {
  shop: location.shop,
  product: location.product,
  location: location.location,
  available: location.available,
  quantity: location.quantity,
  inventoryValue: location.inventoryValue,
  inventoryRetailValue: location.inventoryRetailValue,
  minPrice: location.minPrice,
  maxPrice: location.maxPrice,
  minCost: location.minCost,
  maxCost: location.maxCost,
  totalVariants: location.totalVariants,
  variantsOutOfStock: location.variantsOutOfStock,
  key: `${location.location._link}-${location.product._link}-${location.date}`,
  date: location.date
};

try {
  await api.internal.locationProductStock.upsert({
    ...locationProductStock,
    on: ['key']
  });
} catch (error) {
  logger.error(
    {
      source: 'shopifyProduct/actions/upsertProductStock.js',
      function: 'processLocation',
      error,
      locationId: location.location._link,
      productId: location.product._link
    },
    'Failed to create location product stock count'
  );
}

const locationProductStock = {
  shop: location.shop,
  product: location.product,
  location: location.location,
  available: location.available,
  quantity: location.quantity,
  inventoryValue: location.inventoryValue,
  inventoryRetailValue: location.inventoryRetailValue,
  minPrice: location.minPrice,
  maxPrice: location.maxPrice,
  minCost: location.minCost,
  maxCost: location.maxCost,
  totalVariants: location.totalVariants,
  variantsOutOfStock: location.variantsOutOfStock,
  key: `${location.location._link}-${location.product._link}-${location.date}`,
  date: location.date
};

try {
  await api.internal.locationProductStock.upsert({
    ...locationProductStock,
    on: ['key']
  });
} catch (error) {
  logger.error(
    {
      source: 'shopifyProduct/actions/upsertProductStock.js',
      function: 'processLocation',
      error,
      locationId: location.location._link,
      productId: location.product._link
    },
    'Failed to create location product stock count'
  );
}

Am I missing something?

ljspoor94OP•7mo ago

This has only happened on a select few records though, FYI I started with an empty table for this model, no data existed prior to running this. Had no idea what defunct even meant until I just looked.. 😂