Numbers are sorted wrong in QTableView/QSqlQueryModel setup

[antimatter]

Hello,

I've got a QTableView / QSortFilterProxyModel / QSqlQueryModel-subclass setup. Some columns contain floating point numbers and the default sorting is by alphabet. That will sort numbers the wrong way like this:

-0.06
-1.45
 0.04
 0.15

-0.06
-1.45
 0.04
 0.15

To copy to clipboard, switch view to plain text mode 

Of course, -1.45 is less than -0.06 and should occur before -0.06.

So, how do I make the model(s) sort the right way? I researched quite a while, but the more I read the more I get confused. I read about several possible approaches, but they don't work, shouldn't be used or I don't understand them.

• SQL model should be left alone, sorting should be implemented in proxy model. I don't know how. Reimplementing lessThan() is my working solution, but apparently it is discouraged.
• Use a custom SortRole with the proxy model and return actual floats in SQL model subclass for this role. Didn't work. Custom role didn't arrive in the model.
• Reimplement sort() in the SQL model subclass. Don't know how, because the data is handled internally by some QSqlQueryModelPrivate class. I might reset the QSqlQuery with a new query containing a order-by clause. But that will fetch the data from the database again. Seems to be strange, as the data already resides in that QSqlQueryModelPrivate class instance.

I was hoping, someone could show me the 'proper' way for sorting floating point numbers.

All the best,
Andreas

[ChrisW67]

This works fine, but it will not sort numerically if your "floating point numbers" are actually strings:

#include <QApplication>
#include <QTableView>
#include <QSqlQueryModel>
#include <QDebug>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSortFilterProxyModel>
 
bool createData()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    if (db.open()) {
        QSqlQuery query;
        query.exec("create table test (id int, name text, value real)");
        query.exec("insert into test values(101, 'Name 1', 0.3)");
        query.exec("insert into test values(102, 'Name 2', 0.34)");
        query.exec("insert into test values(103, 'Name 3', -0.1)");
        query.exec("insert into test values(104, 'Name 4', 4.0)");
        query.exec("insert into test values(105, 'Name 5', 0.11)");
        return true;
    }
 
    return false;
}
 
int main(int argc, char *argv[])
{
    QApplication app(argc, argv);
    if (!createData())
        return 1;
    QSqlQueryModel model;
    model.setQuery("select * from test");
    QSortFilterProxyModel proxy;
    proxy.setSourceModel(&model);
    QTableView view;
    view.setSortingEnabled(true);
    view.setModel(&proxy);
    view.show();
    return app.exec();
}

#include <QApplication>
#include <QTableView>
#include <QSqlQueryModel>
#include <QDebug>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSortFilterProxyModel>

bool createData()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    if (db.open()) {
        QSqlQuery query;
        query.exec("create table test (id int, name text, value real)");
        query.exec("insert into test values(101, 'Name 1', 0.3)");
        query.exec("insert into test values(102, 'Name 2', 0.34)");
        query.exec("insert into test values(103, 'Name 3', -0.1)");
        query.exec("insert into test values(104, 'Name 4', 4.0)");
        query.exec("insert into test values(105, 'Name 5', 0.11)");
        return true;
    }

    return false;
}

int main(int argc, char *argv[])
{
    QApplication app(argc, argv);
    if (!createData())
        return 1;
    QSqlQueryModel model;
    model.setQuery("select * from test");
    QSortFilterProxyModel proxy;
    proxy.setSourceModel(&model);
    QTableView view;
    view.setSortingEnabled(true);
    view.setModel(&proxy);
    view.show();
    return app.exec();
}

To copy to clipboard, switch view to plain text mode 

[antimatter]

You made my day: I found the problem. Those numbers were really formatted as strings. In my QSqlQueryModel subclass, I returned those floats like

return "%.2f" % data.toFloat()[0]  # (this is python)

return "%.2f" % data.toFloat()[0]  # (this is python)

To copy to clipboard, switch view to plain text mode 

in order to display all numbers with 2 digits after the decimal point. The variable "data" is just the result of the baseclass' data() method. Now I return the unformatted data and sorting works with floats, too!

But is there a way to sort those numbers correctly and display them in some formatted way? I've got two other columns, that contain right-ascention and declination as floats, and I display them by formatting them into degree/hours notation (string):
RA: 1.767798 = 6h 45m 08.97s
DEC: -0.29175 = -16° 42' 57.8"

I set my proxy model like this

proxy.setSortRole(Qt.UserRole)

proxy.setSortRole(Qt.UserRole)

To copy to clipboard, switch view to plain text mode 

and return plain floats in my models data() method for this role, but then sorting doesn't work at all. Any idea, how I might achieve that kind of sorting/displaying?


Andi

[ChrisW67]

Typically you would return a human friendly string from data() when the Qt::DisplayRole is requested, and the native value when Qt::EditRole is requested. Sorting on Qt::EditRole then has the desired effect, the table view shows the user the human friendly text, and a reasonable default editor is more likely to be created.

Astronomy application?

[antimatter]

That is fantastic! Thank you very much. Now the sorting works like it should be AND I have my desired formatting.
But why does EditRole work here but not UserRole?

Yes, kinda astronomy application. It's purpose is to reconstruct the illuminated flight path of meteors ("fireballs") from these kinds of all-sky, long-exposure images:

[ChrisW67]

I don't see a reason the code would not sort on Qt::UserRole the same as any other. Perhaps there is some other quirk at play.

Have fun with the project.

[antimatter]

I found the quirk.
When I set my proxy models sortRole to "UserRole", it will call my QSqlQueryModel subclass' data() method with that role.
In there I call the base class' data() method with the parameters fist (will receive UserRole, too), and will get an invalid QVariant because of this little excerpt from the QSqlQueryModel::data() source code.

QVariant v;
if (role & ~(Qt::DisplayRole | Qt::EditRole))
return v;

QVariant v;
if (role & ~(Qt::DisplayRole | Qt::EditRole))
return v;

To copy to clipboard, switch view to plain text mode 

With that out of the way, I can indeed have some fun. Thanks all.

Andi