Subject: [tryton] Re: order of char with number

From: Hartmut Goebel <[email-masked]>
Date: Sat, 23 Aug 2008 17:34:30 +0200

Cédric Krier schrieb:

> So we order with this clause:
> 
> ORDER BY code_length ASC/DESC, code ASC/DESC

This is a smart solution :-)

-- 
Schönen Gruß - Regards
Hartmut Goebel
Dilp.-Informatiker (univ.), CISSP

Goebel Consult
Spezialist für IT-Sicherheit in komplexen Umgebungen
http://www.goebel-consult.de

---

From: Cédric Krier <[email-masked]>
Date: Sat, 23 Aug 2008 01:40:08 +0200

On 20/08/08 10:33 +0200, Cédric Krier wrote:
> On 20/08/08 09:58 +0200, Hartmut Goebel wrote:
> > Cédric Krier schrieb:
> >
> >> Sorry, by if you put space, it will not order with the human logic.
> >
> > Okay, I should shut up with untested code :-)
> >
> >> But if you know a better way to know the max length of a field in the
> >> database, I will be happy to remove the max(length).
> >
> > Some non-ideal ideas:
> >
> > - use "lpad(99, ..." which should be enough in most cases
> 
> Not very scalable
> 
> > - precalcuate max(len(code)), whenever an entry is added
> 
> Good idea, I push this:
> 
> http://www.tryton.org/hgwebdir.cgi/modules/relationship/rev/544d1de586d9
> 
> So now, we use the index to retreive the max length.
> 
> > - use another row which contains the order-key
> >   (precalculated lpad value)
> 
> But we can not precalculate the lpad as it depends of the number of
> records.
> 

Finnally with Bertrand, we find a better solution.
I adapt the order_by to use dictionary formating:
http://www.tryton.org/hgwebdir.cgi/trytond/rev/03b511477c9c

http://www.tryton.org/hgwebdir.cgi/modules/relationship/rev/7eb35f161dba

-- 
Cédric Krier

B2CK sprl
Rue de Rotterdam, 4
4000 Liège
Belgium
Tel: +32 472 54 46 59
Email: [email-masked]
Jabber: [email-masked]
Website: http://www.b2ck.com/

---

From: Cédric Krier <[email-masked]>
Date: Wed, 20 Aug 2008 10:33:14 +0200

Not very scalable

Good idea, I push this:

-- 
Cédric Krier

B2CK sprl
Rue de Rotterdam, 4
4000 Liège
Belgium
Tel: +32 472 54 46 59

---

From: Hartmut Goebel <[email-masked]>
Date: Wed, 20 Aug 2008 09:58:17 +0200

Some non-ideal ideas:

But I assume there are more important problems domains to solve. So I 
propose adding a command, opening an issue and solve this minor point 
some when later :-)

-- 
Schönen Gruß - Regards
Hartmut Goebel

Goebel Consult

---

From: Cédric Krier <[email-masked]>
Date: Sat, 16 Aug 2008 12:20:45 +0200

On 16/08/08 12:10 +0200, Hartmut Goebel wrote:
> Cédric Krier schrieb:
> 
> > We can not use integer cast because we can not assume that we have
> > integers. Otherwise, we would put a integer in the database.
> 
> IC. Sorry for being such harsh.
> But in this case I would not pad with zero but with space, which is more
> text-like and lexically smaller than '0'.
> 

You will have:

 1
 10
 100
 1000
 10000
 10001
 10002
 10003
 10004
 10005
 10006
 10007
 10008
 10009
 1001

instead of:

 1
 2
 3
 4
 5
 6
 7
 8
 9
 10
 11
 12
 13
 14

-- 
Cédric Krier

B2CK sprl
Rue de Rotterdam, 4
4000 Liège
Belgium
Tel: +32 472 54 46 59

---

From: Hartmut Goebel <[email-masked]>
Date: Sat, 16 Aug 2008 12:10:30 +0200

-- 
Schönen Gruß - Regards
Hartmut Goebel

Goebel Consult

---

From: Cédric Krier <[email-masked]>
Date: Fri, 15 Aug 2008 22:48:45 +0200

On 15/08/08 22:06 +0200, Hartmut Goebel wrote:
> mb schrieb:
> 
> >> SELECT * from account_account order by lpad(code, (select max(length(code)) from account_account), '0');
> ...
> >> So what do you think about this?
> 
> Ugly code! Throw it away!
> 1) It uses string-arithmetics
> 2) it uses max() which requires to get _all_ values first
> 3) it has to sort strings
> 
> Better use
> ... order by cast(code to integer)
> 

We can not use integer cast because we can not assume that we have
integers. Otherwise, we would put a integer in the database.
But of course if we can remove the MAX and replace it with an other
query that can use the index, it will be better.

-- 
Cédric Krier

B2CK sprl
Rue de Rotterdam, 4
4000 Liège
Belgium
Tel: +32 472 54 46 59

---

From: Hartmut Goebel <[email-masked]>
Date: Fri, 15 Aug 2008 22:06:55 +0200

...

Better use

-- 
Schönen Gruß - Regards
Hartmut Goebel

Goebel Consult

---

From: Eric Vernichon <[email-masked]>
Date: Fri, 15 Aug 2008 20:29:36 +0200

Le mardi 29 juillet 2008 à 14:58 +0200, Cédric Krier a écrit :
> Here is the problem. When we sort records based on a char field, we can
> have non usefull sort from SQL. Per example, sorting accounts on the
> field "code", which are often integer, may have strange behavior for the
> user like this:
> 
> 1
> 11
> 12
> 2
> 21
> 3
> 
> 
> So I propose to allow order_field attribute this kind of clause when
> ordering on it:
> 
> This query order correctly the accounts.
> 
> The definition of the code field will be:
> 
> code = fields.Char('Code', size=None, select=1,
>         order_field="lpad(%s.code, (select max(length(code)) " \
>                 "from account_account), '0')")
> 
> where %s will be replaced by the table name by the ORM.
> 

Great

-- 
Eric Vernichon Informatique
7, Chemin d'en barbaro
Les Martinels
81710 SAÏX

Partenaire Open ERP

Tel  : 05 63 72 31 25
Port : 06 62 19 65 85
Fax  : 05 63 59 31 70
http://www.vernichon.fr

--~--~---------~--~----~------------~-------~--~----~
[email-masked] mailing list
-~----------~----~----~----~------~----~------~--~---

---

From: mb <[email-masked]>
Date: Fri, 15 Aug 2008 11:23:22 -0700 (PDT)

Very good feature!

On Jul 29, 2:58 pm, Cédric Krier <[email-masked]> wrote:
> Email: [email-masked]
> Website:http://www.b2ck.com/
>
>  application_pgp-signature_part
> < 1KViewDownload

---

From: Cédric Krier <[email-masked]>
Date: Tue, 29 Jul 2008 14:58:27 +0200

1
11
12
2
21
3

-- 
Cédric Krier

B2CK sprl
Rue de Rotterdam, 4
4000 Liège
Belgium
Tel: +32 472 54 46 59