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/
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
>> 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
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/
> 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).
> 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 :-)
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/
> 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