1. Michael Bayer
  2. sqlalchemy
  3. Issues


Issue #3003 resolved

Update of orderinglist relationship when position is used in PK

created an issue

Sometimes the only choice for primary key of objects in private relationship is parent ID and position in list pair. But update of such property doesn't work: property after flush contains other objects than where assigned to it.

Comments (12)

  1. Michael Bayer repo owner

    Haven't looked deeply yet, but I see "ordering list" and "primary key", a lot of these issues aren't fixable, I'm sure you've seen http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/orderinglist.html :


    OrderingList only provides limited functionality when a primary key column or unique column is the target of the sort. Since changing the order of entries often means that two rows must trade values, this is not possible when the value is constrained by a primary key or unique constraint, since one of the rows would temporarily have to point to a third available value so that the other row could take its old value. OrderingList doesn’t do any of this for you, nor does SQLAlchemy itself.

    is this issue just another restatement of that? orderinglist only goes so far and I don't really have any great way to improve it in this regard.

  2. ods reporter

    Sorry, I havn't seen the warning. I agree with you that sometimes it's not possible to meet unique constraint when order is changed (it's agruable for PK: we can see it as order change or just values swap). In my test case all objects in the assigned list are new, so I see no reason to handle it as reorder.

  3. Michael Bayer repo owner

    OK, it would take me some time to understand the issue and at the end it's probably some variant of that problem in any case, is there no way you can just work around this or attempt to fix ? that would be very helpful.

  4. Michael Bayer repo owner

    my first observation is that you're expecting the "d" value to stay the same? I'm reading what ordering list does (I didn't write it) and it says: "automatically synchronize changes in list position onto a target scalar attribute." Doesn't that mean you at the very least need to provide "ordering_func" to do alphabetical ordering, and that you'd want this?

    p.children = [Child(data='d')]
    assert p.children == [Child(data='a')]
  5. ods reporter

    No, 'a', 'b', 'c', etc. are just handy values for testing to see what's going on. I need to keep the same order as it was when property was assigned. And I forced to use position is PK since there is no other candidate (all the rest fields are not unique, most are nullable) and using autoincremented id will increase database fragmentation on each update. Also don't want to use serialization (like pickle or JSON) since I have a field with foreign key constraint.

  6. Michael Bayer repo owner

    oh, data is not the ordering attribute. totally confusing example, still having trouble understanding what the problem is. this is taking me all morning

  7. Michael Bayer repo owner

    it seems like the deletes of old records fails because as they are removed, their ordering position changes, so the primary keys are wrong and the rows are missed. This is exactly why the warning is there, I don't know how to solve this and I didn't write this extension in the first place. Please don't use primary keys with ordering list.

  8. Log in to comment