CODEWITHSUNDEEP
postgresql
Vikram Khemlani
Vikram Khemlani

Reputation: 635

How to add record when parent record may or may not exist

I have three tables in a Postgresql database

User -> Cart -> CartItem

Each User has many Carts and each Cart has many CartItems, but each cart belongs to one user and each cartItem belongs to one cart. I have a query where I want to add CartItem but there are three cases: First, the Cart may not exist, in which I will create the Cart, then create the CartItem Second, the Cart may exist and the CartItem is a new cart Item (not editing a previous cart Item), in which case I would just add the cart Item Third, the Cart may exist and the CartItem may be a modification of an existing CartItem, in which the new CartItem object will have an id and Cart_id to allow it to replace the existing CartItem

I had two questions, in order of importance and kind of tied together. One, in the first scenario, the way I am handling it is that I am checking if there is a cart in one query, then creating one if there isnt one, then adding the Cart item with a foreign key reference to the created Cart. However, since its more than one query, (first to check then to create) I am afraid because of race conditions, two carts can be created (different ids but both referencing the same user and store). I can't select for update since it doesnt exist so Im not sure how to handle it.

My second question is, is there one or two queries that can do all of this at once (Insert cart if it doesnt exist, returning the cart id so I can append it to the object, then insert cart item if it doesnt exist)

Upvotes: 0

Views: 56

Answers (1)

LittleXenomorph
LittleXenomorph

Reputation: 325

I would suggest experimenting with a single SELECT QUERY with LEFT JOINs which extracts the joined data for user, his cart and cartitems. If there is not Cart or CartItem available, you will receive null values for the respective attributes. That way you can directly react to the missing table rows and create them if neccessary.

Regarding your second question: inserting multiple values on the fly is possible with WITH {...} INSERT... RETURNING (or with procedures). Something like this e.g.:

with new_cart as (
  insert into cart
     (user_id, creatiom_date) 
  values
     (current_user_id, now())
  returning id as cart_id
)
insert into cart_item
  (cart_id, item_id, count) 
values
  (cart_id, seleted_item_id, 2);

Upvotes: 1

Related Questions