Login

Ordered items in the database - alternative

Author:
Leonidas
Posted:
June 3, 2007
Language:
Python
Version:
.96
Score:
6 (after 6 ratings)

Every now and then you need to have items in your database which have a specific order. As SQL does not save rows in any order, you need to take care about this for yourself. No - actually, you don't need to anymore. You can just use this file - it is designed as kind-of plug-in for the Django ORM.

Usage is (due to use of meta-classes) quite simple. It is recommended to save this snippet into a separate file called positional.py. To use it, you only have to import PositionalSortMixIn from the positional module and inherit from it in your own, custom model (but before you inherit from models.Model, the order counts).

Usage example: Add this to your models.py

from positional import PositionalSortMixIn

class MyModel(PositionalSortMixIn, models.Model):
    name = models.CharField(maxlength=200, unique=True)

Now you need to create the database tables: PositionalSortMixIn will automatically add a postition field to your model. In your views you can use it simply with MyModel.objects.all().order_by('position') and you get the objects sorted by their position. Of course you can move the objects down and up, by using move_up(), move_down() etc.

In case you feel you have seen this code somewhere - right, this snippet is a modified version of snippet #245 which I made earlier. It is basically the same code but uses another approach to display the data in an ordered way. Instead of overriding the Manager it adds the position field to Meta.ordering. Of course, all of this is done automatically, you only need to use YourItem.objects.all() to get the items in an ordered way.

Update: Now you can call your custom managers object as long as the default manager (the one that is defined first) still returns all objects. This Mix-in absolutely needs to be able to access all elements saved.

In case you find any errors just write a comment, updated versions are published here from time to time as new bugs are found and fixed.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
#!/usr/bin/env python
# -*- coding: utf-8 -*-
from django.db import models, transaction
from django.db.models.fields import FieldDoesNotExist

class InjectingModelBase(models.base.ModelBase):
    """This helper metaclass is used by PositionalSortMixIn.
    This metaclass injects the additional IntegerField position,
    which holds the information about the position of the item
    in the list."""

    def __new__(cls, name, bases, attrs):
        """Metaclass constructor calling Django and then modifying
        the resulting class"""
        # get the class which was alreeady built by Django
        child = models.base.ModelBase.__new__(cls, name, bases, attrs)

        # try to add some more or less neccessary fields
        try:
            # add the IntegerField
            position_field = models.IntegerField(editable=False, unique=True)
            try:
                # try to get the `position` field
                child._meta.get_field('position')
            except FieldDoesNotExist:
                # it was not found - create it now
                child.add_to_class('position', position_field)

        except AttributeError:
            # add_to_class was not yet added to the class.
            # No problem, this is called twice by Django, add_to_class 
            # will appear later
            pass
        # we're done - output the class, it's ready for use
        return child

class PositionalSortMixIn(object):
    """This mix-in class implements a user defined order in the database.
    To apply this mix-in you need to inherit from it before you inherit
    from `models.Model`. It adds an IntegerField called `position` to your 
    model. Be careful, it overwrites any existing field that you might have 
    defined. Additionally, this mix-in changes the default ordering
    behavior to order by the position field.
    
    Take care: your model needs to have a manager which returns all objects
    set as default manager, that is, the first defined manager. It does not
    need tp be named `objects`. Future versions of this Mixin may inject its
    own, private manager"""
    # get a metaclass which injects the neccessary fields
    __metaclass__ = InjectingModelBase

    def __init__(self, *args, **kwargs):
        """Initialize the class and set up some magic"""
        # call the parent - the ensure it behaves exactly like 
        # the original model
        models.Model.__init__(self, *args, **kwargs)

        # set position as the first field to order.
        # of course this gets overridden when using database queries which
        # request another ordering method (order_by)
        if 'position' not in self._meta.ordering:
            self._meta.ordering = ['position'] + list(self._meta.ordering)

    def get_object_at_offset(self, offset):
        """Get the object whose position is `offset` positions away 
        from it's own."""
        # get the class in which this was mixed in
        model_class = self.__class__
        try:
            return model_class._default_manager.get(position=self.position+offse
t)
        except model_class.DoesNotExist:
            # no such model? no deal, just return None
            return None

    # some shortcuts, convenience methods
    get_next = lambda self: self.get_object_at_offset(1)
    get_previous = lambda self: self.get_object_at_offset(-1)

    @transaction.commit_on_success
    def move_down(self):
        """Moves element one position down"""
        model_class = self.__class__
        # get the element after this one
        one_after = self.get_next()

        if not one_after:
            # already the last element
            return

        # flip the positions
        # a spare position field is needed
        final = model_class._default_manager.all().order_by('-position')[0].posi
tion + 1
        # move the object after this one to the bottom of the list
        one_after.position = final
        one_after.save()
        # move this object one down
        self.position += 1
        self.save()
        # move the element that was after this one before this one now
        one_after.position = self.position - 1
        one_after.save()

    @transaction.commit_on_success
    def move_up(self):
        """Moves element one position up"""
        model_class = self.__class__
        # get the element before this one
        one_before = self.get_previous()

        if not one_before:
            # already the first
            return

        # flip the positions:
        # first get a spare position field
        final = model_class._default_manager.all().order_by('-position')[0].posi
tion + 1
        # move the object that was before to the exact last position
        one_before.position = final
        one_before.save()
        # now, move this object one position up
        self.position -= 1
        self.save()
        # finally, move the object that was before to the position after this ob
