[Zope] Database table problem

[Passin, Tom]

[ Renier Leenders]

>  "SELECT=20
> FROM law WHERE  article_number >=3D x and article_number <=3D y".=20
> Unfortunately not all law articles are numbered with 1, 2, 3,=20
> etc. If they=20
> were I could define article_number as integer. But some=20
> articles are numbered=20
> with 1, 2, 2a ... 13ab etc. When I define the article_number=20
> as char and I=20
> retrieve I get a result with an sorting order like this:=20
> 1,10,11,11a,11b,12,=20
> 13ab ... 2a, 20, 21,21a. I could not find a database who=20
> supports "natural=20
> sort".=20
> Has someone an idea to work around this problem with Zope?=20
> Any suggestions=20
> would be appreciated.

This can be difficult, especially if the depth of the subdivisions can
be unbounded.  Sometimes it is possible to use a computed column as the
sort index.  This is easiest when there are just numbers, no letters.

If the numbers have no subdivisions, as you seem to be showing, and ther
can only be a single letter after the number, I would probably try to
sort using computed columns like this (using imaginary functions - look
up to see what string handling functions your database supports):

Select substring(article_number, 1, len(article_number)-1) as
main_index,
    right(article_number,1) as secondary_index, article_title
Order by main_index, secondary_index

For a more complicated numbering system, you may still be able to do
something similar using database functions.  Otherwise you would have to
compute the sort order with Python.

With numbers like 1, 1.1, 1.1.1, 1.1.2, I like to multiply each division
be a corresponding power of ten and add them up to get a single sort
index.  1.2.3 would become 1*10^2 + 2*10^1 + 3 =3D 123 , for example. =
Note
that this is not exactly like just removing the separaators because the
trailing number might be missing (e.g., 1.2).Again, whether you can do
this depends on the database.

Some people like to precompute the sort order and store it in the
database.  I try to avoid this, but once in a while it can be useful.

Cheers,

Tom P