2

I want to sort a table by dates. The problem is that they are interpreted as strings, hence my local date format is sorted wrongly, like 26. September is bigger than 16. November, because 26 > 16.

Anyway, so I have my own model set up and tried it like this:

QVariant MyModel::data(const QModelIndex &index, int role) const
{
    if(role == Qt::UserRole)
    {
        if(index.column() == 5) // Date
           return QSqlTableModel::data(index, role).toDate();
    }

    if(role == Qt::DisplayRole)
    {
        if(index.column() == 5) // Date
           return QSqlTableModel::data(index.role).toDate().toString("dd MMMM yyyy");
    }
}

and I set the sortRole like this:

proxyModel->setSortRole(Qt::UserRole);

The corresponding lines actually get called, but now I cant sort the table at all. It's just not responding. The arrows (representing asc or desc ordering) at the corresponding columns are changing, but the data isn't. Of course I set the rest like:

proxyModel->setDynamicSortFilter(true);
proxyModel->setSourceModel(myDBModel);
proxyModel->setFilterKeyColumn(1);
proxyModel->setFilterCaseSensitivity(Qt::CaseInsensitive);

If I do not set the sortRole at least I can sort by the other columns correctly. What am I doing wrong here? Do I have to implement another function or anything? I looked around the internet, but all I found was problems with sorting integers from years back, never dates :(

Improve this question

3 Answers 3

Reset to default
1

I am an idiot (fact!)

Qt already provided all I needed, it was just a stupid mistake on my side. Two things, really:

   if(role == Qt::UserRole)
    {
        if(index.column() == 5)
            return QDate::fromString(QSqlTableModel::data(index, Qt::DisplayRole).toString(), "yyyy-MM-dd"); // 1st Mistake, no correct conversion. I always got QVariant(invalid)
// 1.1 Mistake, also, grab the data from Qt::DisplayRole, not from Qt::UserRole!

        return QSqlTableModel::data(index, Qt::DisplayRole); // 2nd Mistake. Because I didn't add that line I couldn't sort it on any other column anymore. When I finally could sort it on the dates I couldn't on the other columns, then I thought about adding this line et voila!
    }

I wanna thank you for your kind and fast answers, but the problem existed between chair and keyboard this time.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

According to the QSortFilterProxyModel documentation, you can provide your own implementation of lessThan(). http://doc.qt.io/qt-5/qsortfilterproxymodel.html#lessThan

If for some reason that doesn't work as expected, you can always sort the dates as strings if you format those dates using ISO8601 (YYYY-MM-DD). This is how I typically choose to store (and later sort) dates when dealing with databases.

Improve this answer

Comments

1

I think that you should use Custom Sort/Filter Model. You should "teach" class how to compare it. Take a look of this example: http://doc.qt.io/qt-5/qtwidgets-itemviews-customsortfiltermodel-example.html

I think that the most useful code for you, you can find here:

bool MySortFilterProxyModel::lessThan(const QModelIndex &left,
                                       const QModelIndex &right) const
 {
     QVariant leftData = sourceModel()->data(left);
     QVariant rightData = sourceModel()->data(right);

     if (leftData.type() == QVariant::DateTime) 
         return leftData.toDateTime() < rightData.toDateTime();
}

QDateTime already has overloaded operator <.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.