1 --[[--

2 param.update_relationship{

3 param_name = param_name, -- name of request GET/POST request parameters containing primary keys for model B

4 id = id, -- value of the primary key for model A

5 connecting_model = connecting_model, -- model used for creating/deleting entries referencing both model A and B

6 own_reference = own_reference, -- field name for foreign key in the connecting model referencing model A

7 foreign_reference = foreign_reference -- field name for foreign key in the connecting model referencing model B

8 }

9

10 This function updates a many-to-many relationship using a specified 'connecting_model' referencing both models.

11

12 --]]--

13

14 function param.update_relationship(args)

15 local param_name = args.param_name

16 local id = args.id

17 local connecting_model = args.connecting_model

18 local own_reference = args.own_reference

19 local foreign_reference = args.foreign_reference

20 local selected_ids = param.get_list(param_name, atom.integer) -- TODO: support other types than integer too

21 local db = connecting_model:get_db_conn()

22 local table = connecting_model:get_qualified_table()

23 if #selected_ids == 0 then

24 db:query{

25 'DELETE FROM ' .. table .. ' WHERE "' .. own_reference .. '" = ?',

26 args.id

27 }

28 else

29 local selected_ids_sql = { sep = ", " }

30 for idx, value in ipairs(selected_ids) do

31 selected_ids_sql[idx] = {"?::int8", value}

32 end

33 db:query{

34 'DELETE FROM ' .. table ..

35 ' WHERE "' .. own_reference .. '" = ?' ..

36 ' AND NOT "' .. foreign_reference .. '" IN ($)',

37 args.id,

38 selected_ids_sql

39 }

40 -- TODO: use VALUES SQL command, instead of this dirty array trick

41 db:query{

42 'INSERT INTO ' .. table ..

43 ' ("' .. own_reference .. '", "' .. foreign_reference .. '")' ..

44 ' SELECT ?, "subquery"."foreign" FROM (' ..

45 'SELECT (ARRAY[$])[i] AS "foreign"' ..

46 ' FROM generate_series(1, ?) AS "dummy"("i")' ..

47 ' EXCEPT SELECT "' .. foreign_reference .. '" AS "foreign"' ..

48 ' FROM ' .. table ..

49 ' WHERE "' .. own_reference .. '" = ?' ..

50 ') AS "subquery"',

51 args.id,

52 selected_ids_sql,

53 #selected_ids,

54 args.id

55 }

56 end

57 end