Hi

There is an actual Perl context, I'm representing (kind of) a HoAoHoA tree structure in an SQL-table (so it's not totally off-topic).

The array part is using a field f_sort to represent the order.

For instance: (the parent_id=2 identifies a particular array inside the tree)

f_node_id	f_parent_id	f_name	f_sort
142	2	B06	1
143	2	B2L	2
144	2	B2M	3
145	2	B2N	4
146	2	B2O	5

Now I have the requirement to delete , insert and move ° multiple elements in this "array", which of course involves renumbering f_sort .

I wanted to be clever and avoid thinking about all involved edge cases, and changed the type of f_sort to float, such that I can just move new elements in between two elements as fractions. (the monastery is using a similar concept for sorting in user settings)

f_node_id	f_parent_id	f_name	f_sort
142	2	B06	1
143	2	B2L	2
394	2	*Inserted*	2.5
144	2	B2M	3
145	2	B2N	4
146	2	B2O	5

And after each operation I would just run one SQL statement to "refresh" the f_sort index to integers starting from 1.

SET @sort = 0;

UPDATE t_tree AS T
SET f_sort = (@sort := @sort +1)
WHERE T.f_parent_id = 2
ORDER BY f_sort;
[download]

Now this fails since (f_parent_id, f_sort) are set to be unique, i.e. numbering the inserted element 394 to f_sort :=3 will clash with the already available element 144 with f_sort=3 (and so on)

Of course I could start here sorting from the end, but this contradicts the intention to avoid creating code for each case.

A workaround is to multiply each f_sort with -1 before re-sorting, because new indices can't clash with negative numbers.

But this feels quite clumsy.

I could achieve the same effect with an extra boolean column f_is_currently_sorting and add it to the "unique index" (it would reflect the "negativity" of -1) ... at least the sort order would stay correct in every phase. But still.

Any better ideas for that approach?

°) move in the sense of cut&paste or copy&paste of multiple elements, be from the same or from another array

update

I was asked

> I'm not clear on what you're wanting to do. Can you add a bit of information on what the sort criteria are?

ORDER BY f_sort WHERE f_parent_id = ?

f_node_id    is the primary key
f_parent_id  is the node_id of an upper structure
f_name       is the key of a hash if f_sort= NULL, 
             otherwise it's just an optional "name" of a array element
+*
f_sort       is the array index and NULL for hashes
[download]

so yes the sort criteria is actually

ORDER BY f_sort,f_name WHERE f_parent_id = ?

but the hash logic is not relevant for this question.

*) think sorted hashes, or representing JSON, where some RFC require that the order of keys matters

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.