MySortFilterProxyModel sorting by multiple columns



  • Hey I am using the CheckablesortFilterProxymodel to sort my MS SQL database. It is working fine but I need to add in sorting by a second column now in a kinda weird way. I when the 2nd column is checked for a record I need all of those checked to be sorted to the bottom of the list and, preferably, still be ordered by the 1st column. Is this possible to do with the CheckablesortFilterProxymodel or will I need to find/attempt to make something else?

    any help would be greatly appreciated.
    thanks



  • My bad I haven't worked on this program in a while and was mistaken. I am actually using MySortFilterProxyModel to sort the records correctly by a date/time field. The 2nd column that I will be sorting by will use the CheckablesortFilterProxyModel so it will just be 1's and 0's and any records that are checked need to be grouped at the bottom of the list.

    Sorry about that confusion.



  • If possible, sort with merge method twice: first by secondary column, then by primary. Merge sorting preserves order and is extremely fast if used on lists.



  • if I have a database how do I sort it with the merge method using different columns? I got the MySortFilterProxyModel from another post on here and I understand how its sorting the 2 indexes but not how it moves the whole row (if that makes any sense at all). Also where it sorts it takes the index data and then uses QVariant::DateTime so if I'm using 2 different data types how would that work, would I just need 2 different functions?

    And if you want to see where I got the code from it came from these 2 links
    "QSortFilterProxyModel":http://qt-project.org/doc/qt-4.8/qsortfilterproxymodel.html#sorting and "Custom Sort/Filter Model Example":http://qt-project.org/doc/qt-4.8/itemviews-customsortfiltermodel.html



  • Since you're using MS SQL database - you don't choose how to sort it. On the other hand, ProxyModel does not sort the database. It sorts the output of your model as it presents it to the view. To do custom sorting you reimplement the sort() method of the model. Maybe you need to do more than just that. I can't say since I never used proxy models. My own model does merge sorting, but it also contains its own data in a list.



  • In case anyone else is ever wondering how to do anything similar to this I ended up using the setsort() function twice on my QSqlTableModel. I also used a large default date and time of 09/09/7999 9:09 AM since I am displaying the date and time in a QDateTimeEdit that is the largest year I could do or else it would have been all 9's. I set the sort method 1st by the date time field call model->select() then by the checked field and call model->select again and it worked perfect.



  • Couldn't you just query "SELECT * FROM (SELECT <columns> FROM <table> WHERE <statement> SORT BY datetime) SORT BY check"?



  • I'm honestly not sure. I probably could but I don't have much experience writing SQL code and I'm not sure how to do that in Qt I only know of QTableModel->setSort() and Select() and functions like that. Would your query suggestion be more efficient though? And if so, how would I do that in Qt?



  • I don't know how efficient that query is, especially compared to other methods. When dealing with complex queries you might face the dilemma of making work easier for server or client side. Sometimes you even have to denormalize your tables to make queries more efficient. Thing is: I haven't faced these cases so far. If that's not the case for you either - don't mind it and just do what works. And I don't know how to implement my query in your case, since I know too little about it and your code. But I suggest you learn SQL queries if you deal with SQL databases. Smart queries save you writing a lot of code.



  • Yeah when I was starting this I was starting to look into learning SQL queries but then people were talking about the proxy model's and sort functions for the QSqlTableModel's so at the time it didnt seem as needed. But now that I have to sort by 2 columns I regret that.

    I've written this on my laptop using a database with all the same fields and everything that the real database has, but when I transferred it over to use the real database the records are all out of order, or it pretty much seems to only be ordered based on the second sort I call which is the checked field but everything else isn't ordered in anyway by the datetime field. So I'm thinking about trying to use your Query suggestion and see if that will work on the real database. But where you put <columns> do I put just the columns I need to sort by or all the columns I want to pull in from the database? Because there's 33 columns.. and also for <statement> would that be where I tell it to sort it Ascending? And if so do you know how I would write that in SQL? ..Sorry for all the questions, but I appreciate all the help you've given me



  • In <columns> you do enumerate all the columns you need. Though if you need all columns - just write *. <statement> is not for sort order, but for records filter. For details read the reference for SELECT query. Same for sort order - it is all in reference. Oh, and I made a mistake. ORDER BY, not SORT BY.


Log in to reply
 

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