QSqlRelationalTableModel sorting incorrectly after MySQL Update

[Scott Krise]

Hello Everyone,

So I have a multi-column browse screen created using QSqlRelationalTableModel. We recently did an upgrade to our the version of MySQL, and when we did, the order in which the data was displayed in the screen changed. See my code below:

ipmodel = new QSqlRelationalTableModel(this);
ipmodel->setTable("wo_routing");
ipmodel->setEditStrategy(QSqlTableModel::OnManualSubmit);
ipmodel->setRelation(6, QSqlRelation("mf_work_center", "work_center", "work_center"));
ipmodel->setRelation(5, QSqlRelation("tt_operator", "operator", "operator"));
ipmodel->setRelation(7, QSqlRelation("mf_cells", "cells", "cells"));
ipmodel->setRelation(8, QSqlRelation("mf_machine", "machine", "machine"));
ipmodel->setSort(2,Qt::AscendingOrder);
ipmodel->setHeaderData(Work_Center, Qt::Horizontal, tr("Work Center"));
ipmodel->setHeaderData(Cell, Qt::Horizontal, tr("Cell"));
ipmodel->setHeaderData(Operation, Qt::Horizontal, tr("Operation"));
ipmodel->setHeaderData(Machine, Qt::Horizontal, tr("Machine"));
ipmodel->setHeaderData(Throughput, Qt::Horizontal, tr("Throughput - PT/HR"));
ipmodel->setHeaderData(Setup_Time, Qt::Horizontal, tr("Setup Hrs"));
ipmodel->setHeaderData(PM_Ratio, Qt::Horizontal, tr("PM Ratio/1"));
ipmodel->setFilter(updateStr);
ipmodel->select();

I've found that if I comment out if I removed the setRelation commands, it sorts properly. Further, if I uncomment the "ipmodel->setRelation(5, QSqlRelation("tt_operator", "operator", "operator"))" line, it works correctly there as well.
Anyone have any thoughts on what might be causing this and what I need to do to fix it? We are using Qt 4.4.3 and we updated from MySQL 5.5.15 to 5.7.13.

Thanks,

Scott

[VRonin]

Since you are using a 10 years old version of Qt it might as well be a bug in QSqlRelationalTableModel or the db driver.

The easiest way to fix this is to use a QSortFilterProxyModel for sorting/filtering instead of sorting directly the model

[Scott Krise]

Not familiar with the QSortFilterProxyModel. Been looking at on line documentation since your response, but not sure how to use that in my scenario. So would I need to replace the QSqlRelationalTableModel with something else, or could the QSortFilterProxyModel be used to sort given my current design? (An example would be very helpful). I need to maintain the current functionality of being able to have lookup capabilities within the screen.

[VRonin]

Very easy:

ipmodel = new QSqlRelationalTableModel(this);
ipmodel->setTable("wo_routing");
ipmodel->setEditStrategy(QSqlTableModel::OnManualSubmit);
ipmodel->setRelation(6, QSqlRelation("mf_work_center", "work_center", "work_center"));
ipmodel->setRelation(5, QSqlRelation("tt_operator", "operator", "operator"));
ipmodel->setRelation(7, QSqlRelation("mf_cells", "cells", "cells"));
ipmodel->setRelation(8, QSqlRelation("mf_machine", "machine", "machine"));
ipmodel->setHeaderData(Work_Center, Qt::Horizontal, tr("Work Center"));
ipmodel->setHeaderData(Cell, Qt::Horizontal, tr("Cell"));
ipmodel->setHeaderData(Operation, Qt::Horizontal, tr("Operation"));
ipmodel->setHeaderData(Machine, Qt::Horizontal, tr("Machine"));
ipmodel->setHeaderData(Throughput, Qt::Horizontal, tr("Throughput - PT/HR"));
ipmodel->setHeaderData(Setup_Time, Qt::Horizontal, tr("Setup Hrs"));
ipmodel->setHeaderData(PM_Ratio, Qt::Horizontal, tr("PM Ratio/1"));
ipmodel->select();
ipModelProxy = new QSortFilterProxyModel(this);
ipModelProxy->setSourceModel(ipmodel);
ipModelProxy->sort(2,Qt::AscendingOrder);
ipModelProxy->setFilterFixedString(updateStr);