ject
        one_before.position = self.position + 1
        one_before.save()

    @transaction.commit_on_success
    def insert_after(self, other):
        """Inserts an object in the database so that the objects will be ordered
 just
        behind the `other` object - this has to be of the same type, of course""
"
        # we only need to call another method and prepare the proper parameters
        self.insert_at(other.position + 1)

    @transaction.commit_on_success
    def insert_at(self, position):
        """Saves the object at a specified position.
        The `position` field """
        # get the class reference
        model_class = self.__class__
        # the position in what we want to put this in
        self.position = position
        # get all the objects which are at or after the currrent position
        # order them from back to front because of the unique `position` constra
int
        objects_after = model_class._default_manager.filter(position__gte=positi
on).order_by('-position')
        for element in objects_after:
            element.position += 1
            element.save()
        self.save()

    @transaction.commit_on_success
    def swap_position(self, other):
        """Swaps the position with some other class instance"""
        # save the current position
        current_position = self.position
        # set own position to special, temporary position
        self.position = -1
        self.save()
        self.position, other.position = other.position, current_position
        for obj in (other, self):
            obj.save()

    def save(self):
        """Saves the model to the database.
        It populates the `position` field of the model automatically if there 
        is no such field set. In this case, the element will be appended at
        the end of the list."""
        model_class = self.__class__
        # is there a position saved? (explicitly testing None because 0 would be
 false as well)
        if self.position == None:
            # no, it was empty. Find one
            try:
                # get the last object
                last = model_class._default_manager.all().order_by('-position')[
0]
                # the position of the last element
                self.position = last.position + 1
            except IndexError:
                # IndexError happened: the query did not return any objects
                # so this has to be the first
                self.position = 0

        # save the now properly set-up model
        return models.Model.save(self)

    def delete(self):
        """Deletes the item from the list."""
        model_class = self.__class__
        # get all objects with a position greater than this objects position
        objects_after = model_class._default_manager.filter(position__gt=self.po
sition)
        # now we remove this model instance
        # so the `position` is free and other instances can fill this gap
        models.Model.delete(self)

        # iterate through all objects which were found
        for element in objects_after:
            # decrease the position in the list (means: move forward)
            element.position -= 1
            element.save()

More like this

  1. Template tag - list punctuation for a list of items by shapiromatron 10 months, 4 weeks ago
  2. JSONRequestMiddleware adds a .json() method to your HttpRequests by cdcarter 11 months ago
  3. Serializer factory with Django Rest Framework by julio 1 year, 5 months ago
  4. Image compression before saving the new model / work with JPG, PNG by Schleidens 1 year, 6 months ago
  5. Help text hyperlinks by sa2812 1 year, 7 months ago

Comments

jteague (on June 18, 2007):

This is exactly what I needed to provide a method for admin to set the order of objects retrieved by preference. I was hoping you or someone could provide a specific usage example for a newbie to Django like me. I'm having a bit of trouble trying to get this inherited from my model. Thanks very much.

John

#

jga23 (on June 20, 2007):

When I do objects.create(...) I get new() takes exactly 4 arguments (1 given). This only started happening after I inherited positional.PositionalSortMixIn

JGA

#

jokull (on July 2, 2007):

I am a total newbie - bare with me

Do I import PositionalSortMixIn in the model? How do I hook position ordering to an object? What does the import line look like?

IOW can someone give me copy-paste examples. Thanks in advance!

#

jokull (on July 13, 2007):

One of my objects is now correctly using PositionalSortMixIn. The get_object_at_offset is working and retrieving the correct item. However move_down and move_up can't save:

IntegrityError: (1062, "Duplicate entry '4' for key 2")

Could this snippet be broken with development version of Django?

<hr />

A temporary fix would be to intermediately save a -1 for one field so that everything is unique, while storing a variable with the position that -1 then gets in a secondary .save()

like so:

one_before_temp = self.position
self.position = one_before.position
one_before.position = -1
for obj in (one_before, self):
    obj.save()
one_before.position = one_before_temp
one_before.save()

#

Leonidas (on July 14, 2007):

jokull, you were right. At some point I added a unique constraint to the positional field which broke things. But it was absolutely neccessary, otherwise thare would be some strange corruption in the data (dublicate positions, missing positions).

I solved this in the meantime, using a temporary position field and added some decorators that should prevent data corruption.

#

Leonidas (on July 14, 2007):

I added now some usage example in the description text - now it should be simpler to get how PositionalSortMixIn works. The new version of PositionalSortMixIn takes a litte bit more care about adding the position attribute to a class to prevent it from being added twice. I also added a possibility to add an object at a defined position instead of appending it to the end.

Some methods are not yet optimal, but the current version should be working fine.

#

jokull (on July 19, 2007):

How would one integrate this into the admin then? I guess it's not within the capabilities of the current admin. I will probably make a custom view with a javascript layer to drag and drop into position.

#

Leonidas (on July 19, 2007):

I have coded some kind of integration into the admin. It is done by adding some sort of "toolbox" in the list of objects. Currently it contains the up and down arrows and a copy button. I can post it (create a new snippet), if you are interested but it works only in the current admin and is going to break once Django switches to newforms-admin. When newforms-admin will be merged I'll try to add this toolbox functionality into that as well as I find it quite useful.

Having a cool drag and drop functionality would be really cool, for this I think we'll need something like move_to(), but it's not a big deal.

#

jokull (on July 20, 2007):

With a javascript drag and drop you'd only need to serialize and overwrite everything in one go. Or hit the database with each "drag-drop" and keep everything in sync that way.

#

Leonidas (on July 20, 2007):

I'm not sure which option would be the best. Hitting the database with every "drag-drop" is probably quite slow and overwriting eveything does not seem to be optimal. But I have never implemented drag and drop in JavaScript so I would be interested in a nice implementation.

#

Please login first before commenting.