How can I create a sequence number for a data set in the model?

[Mark Phillips]

I want to create a sequence of values for a column in one of my tables in the model class. The sequence number is updated every time a new rows is added to the table. The sequence number is reset to zero when a foreign key value changes. Is this possible, and how would I do it? 

 

Here is the example table:

id | fk1 | sequence_number | some data in other columns

 1 |  2   |           1                 |       xxx

 2 |  2   |           2                 |       yyy

 3 |  2   |           3                 |       zzz

 4 |  4   |           1                 |       xxx

 5 |  4   |           2                 |       yyy

 6 |  4   |           3                 |       zzz

Basically, I have sets of data determined by a foreign key, and I want to know the order in which the rows arrive within a data set.

Thanks for your suggestions!

Mark

[Mark Phillips]

On Fri, Apr 6, 2012 at 10:58 PM, Dennis Lee Bieber <wlf...@ix.netcom.com> wrote:

On Fri, 6 Apr 2012 22:34:35 -0700, Mark Phillips
<ma...@phillipsmarketing.biz> declaimed the following in
gmane.comp.python.django.user:

>
> Basically, I have sets of data determined by a foreign key, and I want to
> know the order in which the rows arrive within a data set.
>

       IOWs, you don't really need a value incrementing per foreign key...
After all, the primary key already reflects the /order/ of new data
inserts.

       In plain SQL, this might be an application for a "group by" the
foreign key, "order by" the primary key. Note that your example is NOT
very clear. "Reset to zero" could result in:

1       1       1       ....
2       1       2       ...
3       2       1       ...
4       1       1       ...
5       2       1       ...
6       2       2       ...

I don't understand what you mean by "reset to zero".

 

       Whereas, ignoring the example "sequence" column, group by/order by
would return

1       1       1       ...
2       1       2       ...
4       1       1       ...
3       2       1       ...
5       2       1       ...
6       2       2       ...

       Actually, just an ORDER BY FK, PK would produce the above... GROUP
BY would be used if you need something like counts/avg for each FK set.

I agree that if the data in the db was entered correctly in the first place, then just ORDER BY on the FK and PK would be sufficient. However, if the user needs to insert some data between sequence #3 and sequence #4, then I have to muck with the primary key of the table, which sounds dangerous. 

Perhaps a better analogy would be to think of a linked list as my in memory data model, and I want to translate that to a sql table(s). I found a reference http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html, that may solve my problem.

Apologies for not being clearer in my original post.

Mark

[Abhaya Agarwal]

Hi Mark,

On Sat, Apr 7, 2012 at 8:57 PM, Mark Phillips <ma...@phillipsmarketing.biz> wrote:

       Actually, just an ORDER BY FK, PK would produce the above... GROUP
BY would be used if you need something like counts/avg for each FK set.

I agree that if the data in the db was entered correctly in the first place, then just ORDER BY on the FK and PK would be sufficient. However, if the user needs to insert some data between sequence #3 and sequence #4, then I have to muck with the primary key of the table, which sounds dangerous. 

I also have a similar problem in a project right now and have thought of 2 possible solutions. First is to use a FloatField or a DecimalField (o). Do not reset it on every FK change. If the user wants to insert something between records a & b, assign it a o value which is average of a.o and b.o. If you are not expecting too many such insertions, it should work fine.

The other option is to store the ordering explicitly in the Foreign Key model using a JSONField. This means that you will have to move all the work involving ordering in the python layer which may or may not be suitable for your case.

Currently I am going with option 2 for my case.

Regards,

Abhaya

--
-------------------------------------------------
blog: http://abhaga.blogspot.com
Twitter: http://twitter.com/abhaga
-------------------------------------------------