now instead of view->setModel(ipmodel); use view->setModel(ipModelProxy);

[Scott Krise]

Ok, so when I tried it like the above, I think it was trying to sort the entire wo_routing table instead of my filtered version? So I added "ipmodel->setFilter(updateStr);" prior to the ipmodel->select as below. Is that correct?

ipmodel = new QSqlRelationalTableModel(this);
ipmodel->setTable("wo_routing");
ipmodel->setEditStrategy(QSqlTableModel::OnManualSubmit);
ipmodel->setRelation(6, QSqlRelation("mf_work_center", "work_center", "work_center"));
ipmodel->setRelation(5, QSqlRelation("tt_operator", "operator", "operator"));
ipmodel->setRelation(7, QSqlRelation("mf_cells", "cells", "cells"));
ipmodel->setRelation(8, QSqlRelation("mf_machine", "machine", "machine"));
ipmodel->setHeaderData(Work_Center, Qt::Horizontal, tr("Work Center"));
ipmodel->setHeaderData(Cell, Qt::Horizontal, tr("Cell"));
ipmodel->setHeaderData(Operation, Qt::Horizontal, tr("Operation"));
ipmodel->setHeaderData(Machine, Qt::Horizontal, tr("Machine"));
ipmodel->setHeaderData(Throughput, Qt::Horizontal, tr("Throughput - PT/HR"));
ipmodel->setHeaderData(Setup_Time, Qt::Horizontal, tr("Setup Hrs"));
ipmodel->setHeaderData(PM_Ratio, Qt::Horizontal, tr("PM Ratio/1"));
ipmodel->setFilter(updateStr);
ipmodel->select();
ipModelProxy = new QSortFilterProxyModel(this);
ipModelProxy->setSourceModel(ipmodel);
ipModelProxy->sort(2, Qt::AscendingOrder);

I then switched the ipmodel to ipModelProxy in the view as you suggested. Its still not sorting properly...and more interestingly maybe, its sorting differently (both wrong, but both different) on systems where the 5.5.15 version and 5.7.13 versions are running. Using the original internal sort function, it was wrong on 5.7.13 but correct on 5.5.13. First of all, is my syntax correct above?

[Scott Krise]

Ok...I think I figured it out. The sort is based on the field position in the table using QSortFilterProxyModel? Not on the field position on the screen? So I was able to get it to sort correctly on both versions of SQL...now one last issue. The screen comes up with appears to be a "line number" as the first column...but its not in the data...is must be auto-generated? So when I sort it the way we need it, those are now out of order. How to I eliminate that column from showing up?

Thanks!

[Scott Krise]

I spoke to soon. The above suggestion corrected my sorting problem, but the setrelation logic seems to have been lost. I can no longer click in the field and use the lookups. This is a show-stopper. I need that feature back. Suggestions anyone?

[SGaist]

Hi,

The only that comes to mind is to test a more recent version of Qt or downgrade for version of MySQL. If you are locked to the Qt 4 series then you should at least go for the latest and last version which is 4.8.7.

[Scott Krise]

Are you an employee of QT? Can it be confirmed that this was a bug in the version we are using and give me the version where it was corrected? Upgrading has been discussed but not acted on in the past...would be good to know that this would be resolved by an upgrade.

[SGaist]

No I'm not (It's Qt by the way)

And as @VRonin already wrote, using such an old version of Qt (that has seen numerous bug fixes in between) with the latest version of MySQL is not necessarily a good idea.

It might be a bug in Qt's MySQL backend as well as something coming from MySQL.

Even if you are not planning an upgrade of Qt, you should try with a more recent version to see if things work better. That is a pretty easy way to check whether it's MySQL or Qt that might be at fault.

You can also check the bug report system.