webmcp
annotate 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 |
| 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 |