| rev |
line source |
|
jbe/bsw@0
|
1 --[[--
|
|
jbe/bsw@0
|
2 param.update_relationship{
|
|
jbe/bsw@0
|
3 param_name = param_name, -- name of request GET/POST request parameters containing primary keys for model B
|
|
jbe/bsw@0
|
4 id = id, -- value of the primary key for model A
|
|
jbe/bsw@0
|
5 connecting_model = connecting_model, -- model used for creating/deleting entries referencing both model A and B
|
|
jbe/bsw@0
|
6 own_reference = own_reference, -- field name for foreign key in the connecting model referencing model A
|
|
jbe/bsw@0
|
7 foreign_reference = foreign_reference -- field name for foreign key in the connecting model referencing model B
|
|
jbe/bsw@0
|
8 }
|
|
jbe/bsw@0
|
9
|
|
jbe/bsw@0
|
10 This function updates a many-to-many relationship using a specified 'connecting_model' referencing both models.
|
|
jbe/bsw@0
|
11
|
|
jbe/bsw@0
|
12 --]]--
|
|
jbe/bsw@0
|
13
|
|
jbe/bsw@0
|
14 function param.update_relationship(args)
|
|
jbe/bsw@0
|
15 local param_name = args.param_name
|
|
jbe/bsw@0
|
16 local id = args.id
|
|
jbe/bsw@0
|
17 local connecting_model = args.connecting_model
|
|
jbe/bsw@0
|
18 local own_reference = args.own_reference
|
|
jbe/bsw@0
|
19 local foreign_reference = args.foreign_reference
|
|
jbe/bsw@0
|
20 local selected_ids = param.get_list(param_name, atom.integer) -- TODO: support other types than integer too
|
|
jbe/bsw@0
|
21 local db = connecting_model:get_db_conn()
|
|
jbe/bsw@0
|
22 local table = connecting_model:get_qualified_table()
|
|
jbe/bsw@0
|
23 if #selected_ids == 0 then
|
|
jbe/bsw@0
|
24 db:query{
|
|
jbe/bsw@0
|
25 'DELETE FROM ' .. table .. ' WHERE "' .. own_reference .. '" = ?',
|
|
jbe/bsw@0
|
26 args.id
|
|
jbe/bsw@0
|
27 }
|
|
jbe/bsw@0
|
28 else
|
|
jbe/bsw@0
|
29 local selected_ids_sql = { sep = ", " }
|
|
jbe/bsw@0
|
30 for idx, value in ipairs(selected_ids) do
|
|
jbe/bsw@0
|
31 selected_ids_sql[idx] = {"?::int8", value}
|
|
jbe/bsw@0
|
32 end
|
|
jbe/bsw@0
|
33 db:query{
|
|
jbe/bsw@0
|
34 'DELETE FROM ' .. table ..
|
|
jbe/bsw@0
|
35 ' WHERE "' .. own_reference .. '" = ?' ..
|
|
jbe/bsw@0
|
36 ' AND NOT "' .. foreign_reference .. '" IN ($)',
|
|
jbe/bsw@0
|
37 args.id,
|
|
jbe/bsw@0
|
38 selected_ids_sql
|
|
jbe/bsw@0
|
39 }
|
|
jbe/bsw@0
|
40 -- TODO: use VALUES SQL command, instead of this dirty array trick
|
|
jbe/bsw@0
|
41 db:query{
|
|
jbe/bsw@0
|
42 'INSERT INTO ' .. table ..
|
|
jbe/bsw@0
|
43 ' ("' .. own_reference .. '", "' .. foreign_reference .. '")' ..
|
|
jbe/bsw@0
|
44 ' SELECT ?, "subquery"."foreign" FROM (' ..
|
|
jbe/bsw@0
|
45 'SELECT (ARRAY[$])[i] AS "foreign"' ..
|
|
jbe/bsw@0
|
46 ' FROM generate_series(1, ?) AS "dummy"("i")' ..
|
|
jbe/bsw@0
|
47 ' EXCEPT SELECT "' .. foreign_reference .. '" AS "foreign"' ..
|
|
jbe/bsw@0
|
48 ' FROM ' .. table ..
|
|
jbe/bsw@0
|
49 ' WHERE "' .. own_reference .. '" = ?' ..
|
|
jbe/bsw@0
|
50 ') AS "subquery"',
|
|
jbe/bsw@0
|
51 args.id,
|
|
jbe/bsw@0
|
52 selected_ids_sql,
|
|
jbe/bsw@0
|
53 #selected_ids,
|
|
jbe/bsw@0
|
54 args.id
|
|
jbe/bsw@0
|
55 }
|
|
jbe/bsw@0
|
56 end
|
|
jbe/bsw@0
|
57 end
|