Details
-
Bug
-
Resolution: Done
-
P2: Important
-
4.7.1, 4.7.2
-
None
-
any
-
8c5f87904a
Description
When a QSqlRelation is set on a QSqlRelationalTableModel, a table name alias is created when columns of the related table share the same name, to avoid column name collisions. This alias is of the form "relTblAl_1" or "relTblAl_2". However, this makes setFilter difficult to use, since the WHERE clause cannot use the original name of a table, but its alias name. In standard SQL, it cannot use the alias name of a column either. Therefore one has to use "relTblAl_1.column_name" in the filter. The problem is that the alias name cannot be found using a public function, but can only be derived from the SELECT clause (using myModel->query().lastQuery()).
It would be more suitable that setFilter() accepts the original table name and transparently replaces it with its alias before bulding the WHERE clause.
To reproduce (using MySQL):
Create two tables : A with 2 columns: idA and name, and B with 4 columns: idB, idA , name and color.
bModel = new QSqlRelationalTableModel(this); bModel->setTable("B"); // we set idA in table B as its foreign key and want "A.name" to be displayed instead of idA bModel->setRelation(1, QSqlRelation("A", "idA","name")); bModel->select(); bTableView->setModel(bModel); bTableView->setColumnHidden(0, true); // works OK, A name column is displayed in the place of idA foreign key bTableView->setItemDelegate(new QSqlRelationalDelegate(regimenTableView)); //now let's apply a filter, the first one works, not the second one: bModel->setFilter("color='red'"); // works, filters and display all B rows with color 'red', no ambiguity //bModel->setFilter("A.name='Steve'"); // NOT OK! Model becomes empty, does not filter according to A.name bModel->setFilter("relTblAl_1.name='Steve'"); // works! A is aliased as relTblAl_1 and that name is used in the WHERE clause bTableView->show();
Using relTblAl_1 should not ne necessary since it is masked to the user of QSqlRelation.
Attachments
For Gerrit Dashboard: QTBUG-15989 | ||||||
---|---|---|---|---|---|---|
# | Subject | Branch | Project | Status | CR | V |
10354,5 | Update QSqlRelationalTableModel about reference table alias | master | qt/qtbase | Status: MERGED | +2 | 0 |