4

I have done this before, but before I mindlessly repeat something that I consider as a hack, I'm asking here.

I have 3 tables (details left out for clarity) :

inv_items

id bigserial (PK)
sku character varying(24)
name character varying(32)
...

inv_item_groups

id bigserial (PK)
name cahracter varying(32)
...

inv_item_group_members

item_group_id bigint (FK -> inv_item_groups)
item_id bigint (FK -> inv_items)

Now, in my code, I have an object like so (in pseudo-code)

class ItemGroup
   id:long
   groupName:String
   items:long[]

and these objects can be modified, then needs to be updated. Since I want to preserve the key integrity, I need the inv_item_group_members table (otherwise, I would've used other solutions).

Now, the usual way I was doing this was to

DELETE FROM inv_item_group_members WHERE item_group_id = $1
-- where $1 is the object's id

the, for each items in the object

INSERT INTO inv_item_group_members (item_group_id, item_id) VALUES ($1, $2)

Is there a better solution? What are the alternative? I'm thinking of a SQL function, but not really sure what's the best approach here (I'm not very experienced with PGSQL, yet.) I have read about writable CTE, but it does not address the case when elements are removed from the array (i.e. association removed).

2

My suggestion is that you only delete from inv_item_group_members the items you deleted from the object and only insert into it the new items you added to the object.

You can do that by creating an itemsToDelete array, which you populate with item_ids each time you call the removeItem method of the class and an itemsToInsert which you populate each time you add an item whith the addItem method.

Then when you call the save method you delete the items whose item_id are present in the itemsToDelete array (obviously restricted also to group_id). Then you proceed to insert items present in the itemsToInsert array.

To make it resilient, you can either use a Map instead of arrays in order to prevent duplicates, or catch and ignore PK exceptions during the insert.

  • Right. I might try that. My model already has a _previousData, which is the original data, fetched from the database. I'm just worried that it's not as safe as I'd like it to be (i.e. race conditions), but I'll try that. – Yanick Rochon Jun 19 '14 at 16:34
0

I have found a working implementation of an "UPSERT" command. So far, it works fine.

WITH upsert AS (UPDATE inv_items_group_members
                   SET priority = $3
                 WHERE group_id = $1
                   AND item_id = $2
             RETURNING *)
INSERT INTO inv_items_group_members 
            (group_id, item_id, priority) 
     SELECT $1, $2, $3 WHERE NOT EXISTS (SELECT * FROM upsert)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.