QSqlRelationalTableModel and setFilter difficulties - possible bug?
Hi,
I have spotted an issue with setFilter() and relations when column names are colliding.
I have two tables: A with 2 columns: idA and name, and B with 4 columns: idB, idA, name and color. I want to display B table with support of relation using idA as its foreign key. The FK column from A should be its name. I also want to filter this table view according to the FK name:
- bModel->setTable("B");
- bModel->select();
- bTableView->setModel(bModel);
- bTableView->setColumnHidden(0, true);
- // works OK, A name column is displayed in the place of idA foreign key
- //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'
- //bModel->setFilter("name='Steve'"); // NOT OK! Model becomes empty, does not filter according to A name
- bTableView->show();
the problem, it seems, is the collision between the column names. Both A and B tables have “name” columns (which of course are different data). What I noticed is that without filter the FK column is renamed in the view (e.g. A_name_2) but even using this column name in the filter does not work.
How can I handle this? Thanks a lot in advance.
5 replies
The API docs of QSqlRelationalTableModel [doc.trolltech.com] covers that case:
If a relation’s display column name is also used as a column name in the main table, or if it is used as display column name in more than one relation it will be aliased. The alias is is the relation’s table name and display column name joined by an underscore (e.g. tablename_columnname). All occurrences of the duplicate display column name are aliased when duplication is detected, but no aliasing is done to the column names in the main table. The aliasing doesn’t affect QSqlRelation, so QSqlRelation::displayColumn() will return the original display column name, but QSqlRecord::fieldName() will return aliases.
So, according to the docs, if you filter on B.name you should use name=‘xy’; if you filter on A.name you should use A_name=‘abc’.
What does
- bMode->query().lastQuery()
print out?
Hi,
Thanks for your answer, which helps me a lot tracking the issue.
The result of lastQuery() is:
- SELECT B.`idB`, relTblAl_1.name AS A_name_2, B.`name`, B.`color` FROM B, A relTblAl_1 WHERE (B.`idB` = relTblAl_1.idB) AND (A_name_2='Steve')
when I use as the filter (the alias name of the column):
- bModel->setFilter("A_name_2='Steve'");
so obviously this does not work: A is aliased as reTblAl_1, and I cannot use A_name_2 in the WHERE clause. In MySQL, such SELECT command returns me: Error: code 1054 Unknown column name ‘A_name_2’ in ‘where clause’.
If I use:
- bModel->setFilter("reTblAl1.name='Steve'");
then the filter works!
Is that a problem with MySQL itself, which does not accept column aliases in the WHERE clause? In that case, should it be corrected in Qt?
Besides, can I rely on the reTblA1 alias? Is there any function that returns this name?
Thanks.
I found this in the MySQL manual: [dev.mysql.com]
Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined. For example, the following query is illegal:
- SELECT id, COUNT(*) AS cnt FROM tbl_name
- WHERE cnt > 0 GROUP BY id;
Therefore this confirms that one cannot use a column alias in setFilter(). But the problem is still there: how can I know the table alias name?
I have reported the problem on the QTBUG dashboard at https://bugreports.qt.nokia.com/browse/QTBUG-15989 a while ago but still have not got any answer. Is there any progress on this issue? Because the API doc is still erroneous. One way to fix it would be to have a public function in QSqlRelationalTableModel that would return the name of the table alias. Are there Qt developers who could comment on this proposal?
Thanks in advance.
[EDIT: fixed jira link, Volker]
You must log in to post a reply. Not a member yet? Register here!
