Uploaded image for project: 'Qt'
  1. Qt
  2. QTBUG-15989

QSqlRelationalTableModel aliasing makes setFilter() troublesome to use

    XMLWordPrintable

Details

    • Bug
    • Resolution: Done
    • P2: Important
    • 5.0.0, Some future release
    • 4.7.1, 4.7.2
    • SQL Support
    • 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

        Activity

          People

            zhongle honglei zhang
            zeb Zebulon
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Gerrit Reviews

                There are no open Gerrit changes