How to reorder all elements of an orderable 1-to-n relationship?

Data Services
, , ,
1

Given an orderable 1-to-n relationship between document models how can I reorder all of the elements on the n-side?

We had a bug in our application that caused the role order of the n-side of the relationship to be persisted incorrectly. For a known set of documents of the 1-side we need to fix the order so that it reflects the ordering of the createdAt date of those documents. How can we achieve this?

2

This can be achieved via executing the following SQL

update relationship_order ro 
   set role_order = role_new_order.new_role_order 
  from (select role_createdat_index.target_role_id target_role_id,
               role_createdat_index.source_role_id source_role_id,
               'sssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss' || chr(ascii('a') + role_createdat_index.rnum::int - 1) as new_role_order
          from (select rst.target_role_id,
                       rst.source_role_id,
                       d_right.content::jsonb->'__meta'->>'createdAt' as target_doc_created_at,
                       row_number() OVER (PARTITION BY rst.source_docref 
                                              ORDER BY d_right.content::jsonb->'__meta'->>'createdAt', 
                                                       rst.target_role_id) AS rnum
                  from relationship_source_target rst
                  join relationship_role rr_left on rr_left.id = rst.source_role_id
                  join document d_right on rst.target_docref = d_right.model_name || '/' || d_right.id
                 where rst.source_docref in ('YourDM/ids-inserted-here', 'FooDM/1234-5678-90ab', 'BarDM/abcd-abcd-abcd')
               ) role_createdat_index
  )  role_new_order where ro.id = role_new_order.source_role_id or ro.id=role_new_order.target_role_id

This uses the view from How to find all documents that are related to a specific one?.

The approach with calculating the new role_order value is limited to a max of 26 elements on the n-side because of the limited size of the supported alphabet for that column value. The relationship_role.id of the target value is used as a secondary ordering criteria to avoid conflicts when documents where created within the same second.

For our application we had to reorder all elements in a multi-hierarchical domain where we could use the transitive_relationship_source_target view also mentioned in that other post. Therefore the where clause

                 where rst.source_docref in ('YourDM/ids-inserted-here', 'FooDM/1234-5678-90ab', 'BarDM/abcd-abcd-abcd')

needed to be replaced by

                 where rst.source_docref in (select tr.target_docref 
                                               from transitive_relationship_source_target tr 
                                              where tr.source_docref in ('YourDM/ids-inserted-here', 'FooDM/1234-5678-90ab', 'BarDM/abcd-abcd-abcd') 
                                              union 
                                             select unnest(ARRAY['YourDM/ids-inserted-here', 'FooDM/1234-5678-90ab', 'BarDM/abcd-abcd-abcd'])