Hello, I think this question is OT but nevertheless. I don't know where to put my question elsewhere. I use Zope as some kind of 'frontend' for an relational database. I want to use the database to store laws. The idea is to retrieve articles with "SELECT FROM law WHERE article_number >= x and article_number <= y". Unfortunately not all law articles are numbered with 1, 2, 3, etc. If they were I could define article_number as integer. But some articles are numbered with 1, 2, 2a ... 13ab etc. When I define the article_number as char and I retrieve I get a result with an sorting order like this: 1,10,11,11a,11b,12, 13ab ... 2a, 20, 21,21a. I could not find a database who supports "natural sort". Has someone an idea to work around this problem with Zope? Any suggestions would be appreciated.
Renier Leenders wrote:
Hello,
I think this question is OT but nevertheless. I don't know where to put my question elsewhere. I use Zope as some kind of 'frontend' for an relational database. I want to use the database to store laws. The idea is to retrieve articles with "SELECT FROM law WHERE article_number >= x and article_number <= y". Unfortunately not all law articles are numbered with 1, 2, 3, etc. If they were I could define article_number as integer. But some articles are numbered with 1, 2, 2a ... 13ab etc. When I define the article_number as char and I retrieve I get a result with an sorting order like this: 1,10,11,11a,11b,12, 13ab ... 2a, 20, 21,21a. I could not find a database who supports "natural sort". Has someone an idea to work around this problem with Zope? Any suggestions would be appreciated.
Before inserting into the database strip the alphabetical part and store this in a special row. This can be done with a small PythonScript. As a side note, for this kind of data the relational storage has no benefit over a ZODB storage, except the data should be managed independant of Zope, which you state is not the case. HTH, __Janko
Renier Leenders wrote:
Hello,
I think this question is OT but nevertheless. I don't know where to put my question elsewhere. I use Zope as some kind of 'frontend' for an relational database. I want to use the database to store laws. The idea is to retrieve articles with "SELECT FROM law WHERE article_number >= x and article_number <= y". Unfortunately not all law articles are numbered with 1, 2, 3, etc. If they were I could define article_number as integer. But some articles are numbered with 1, 2, 2a ... 13ab etc. When I define the article_number as char and I retrieve I get a result with an sorting order like this: 1,10,11,11a,11b,12, 13ab ... 2a, 20, 21,21a. I could not find a database who supports "natural sort". Has someone an idea to work around this problem with Zope? Any suggestions would be appreciated.
In python, you could transform the strings to lists of strings of length 1, i.e. '12' -> ['1','2']. Now build a list of these lists and sort it. This should work, try it on a python command line. But I don't know if something like this exists in a database. HTH, oliver
Hello Renier, the type of sortinmg you are looking for is very unusual. Numeric sorting works not with string. What you need is a combination of numeric and string sorting. Maybe your database will allow you define your own sorting rules, but I doubt that. You could try a diffrent approach. use two database columns law_num, law_minor in which you store the information, e.g. law_num law_minor 1 2 2 a 2 b 11 11 a You should define your primary key as two column key and therefor it would not be allows to insert two laws with the same num/minor data but the ame num or minor would be allowed. Hope it helps. Ulli -- Lost? Get yourself found. http://www.PublisherSEO.com/3 World Wide Web Publisher, Ulrich Wisser, Odensvag 13, S-14571 Norsborg http://www.publisher.de Tel: +46-8-53460905 Fax: +46-8-534 609 06
participants (4)
-
Janko Hauser -
Oliver Bleutgen -
Renier Leenders -
Ulrich Wisser