optionSet api return null in the options

Support - Assistance technique
1

the api was returning an array of 2 null inside it

{
 "name": "Dep - ONG",
 "options": [
   null, 
   {
   "id": "OtyeXdg52cd"
   },
    null, 
   {
   "id": "Si1RToTrYo5"
   }
 ],
 "id": "Vwlv1BphHUD"
}

and trying to access the optionset via maintenance page was not working

looking into the db I noticed gap in the sort_order

select * from optionvalue where optionsetid = 121

optionvalueid  sort_order

  117                2
  118                4

I removed the gap between the sort_order

update optionvalue set sort_order = 1 where optionvalueid = 117
update optionvalue set sort_order = 2 where optionvalueid = 118

restarted dhis2 and it solved the problem.

I fear this happen again, is this a known bug ?
This happened on a 2.39.2.1

2

Although it’s a different case, but it happened before here where null was being added and it got fixed. However, for that issue it was possible to replicate/reproduce the issue. Do you think there’s a way to reproduce this issue?

Thanks!

3

from what I see in your comment, it has not been retro-fixed in 2.39 ?

4

Thanks! Yes, you are right. It has been fixed for version 40.1 as for version 2.39 it will be available in version 2.39.3 once it is released.

1 Like
5

We noticed this bug too, and we used this function to fix it, as having non-consecutive sort orders is generally something worth fixing.

Note that some tables have sort orders that begin with 0 and some have sort orders that begin with 1, hence the need for the third parameter in the SQL function.

Please use this with caution! (As you would any other modifications to the database).

begin;

create temporary table if not exists _fixsortorder (temp integer) on commit drop;

create or replace function fixSortOrder
  (tbl text, key text, start integer) returns boolean as
$$
declare
  resortObject record;
begin
  execute format('insert into _fixsortorder (select distinct %s from %s)', key, tbl);
  for resortObject in (select temp from _fixsortorder) loop
    execute format('update %s
                    set sort_order = -t.i
                    from (select row_number() over (order by sort_order) as i, %s, sort_order
                            from %s where %s = %s order by sort_order) t
                    where %s.%s = %s and t.sort_order = %s.sort_order', 
            tbl, key, tbl, key, resortObject.temp, tbl, key, resortObject.temp, tbl);
  end loop;
  execute format('update %s set sort_order = -(sort_order + 1) + %s', tbl, start);
  execute 'truncate _fixsortorder';
  return true;
end;
$$
language plpgsql volatile;

select fixsortorder('categoryoptiongroupsetmembers', 'categoryoptiongroupsetid', 1);

drop function if exists fix_sort_order(tbl text, key text, start integer);

commit;
2 Likes