jbe/bsw@0: --[[--
jbe/bsw@0: param.update_relationship{
jbe/bsw@0:   param_name        = param_name,        -- name of request GET/POST request parameters containing primary keys for model B
jbe/bsw@0:   id                = id,                -- value of the primary key for model A
jbe/bsw@0:   connecting_model  = connecting_model,  -- model used for creating/deleting entries referencing both model A and B
jbe/bsw@0:   own_reference     = own_reference,     -- field name for foreign key in the connecting model referencing model A 
jbe/bsw@0:   foreign_reference = foreign_reference  -- field name for foreign key in the connecting model referencing model B
jbe/bsw@0: }
jbe/bsw@0: 
jbe/bsw@0: This function updates a many-to-many relationship using a specified 'connecting_model' referencing both models.
jbe/bsw@0: 
jbe/bsw@0: --]]--
jbe/bsw@0: 
jbe/bsw@0: function param.update_relationship(args)
jbe/bsw@0:   local param_name        = args.param_name
jbe/bsw@0:   local id                = args.id
jbe/bsw@0:   local connecting_model  = args.connecting_model
jbe/bsw@0:   local own_reference     = args.own_reference
jbe/bsw@0:   local foreign_reference = args.foreign_reference
jbe/bsw@0:   local selected_ids = param.get_list(param_name, atom.integer)  -- TODO: support other types than integer too
jbe/bsw@0:   local db    = connecting_model:get_db_conn()
jbe/bsw@0:   local table = connecting_model:get_qualified_table()
jbe/bsw@0:   if #selected_ids == 0 then
jbe/bsw@0:     db:query{
jbe/bsw@0:       'DELETE FROM ' .. table .. ' WHERE "' .. own_reference .. '" = ?',
jbe/bsw@0:       args.id
jbe/bsw@0:     }
jbe/bsw@0:   else
jbe/bsw@0:     local selected_ids_sql = { sep = ", " }
jbe/bsw@0:     for idx, value in ipairs(selected_ids) do
jbe/bsw@0:       selected_ids_sql[idx] = {"?::int8", value}
jbe/bsw@0:     end
jbe/bsw@0:     db:query{
jbe/bsw@0:       'DELETE FROM ' .. table ..
jbe/bsw@0:       ' WHERE "' .. own_reference .. '" = ?' ..
jbe/bsw@0:       ' AND NOT "' .. foreign_reference .. '" IN ($)',
jbe/bsw@0:       args.id,
jbe/bsw@0:       selected_ids_sql
jbe/bsw@0:     }
jbe/bsw@0:     -- TODO: use VALUES SQL command, instead of this dirty array trick
jbe/bsw@0:     db:query{
jbe/bsw@0:       'INSERT INTO ' .. table ..
jbe/bsw@0:       ' ("' .. own_reference .. '", "' .. foreign_reference .. '")' ..
jbe/bsw@0:       ' SELECT ?, "subquery"."foreign" FROM (' ..
jbe/bsw@0:         'SELECT (ARRAY[$])[i] AS "foreign"' ..
jbe/bsw@0:         ' FROM generate_series(1, ?) AS "dummy"("i")' ..
jbe/bsw@0:         ' EXCEPT SELECT "' .. foreign_reference .. '" AS "foreign"' ..
jbe/bsw@0:         ' FROM ' .. table ..
jbe/bsw@0:         ' WHERE "' .. own_reference .. '" = ?' ..
jbe/bsw@0:       ') AS "subquery"',
jbe/bsw@0:       args.id,
jbe/bsw@0:       selected_ids_sql,
jbe/bsw@0:       #selected_ids,
jbe/bsw@0:       args.id
jbe/bsw@0:     }
jbe/bsw@0:   end
jbe/bsw@0: end