1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/framework/env/param/update_relationship.lua	Sun Oct 25 12:00:00 2009 +0100
     1.3 @@ -0,0 +1,57 @@
     1.4 +--[[--
     1.5 +param.update_relationship{
     1.6 +  param_name        = param_name,        -- name of request GET/POST request parameters containing primary keys for model B
     1.7 +  id                = id,                -- value of the primary key for model A
     1.8 +  connecting_model  = connecting_model,  -- model used for creating/deleting entries referencing both model A and B
     1.9 +  own_reference     = own_reference,     -- field name for foreign key in the connecting model referencing model A 
    1.10 +  foreign_reference = foreign_reference  -- field name for foreign key in the connecting model referencing model B
    1.11 +}
    1.12 +
    1.13 +This function updates a many-to-many relationship using a specified 'connecting_model' referencing both models.
    1.14 +
    1.15 +--]]--
    1.16 +
    1.17 +function param.update_relationship(args)
    1.18 +  local param_name        = args.param_name
    1.19 +  local id                = args.id
    1.20 +  local connecting_model  = args.connecting_model
    1.21 +  local own_reference     = args.own_reference
    1.22 +  local foreign_reference = args.foreign_reference
    1.23 +  local selected_ids = param.get_list(param_name, atom.integer)  -- TODO: support other types than integer too
    1.24 +  local db    = connecting_model:get_db_conn()
    1.25 +  local table = connecting_model:get_qualified_table()
    1.26 +  if #selected_ids == 0 then
    1.27 +    db:query{
    1.28 +      'DELETE FROM ' .. table .. ' WHERE "' .. own_reference .. '" = ?',
    1.29 +      args.id
    1.30 +    }
    1.31 +  else
    1.32 +    local selected_ids_sql = { sep = ", " }
    1.33 +    for idx, value in ipairs(selected_ids) do
    1.34 +      selected_ids_sql[idx] = {"?::int8", value}
    1.35 +    end
    1.36 +    db:query{
    1.37 +      'DELETE FROM ' .. table ..
    1.38 +      ' WHERE "' .. own_reference .. '" = ?' ..
    1.39 +      ' AND NOT "' .. foreign_reference .. '" IN ($)',
    1.40 +      args.id,
    1.41 +      selected_ids_sql
    1.42 +    }
    1.43 +    -- TODO: use VALUES SQL command, instead of this dirty array trick
    1.44 +    db:query{
    1.45 +      'INSERT INTO ' .. table ..
    1.46 +      ' ("' .. own_reference .. '", "' .. foreign_reference .. '")' ..
    1.47 +      ' SELECT ?, "subquery"."foreign" FROM (' ..
    1.48 +        'SELECT (ARRAY[$])[i] AS "foreign"' ..
    1.49 +        ' FROM generate_series(1, ?) AS "dummy"("i")' ..
    1.50 +        ' EXCEPT SELECT "' .. foreign_reference .. '" AS "foreign"' ..
    1.51 +        ' FROM ' .. table ..
    1.52 +        ' WHERE "' .. own_reference .. '" = ?' ..
    1.53 +      ') AS "subquery"',
    1.54 +      args.id,
    1.55 +      selected_ids_sql,
    1.56 +      #selected_ids,
    1.57 +      args.id
    1.58 +    }
    1.59 +  end
    1.60 +end