QSqlTableModel sorting by multiple columns
-
Hey
I have a table in MS SQL I am retrieving with a QSqlTableModel. I need the records to be sorted on a datetime field and also on an int field that I display as a check box, so just 1's and 0's, and if its checked I need all those values to be at the bottom of the list. I wrote the program on my laptop using a database with all the same fields and everything as the actual database that it will be using and used
@tableModel->setSort(DateTime, Qt::AscendingOrder);
tableModel->select();
tableModel->setSort(checkField, Qt::AscendingOrder);
tableModel->select();@
And that was working fine on my computer. Then when I moved it to the network to test it on the actual database it is only sorting by the checkField and in no order at all by the datetime field. I was going to try writing a query but then realized that the setQuery() function for QSqlTableModel is protected so I can't do that.Does anyone know any way for me to be able to do this?
Thank you
-
Sorting on multiple columns can, AFAIK, only be done using your own query, or using a custom subclass of QSortFilterProxyModel in which you reimplement bool lessThan(const QModelIndex& left, const QModelIndex& right) const.
It depends on your exact use case which solution I'd prefer.
-
How would I do that? I had originally made a custom subclass of QSortFilterProxyModel , when I didn't have a default datetime that was very large and before I needed to sort by the second column, that was very similar to this link I was referred to "custom QSortFilterProxyModel":http://qt-project.org/doc/qt-4.8/qsortfilterproxymodel.html#sorting but I couldn't figure out how to add in a second column to sort by.
Also, since QSqlTableMode->setQuery() is protected how would I use my own query if I did it that way?Would I have to make a custom subclass of QSqlTableMode?
And lastly, what do you mean it depends on my use for which solution? Basically what is the difference between the two? From what I understand, the query would have the database do more of the work and the proxy model would only sort the viewing output? So would a query would be better for larger data sets?
-
[quote author="confused" date="1359122824"]How would I do that? I had originally made a custom subclass of QSortFilterProxyModel , when I didn't have a default datetime that was very large and before I needed to sort by the second column, that was very similar to this link I was referred to "custom QSortFilterProxyModel":http://qt-project.org/doc/qt-4.8/qsortfilterproxymodel.html#sorting but I couldn't figure out how to add in a second column to sort by.[/quote]
You'd do that by reimplementing lessThan, as I just told you. There, you can compare on as many columns as you wish before you decide if item left is smaller than item right.[quote]Also, since QSqlTableMode->setQuery() is protected how would I use my own query if I did it that way?Would I have to make a custom subclass of QSqlTableMode?[/quote]
Or use QSqlQueryModel, or use a view in your database instead.[/quote]
And lastly, what do you mean it depends on my use for which solution? Basically what is the difference between the two? From what I understand, the query would have the database do more of the work and the proxy model would only sort the viewing output? So would a query would be better for larger data sets?[/quote]
Yes, basically. The problem with a QSFPM is that it can only do the sorting if it has all the items it needs to sort. So that means that your database needs to provide all the items first. For large data sets and/or networked databases, that may be very inefficient. However, your gains may also depend on the database driver and the exact implementation of QSqlQueryModel and QSqlTableModel (I am not sure these actually support only fetching part of the result set). -
[quote author="Andre" date="1359123208"]
You'd do that by reimplementing lessThan, as I just told you. There, you can compare on as many columns as you wish before you decide if item left is smaller than item right.[/quote]
Ok so in lessThan I have it look at different columns instead of recalling lessThan with different column indexes? Think that was my problem when I was doing this before[quote]Or use QSqlQueryModel, or use a view in your database instead.[/quote]
If by "a view" you mean something like a QTableView unfortunately I can't do that because I have to output it in a form like format. So I use a QListWidget that shows some of the data from each record then when a row is clicked on I use a QDataWidgetMapper to populate all the QLineEdit's with the field data.[quote]Yes, basically. The problem with a QSFPM is that it can only do the sorting if it has all the items it needs to sort. So that means that your database needs to provide all the items first. For large data sets and/or networked databases, that may be very inefficient. However, your gains may also depend on the database driver and the exact implementation of QSqlQueryModel and QSqlTableModel (I am not sure these actually support only fetching part of the result set).
[/quote]
I have a default value for both fields so I think it should always have all the items it needs to sort, if that's what you mean by that. But since I don't exactly know how to use QSqlQueryModel I am going to try the custom QSFPM 1st, assuming my understanding of having the different columns be looked at inside of less that is correct -
[quote author="confused" date="1359128142"]
[quote author="Andre" date="1359123208"]
You'd do that by reimplementing lessThan, as I just told you. There, you can compare on as many columns as you wish before you decide if item left is smaller than item right.[/quote]
Ok so in lessThan I have it look at different columns instead of recalling lessThan with different column indexes? Think that was my problem when I was doing this before
[/quote]
Well, calling QSortFilterProxyModel::lessThan could be part of the implementation of your own version of lessThan, of course. But I'd probably write it myself.
[quote]
[quote]Or use QSqlQueryModel, or use a view in your database instead.[/quote]
If by "a view" you mean something like a QTableView unfortunately I can't do that because I have to output it in a form like format. So I use a QListWidget that shows some of the data from each record then when a row is clicked on I use a QDataWidgetMapper to populate all the QLineEdit's with the field data.
[/quote]
No, I mean a "view in the database sense":http://en.wikipedia.org/wiki/View_(database) of it. Define the view with the sorting you need in your database, and query that from your Qt application.
[quote]
[quote]Yes, basically. The problem with a QSFPM is that it can only do the sorting if it has all the items it needs to sort. So that means that your database needs to provide all the items first. For large data sets and/or networked databases, that may be very inefficient. However, your gains may also depend on the database driver and the exact implementation of QSqlQueryModel and QSqlTableModel (I am not sure these actually support only fetching part of the result set).
[/quote]
I have a default value for both fields so I think it should always have all the items it needs to sort, if that's what you mean by that. But since I don't exactly know how to use QSqlQueryModel I am going to try the custom QSFPM 1st, assuming my understanding of having the different columns be looked at inside of less that is correct[/quote]
No, that's not what I mean. I was talking about rows, not columns. If your database table contains 10 million rows, but your user is only interested in the most recent 10, how efficient do you think it is to get all the rows from the database (over a possibly slow database connection) and then sort them all in reverse-chronological order? Would it not be more efficient to just ask the database to at first only return the most recent few rows (that it can find easily using an index it keeps)? -
[quote author="Andre" date="1359123208"]
Well, calling QSortFilterProxyModel::lessThan could be part of the implementation of your own version of lessThan, of course. But I'd probably write it myself.[/quote]
Yeah I am writing a customized version I just don't know the exact way to do that but I'm trying to figure it out. I am thinking call lessThan like this
@
for (int row=0; row < myProxyModel->rowCount(QModelIndex()); row++){
myProxyModel->index(row, DateTimeField, QModelIndex()) < myProxyModel->index(row+1, DateTimeField, QModelIndex());
}@
And then lessThan is something like this but I dont know what to do after comparing the 1st index to effect the second comparison
@
bool MySortFilterProxyModel::lessThan(const QModelIndex &left,
const QModelIndex &right) const
{
QModelIndex leftCheck = sourceModel()->index(left.row, 2, QModelIndex());
QModelIndex rightCheck = sourceModel()->index(right.row, 2, QModelIndex());QVariant leftDateTimeData = sourceModel()->data(left); QVariant righDateTimeData = sourceModel()->data(right); QVariant leftCheckData = sourceModel()->data(leftCheck); QVariant rightCheckData = sourceModel()->data(rightCheck); if (leftDateTimeData.type() == QVariant::DateTime) { leftDateTimeData.toDateTime() < righDateTimeData.toDateTime(); } if (leftCheckData.type() == QVariant::Int) { leftCheckData.toInt() < rightCheckData.toInt(); }}
@[quote]
No, I mean a "view in the database sense":http://en.wikipedia.org/wiki/View_(database) of it. Define the view with the sorting you need in your database, and query that from your Qt application.[/quote]
your link didnt work was it supposed to be "this page":http://en.wikipedia.org/wiki/View_(database)? How do I make the view though? I am trying to in MS SQL in views then create new view and added in my table but keep getting errors so apparently I don't know how to. Also how do I query it from my application?[quote]
No, that's not what I mean. I was talking about rows, not columns. If your database table contains 10 million rows, but your user is only interested in the most recent 10, how efficient do you think it is to get all the rows from the database (over a possibly slow database connection) and then sort them all in reverse-chronological order? Would it not be more efficient to just ask the database to at first only return the most recent few rows (that it can find easily using an index it keeps)?
[/quote]
Yes that makes sense. But since this will be using a network connection to be used at different locations of the company and needing to access the database that is stored on the server at the main location we are using a remote desktop connection that users will open and have to log into. And from understanding from my supervisor that only points at the database so it doesn't pull the whole thing out so wouldnt that not be as much of a concern? I could be completely wrong about that though.. -
Yeah, it seems the link to wikipedia got screwed up by the forum software running on qt-project.org. But you got the right page. How to create a view for your database is something that we can't help you with on this forum. Try a support channel for your database for that. MySQL is certainly capable enough.
If your database is indeed going to be run over a network (so: slowly), then I'd certainly look into this option. In order to sort locally on the client, as would be the case using a QSFPM, you will need to pull in all rows. If you don't get that by now, please re-think and re-read the whole topic.
On your lessThan imlementation: sorting on more than one column means that in case of the values for column n being the same, the result of the comparison will be determined by column n+1. So, that is the only case that is relevant. If your first col already yields a < or a >, you don't need to compare your second column, you return true or false respectively immediately. If however your first comparison column yields ==, you need to let the next comparison column decide. Etc. Only if the last comparison column also yields ==, you just return false (note: == is not <, so lessThan needs to return false if the two items to compare are the same).
Note that the comparisons you have now do not make any sense. You're going to look at something like:
@
if (col1ValueLeft < col1ValueRight) {
return true;
} else if (col1ValueLeft > col1ValueRight) {
return false;
} else {
if (col2ValueLeft < col2ValueRight) {
return true;
} else if (col2ValueLeft > col2ValueRight) {
return false;
} else {
//etc for next columns.
}
}
@This can of course be done more elegantly using recursion, but if you're only interested in two columns instead of N columns, that is a bit pointless.
-
Well since my 2 columns are a checkable column stored as 1's (checked) and 0's (unchecked) and a datetime column. And I need it to be if checked have the column sorted to the bottom and then everything not checked sorted by the datetime column (I'd like the checked ones also sorted by the date time column but that is less important). Then I don't really need to compare the checkable column to the next column do I? But just If its > 0? so something more like this
@
if (checkColValueLeft > 0) {
return false;
} else {
if (datetimeColValueLeft < datetimeColValueRight) {
return true;
} else if (datetimeColValueLeft > datetimeColValueRight) {
return false;
}
}
@I've tried this but I didn't work. But I would think I need to do something like this because I wouldn't see why I would need to compare the 2 checkable columns of 2 rows cuz even if they are both checked I want them to be moved to the bottom so I'd want it to return false, right?
And I knew my comparisons at that time didn't make sense I was just stuck at what to do after I posted that I continued trying to figure out how to make it work, I made much more confusing nested if statements than yours though, so thank you for your response.
I am asking about making the MSSQL view on a Forum specific for SQL. Hoping I can accomplish that, to query that to my application would I just do something like replace
@tableModel->setTable("Table");@with
@tableModel->setTable("TableView");@or is there another way I do this?
-
No, that reasoning is wrong. Even if you say that checked should always be below unchecked, you still need to do the full comparison:
- if one row is checked, and the other not, the unchecked one is smaller (so, you need to compare on the checkable column)
- if both rows have their checkable column unchecked, then you need to compare the date columns (so, you need to compare on that column as well)
- if both rows have their checkable column checked, then you also need to compare the date columns, because the bottom part of the table should also be sorted.
The above equates to:
- if one row is checked, and the other not, the unchecked one is smaller (so, you need to compare on the checkable column)
- if the checkable column of both rows are equal, then you need to compare the date columns (so, you need to compare on that column as well)
And that is exactly what I showed you in the example code in my previous post. If someone gives you example code, perhaps it is a good idea to try that when your own solution doesn't work...
-
Oo ok that makes sense. I did try the code you gave me but it didn't seem to work, which is when I tried what I put in my previous post, but maybe I did something wrong when typing it in, so I'll try it again. What I don't understand is if they are both checked how does it tell it to move the row(s) down to the bottom?
-
That's the job of the sorting algorithm to figure out. Your job is to give that algorithm the right input: a reliable comparison between two rows yielding a boolean that tells if the first item is to be considdered 'smaller' than the second item.
If you want to know how these algoritms work, do a bit of internet research. "Wikipedia":http://en.wikipedia.org/wiki/Sorting_algorithm may be a decent start for that.
Note that my code was meant to convey the idea of how to do this, not be your final "ship it" code. Try to see if you understand the principle that this sample describes, and them implement that on your own.
-
Ohh OK! This is making a lot more sense now. I thought that this was supposed to somehow be actually doing the sorting and that's what I was so confused. But alright I'll take what you gave me an try to understand it better and make it into something working.
In the SQL forum I posted in I was suggested to do a Select Query or a Stored Procedure. I got the select query where it seems to be working, but am told the stored procedure would be better. But do you know how I could use either of these with my Qt Application without having to change too much code?
My main concerns are that I have a checkbox in my window and I use the QSqlTableModel's setFilter() function where if the checkbox is checked setFilter() to only show active records or if not show all records, also I use a QSFPM connected to a search bar, and I have a lot of features that use functions of my TableModel like rowCount(), index(), and a few others. I have to finish this in a few days so I don't really want to have to go through all my code and try to figure out how to change everything to work with something new. So I would really love it if there were someway for me to use either of these in my Qt Application without having to completely revamp my code. And if I use one of these I wouldn't need the custom QSFPM that reimplements lessThan would I? -
The Stored Procedure was created in a new query window so I don't know if I'll need to bring it in as a Query? I asked but haven't gotten a response yet. If so could I possibly subclass QSqlTablemodel and reimplement either setQuery to not be protected and call it pull in the Stored Procedure? Or make my own public function or reimplement a public function such as setTable() (probably not that one since I don't know what it's actually doing just thought of it since I am calling it at the moment) or something like that that would call setQuery() with the parameter of the Stored Procedure?
-
I actually think that I came up with another way to sort correctly without using any of these methods. I know it's probably not the best way, but since I have such little time left before I have to have it done and can't seem to get the proxy model working and using SQL code, either the stored procedure or a query, would require me to redo a large portion of my program this way will have to do.
What I did was made a new field to sort by in ascending order that never shows up in my output, and I took value of the checkable field (saved as 1's and 0's) for that row and concatenated it with the value of the the datetime field from that row, with an empty space in between the 2 values And I use the convert function on both values to convert them both into varchar. So far, this has seemed to work out fine.
Andre, Thank you for all your help, even though it looks like I am not using the methods you had suggested to me, I understand how a lot of things are working better now.