webmcp
view framework/env/param/update_relationship.lua @ 3:795b764629ca
Version 1.0.3
Important bugfix related to internal forwards (Bug was introduced by the restriction of views with underscore prefix in Version 1.0.2)
Important bugfix related to internal forwards (Bug was introduced by the restriction of views with underscore prefix in Version 1.0.2)
| author | jbe |
|---|---|
| date | Thu Dec 10 12:00:00 2009 +0100 (2009-12-10) |
| parents | 9fdfb27f8e67 |
| children |
line source
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 }
10 This function updates a many-to-many relationship using a specified 'connecting_model' referencing both models.
12 --]]--
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