QSqlTableModel sorting



  • I sort my model by the 'Service Date and Time' field in ascending order, but when a new record is recorded sometimes we wont know the date and time the services will be. So I need it to sort by the date and time field but if its empty have it at the end of the records, preferably sorted by when they were entered but that doesnt really matter.

    is there a way to do this? Some way to say something like sort ascending if field isnt empty otherwise leave it at the end?



  • Subclass QSortFilterProxyModel and reimplement lessThan().



  • Alright thanks. How would i do that exactly? I assume I need to use a loop to go through the rows but how do I tell it where to go and what not??



  • Take your QSqlTableModel (no sorting needed there) and set it as source model for a QSortFilterProxyModel subclass you've created and where you've reimplemented the virtual lessThan() function which sorts empty entries to the bottom.

    See also "QSortFilterProxyModel":http://qt-project.org/doc/qt-4.8/qsortfilterproxymodel.html#sorting and the "Custom Sort/Filter Model Example":http://qt-project.org/doc/qt-4.8/itemviews-customsortfiltermodel.html.



  • Ok thank. Do I put lessthan as public or something like that instead of protected so I can call it? Like something like this:

    @
    MySortFilterProxyModel *myProxy = new QSortFilterProxyModel;
    myProxy->setSourceModel(model);
    myProxy->lessthan(leftIndex, rightIndex)
    @

    but with a loop that calls less than at each row?

    Sorry I haven't done much with subclassing before.



  • Ok so it worked when I did this:

    @
    MySortFilterProxyModel *myProxy = new MySortFilterProxyModel;
    myProxy->setSourceModel(myModel);
    for (int row=0; row < myModel->rowCount(QModelIndex()); ++row){
    myProxy->lessThan(myModel->index(row, ServiceDateTime, QModelIndex()),
    myModel->index(row+1, ServiceDateTime, QModelIndex()));
    }
    @

    and I just had this for lessthan (the field I'm sorting by ServiceDateTime is a string not QDate):
    @
    bool MySortFilterProxyModel::lessThan(const QModelIndex &left,
    const QModelIndex &right) const
    {
    QVariant leftData = sourceModel()->data(left);
    QVariant rightData = sourceModel()->data(right);

     if (leftData.type() == QVariant::String) {
         return leftData.toString() < rightData.toString();
     }
    

    }
    @

    I don't excatly understand why, but I figured I'd post it incase it can help anyone else.

    Thanks for your help Lukas, you saved me!



  • [quote author="confused" date="1345815627"]Ok thank. Do I put lessthan as public or something like that instead of protected so I can call it?[/quote]No.

    QSortFilterProxyModel uses lessThan() to sort. The default implementation uses operator<(), and as you want to change this behaviour you subclass QSortFilterProxyModel and reimplement lessThen() providing your own comparison logic.

    @
    class CustomSortFilterProxyModel : public QSortFilterProxyModel
    {
    ...
    protected:
    bool lessThan(const QModelIndex &left, const QModelIndex &right) const
    {
    if ((left.column() == ServiceDateAndTimeColumnIndex) &&
    (sourceModel()->data(left).toString().isEmpty())
    return sourceModel()->data(
    sourceModel()->index(left.row(), EnteredDateColumnIndex))).toString() <
    sourceModel()->data(
    sourceModel()->index(right.row(), EnteredDateColumnIndex))).toString();
    else
    return QSortFilterProxyModel::lessThan(left, right);
    }
    }

    CustomSortFilterProxyModel *proxyModel = new CustomSortFilterProxyModel(this);
    proxyModel->setSourceModel(YourSqlTableModel);

    YourTableView->setModel(proxyModel);
    @
    Brain to terminal. Not tested. Exemplary.

    Take a look at the example I've linked.

    On a sidenote: Inheritance is a core concept of C++ and Qt as well. Make sure you are familiar with it.



  • This was due last week but when some people started to use it they noticed a few problems. One was with this sorting. When 2 things were on the same date it didnt sort them right. When I tried to fix it it now is messing up even more and isn't putting the ones with no date at the bottom.

    what is EnteredDateColumnIndex supposed to be? I only have one column with any dates in it and that is the ServiceDateAndTimeColumnIndex but it is entered as a string in SQL not a datetime so its just a string not a Qdatetime if that matters at all



  • You shouldn't take this example as productive code, it is exemplary and therefore for demonstration only and you will have to adapt it to your situation, your model, your database schema.

    The idea behind QSortFilterProxyModel and lessThan() is actually quite simple.

    If you want to sort your model using a specific column the QSortFilterProxyModel basically calls lessThen() for each value in the column. If lessThen() returns true the value left is less than the value right and it should be placed after right in the table, if lessThen() returns false the value left is greater then the value right and it should be placed before right in the table (for a given sort order).
    [quote]I need it to sort by the date and time field but if its empty have it at the end of the records, preferably sorted by when they were entered but that doesnt really matter.[/quote]
    If we are sorting by the date and time field (<code>left.column() == ServiceDateAndTimeColumnIndex</code>) and it is empty (<code>data(left).toString().isEmpty()</code>) sort it by when they were entered (<code>data(left.row(), EnteredDateColumnIndex) < data(right.row(), EnteredDateColumnIndex)</code>), assuming that when they were entered is stored in the column with the index <code>EnteredDateColumnIndex</code>. If we are sorting a different column or the date and time field is not empty just call the default implementation (<code>return QSortFilterProxyModel::lessThan(left, right)</code>), which basically does operator<().

    You will have to expand the code to support the right is empty case and tinker with the return values (or comparison operators) if you want to have empty values sorted elsewhere (either at top or bottom) or differently.



  • Ooo ok. Thanks that really helps I didn't get how it was sorting it, I just didnt know that if its true it puts the left after the right and if false the other way. How can get I get the date and time that a new row is added? I dont currently have a column for that to sort by the date and time the record was created if its empty but is there a way in Qt do something like when my add record function is called set a certain index to the current date and time?


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.