webmcp

view framework/env/param/update_relationship.lua @ 567:5e3ebe9fd0ce

Added tag v2.2.1 for changeset 3b71fdb3a00d
author jbe
date Wed Apr 28 13:07:52 2021 +0200 (5 months ago)
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

Impressum / About Us