CODEWITHSUNDEEP
web2py
neil.johnson
neil.johnson

Reputation: 2117

In Web2py, orderby on integer field treated as a string

I have a query :-

result = db(db.tag_count.user_id == user_id).select(db.tag_count.name, db.tag_count.count, orderby=~ db.tag_count.count, limitby=(0, 10))

Where the table is defined as

db.define_table('tag_count',
                Field('name'),
                Field('user_id', db.auth_user),
                Field('count', 'integer'),
                format='%(title)s')

The aim of the query is to return the top 10 rows containing the highest integer value of tag_count.

In fact it returns the highest string value of tag_count for example 21 , 2, 12, 2 i.e 2>12 . I would expected 21, 12, 2, 1

I am using web2py 2.3.2 and sqlite as the database.

Any idea what I'm doing wrong?

Upvotes: 1

Views: 836

Answers (1)

neil.johnson
neil.johnson

Reputation: 2117

For the sake of people like me in the future. With thanks to Anthony's comment above.

The original table did not define the field to be an integer and defaulted to string. I realised that I needed an integer and updated the field. SQlite is not able to migrate the type and so it remained a string.

Solution is to export table, recreate table with integer and ensure that reinsertion type is an integer.

See here for more details

Upvotes: 1

Related Questions