search

objectbox / objectbox-dart

Flutter database for super-fast Dart object persistence
https://docs.objectbox.io/getting-started
Apache License 2.0
981 stars 119 forks source link

order by on text field gives different result than Postgresql's #232

Closed ebadta81 closed 3 years ago

ebadta81 commented 3 years ago

Hello,

I have a table with "name" column. I have these text fields in it (Correction: this is from postgresql):

If I query this on postgresql (Order by name), I get this order (Correction: this is from objectbox):

With objectbox I get the first result. The difference is, how they ordering the "space" character.

Why is this a problem? In my app I use objectbox database to display the values, then execute a refresh from the postgresql server. Then I update the result, and the values swapping.

Basic info (please complete the following information):

Expected behavior

I would like to see the same order.

vaind commented 3 years ago

If I build a DB with such values and execute a Query on it with an order on the field, I get the correct result. Are you sure you're configuring the query to use the order by the right field? Should look somewhat like this: final query = box.query(condition).order(field).build()

ebadta81 commented 3 years ago

Sorry I swapped the two results, so the second one what I get from Postgres, and the first one from objectbox. This is my full query looks like: box.query(TownModule_.town_id.equals(global.town!.id)).order(TownModule_.sequence).order(TownModule_.name).build().find()

So I have two order by's, the first one is on an int field, but the values of these are all one-s (1).

ebadta81 commented 3 years ago

I tested with only one order on the name field. The result is the same.

vaind commented 3 years ago

Actually, you'll get the same order from Dart itself (the following code prints [a, a a, a1]):

    final items = ['a a', 'a', 'a1'];
    items.sort();
    print(items);

The issue is rather in your pgsql, specifically the collation setting on the column you're sorting by. If you google for postgres order space you'll get a lot of questions/issues on StackOverflow. For example:

vaind commented 3 years ago

ObjectBox currently doesn't allow you to specify collation so your only chance if you need the order matched, is changing the query in postgres, or maybe sorting in dart after receiving data from the server: List.sort() accepts a callback so you can sort any type of object by any property.

ebadta81 commented 3 years ago

Dear Vaind,

Thank You for your effort.