order of char with number
Cédric Krier
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:
SELECT * from account_account order by lpad(code, (select max(length(code)) from account_account), '0');
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.
So what do you think about this?
--
Cédric Krier
B2CK sprl
Rue de Rotterdam, 4
4000 Liège
Belgium
Tel: +32 472 54 46 59
Email: cedric...@b2ck.com
Website: http://www.b2ck.com/
mb
Very good feature!
> Website:http://www.b2ck.com/
>
> application_pgp-signature_part
> < 1KViewDownload
Eric Vernichon
> 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:
>
> SELECT * from account_account order by lpad(code, (select max(length(code)) from account_account), '0');
>
> 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.
>
> So what do you think about this?
>
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
Hartmut Goebel
>> 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)
--
Schönen Gruß - Regards
Hartmut Goebel
Goebel Consult
Spezialist für IT-Sicherheit in komplexen Umgebungen
http://www.goebel-consult.de
Cédric Krier
> 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
Email: cedric...@b2ck.com
Website: http://www.b2ck.com/
Hartmut Goebel
> 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'.
Cédric Krier
> 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'.
>
Sorry, by if you put space, it will not order with the human logic.
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
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).
Hartmut Goebel
> 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
- precalcuate max(len(code)), whenever an entry is added
- use another row which contains the order-key
(precalculated lpad value)
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 :-)
Cédric Krier
> 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.
Cédric Krier
> 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
So we order with this clause:
ORDER BY code_length ASC/DESC, code ASC/DESC
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: cedric...@b2ck.com
Jabber: cedric...@b2ck.com
Website: http://www.b2ck.com/
Hartmut Goebel
> 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