I have a pretty simple system of 2 views in SQLite DB and 2 tableViews in Desktop Qt Widget Application:
So, we got a strange situation when:
SQL View data in SQLiteStudio (view 'customer_view')
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'
I have some conclusions:
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));
}
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.