c++sqliteqtuitableviewqsqltablemodel

How to deal: qsqltablemodel does not load view


I have a pretty simple system of 2 views in SQLite DB and 2 tableViews in Desktop Qt Widget Application:

  1. First view is 'seller_view' and it is just printed in App's tableView correctly via QSqlTableModel.
  2. Second is 'customer_view' (setup is the same as above) and it does not print any data, but shows model's headers, that was set up in constructor.

So, we got a strange situation when:

SQL View data in SQLiteStudio (view 'customer_view') enter image description here

Model setup

    // model for tableView
    model = new QSqlTableModel();
    model->setTable("customer_view");
    model->setEditStrategy(QSqlTableModel::OnManualSubmit);
    updateView();
    model->setHeaderData(0, Qt::Horizontal, tr("ID"));
    model->setHeaderData(1, Qt::Horizontal, tr("Ім'я")); // only organizations will have ipn
    model->setHeaderData(2, Qt::Horizontal, tr("К-сть авто"));
    model->setHeaderData(3, Qt::Horizontal, tr("Адреса"));
    model->setHeaderData(4, Qt::Horizontal, tr("Номер"));
    model->setHeaderData(5, Qt::Horizontal, tr("Ел.пошта"));
    model->setHeaderData(6, Qt::Horizontal, tr("ІБАН"));
    model->setHeaderData(7, Qt::Horizontal, tr("Банк"));
    model->setHeaderData(8, Qt::Horizontal, tr("ЄДРПОУ"));
    model->setHeaderData(9, Qt::Horizontal, tr("ІПН"));

    ui->tableView->setModel(model);
    ui->tableView->setColumnHidden(ID_COLUMN_INDEX, true); // hide ID
    ui->tableView->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);

    connect(model, &QSqlTableModel::dataChanged, this, &Customers::handleDataChange);

QTableView in app 'customer_view' enter image description here

I have some conclusions:

  1. QSqlTableModel works fine, because tables(views) names (i checked 10 times) are correct, and it displays columns names correctly, so i can say, that tableView model setup was OK.
  2. SQL View is correct because SQLiteStudio shows the data it should. Again, names of views are correct
  3. If i replace the working model's QSqlTableModel::setTable('seller_view') with 'customer_view', it does not show data, as expected. So, i'm almost sure that the problem is in SQL database.

P.S.: I also tried to change view name 'customer_view' -> 'cus'. Nothing changed

P.S.S.: I set up database in correct way, because changing 'seller' data in SQLiteStudio leads to changing 'seller_view' in App. So, the problem is 100% with the 'customer_view', and i am confused. Maybe query breaks my database?

SELECT customer.id,
           customer.name,
           COUNT(car.id),
           customer.address,
           customer.number,
           customer.email,
           customer.iban,
           customer.bank,
           customer.edrpoy,
           customer.ipn
      FROM customer
           LEFT JOIN
           car ON customer.id = car.customer_id
     GROUP BY customer.id

P.S.S.S:

void Customers::updateView()
{
    model->select();
}

seller_view sql

SELECT seller.id,
           seller.name,
           seller.address,
           seller.number,
           seller.iban,
           seller.bank,
           seller.edrpoy,
           seller.ipn
      FROM seller

P.S.S.S.S (everything from this stuff is working right and updates the table and the view in SQLiteStudio, but is not shown by model as seller_view does):

void Customers::on_btn_add_clicked()
{
    QSqlQuery qry;
    qry.exec("INSERT INTO customer(name) VALUES(NULL)");

    updateView();
}

void Customers::on_btn_del_clicked()
{
    const auto selected_indexes = ui->tableView->selectionModel()->selectedIndexes();
    QSqlQuery qry;
    if (!selected_indexes.isEmpty())
    {
        qry.exec("PRAGMA foreign_keys=ON");
        qry.exec("DELETE FROM customer WHERE id=" + selected_indexes.at(0).siblingAtColumn(ID_COLUMN_INDEX)
                 .data(Qt::DisplayRole).toString()
                 );
    }

    updateView();
}

void Customers::handleDataChange(const QModelIndex &topLeft, const QModelIndex &)
{
    const QVector<int> notEditableColumns = {
        2 /* Cars count is NOT EDITABLE */
    };
    const int column = topLeft.column();
    const QString data = topLeft.data(Qt::DisplayRole).toString();
    const QString customer_id = topLeft.siblingAtColumn(ID_COLUMN_INDEX).data(Qt::DisplayRole).toString();

    if (!notEditableColumns.contains(column)) {
        switch (column) {
        case 1: {
            handleSimpleCellChange("name", data, customer_id);
        } break;
        case 3: {
            handleSimpleCellChange("address", data, customer_id);
        } break;
        case 4: {
            handleSimpleCellChange("number", data, customer_id);
        } break;
        case 5: {
            handleSimpleCellChange("email", data, customer_id);
        } break;
        case 6: {
            handleSimpleCellChange("iban", data, customer_id);
        } break;
        case 7: {
            handleSimpleCellChange("bank", data, customer_id);
        } break;
        case 8: {
            handleSimpleCellChange("edrpoy", data, customer_id);
        } break;
        case 9: {
            handleSimpleCellChange("ipn", data, customer_id);
        } break;
        }
    }

    updateView();
}

void Customers::handleSimpleCellChange(const QString &columnName, const QString &data, const QString &customer_id)
{
    QSqlQuery qry;
    const QString query_str = "UPDATE customer SET %1=%2 WHERE id=%3";
    qry.exec(query_str.arg(columnName, data, customer_id));
} 

Solution

  • Since you're not using the model to directly write to the underlying tables, I would give QSqlQueryModel a try: just use it instead of QSqlTableModel, and replace your

    model->select();
    

    with

    model.setQuery("select * from customer_view");
    

    Since this model is read only, you may want to drop the model/view strategy altogether, and fall back to using a QSqlQuery and populating a QTableWidget with its result. This method should be faster than using a subclassed model that provides data/setData implementations, also considering you already wrote code to edit the db on a single-cell data change basis.