QTableView/QSqlQueryModel

[norobro]

I have modified the QT example "querymodel" for my database. I issue a select statement that joins two tables and displays the result in a QTableView. I can edit a field and the program updates the appropriate table.

My problem is that when the edited field is exited, the screen is refreshed and the view returns to the first record in the query. There are over 4,000 records in the DB so needless to say it gets kind of old scrolling back down to edit a field in a another record near the bottom of the query.

Is there a way to update the tables and not update the QTableView? Or is there a way to control what the refreshed view displays?

Thanks in advance for any help!!!

Norm

[triperzz]

if thats the case my advice is that dont use QSqlQuerymodel just use ordinary table and save the record into temporary record...

thats my opinion..

[jpn]

QSqlQueryModel is a read-only model so apparently you commit data by hand, don't you? I can understand the problem because re-setting the query causes the whole data model to be rebuilt. From table view's point of view this is basically same than switching to whole different model.

[norobro]

From the Qt docs for QSqlQueryModel:

The model is read-only by default. To make it read-write, you must subclass it and reimplement setData() and flags().

Which is what I've done. Works great.

.... re-setting the query causes the whole data model to be rebuilt. From table view's point of view this is basically same than switching to whole different model.

You're exactly right. I've thought of saving the row number or the index of the row and then performing a seek after refreshing the model to at least have the previous record on the screen when the model is redisplayed but I don't know how you perform a seek on records displayed with a select statement.

[jpn]

So how do you update the data at the moment? Do you reset the query or..? It would work out of the box if you were able to handle it by emitting dataChanged().

[norobro]

Here is some of my code. As I said in my first post, I have just modified (slightly) one of the examples in the QT docs.

Qt::ItemFlags EditableSqlModel::flags(const QModelIndex &index) const
	{
	Qt::ItemFlags flags = QSqlQueryModel::flags(index);
	if (index.column() == 1 || index.column() == 2 || index.column() == 3 || index.column() == 4 || index.column() == 5)
		flags |= Qt::ItemIsEditable;
	return flags;
	}
 
bool EditableSqlModel::setData(const QModelIndex &index, const QVariant &value, int /* role */)
	{
	if (index.column() < 1 || index.column() > 5)
		return false;
 
	QModelIndex primaryKeyIndex = QSqlQueryModel::index(index.row(), 0);
	int id = data(primaryKeyIndex).toInt();
	clear();
	bool ok=FALSE;
	if (index.column() == 1) 	ok = setName(id, value.toString());
	if (index.column() == 2) 	ok = setCountry(id, value.toString());
	if (index.column() == 3) 	ok = setTotal(id, value.toDouble());
	if (index.column() == 4) 	ok = setMachine(id, value.toString());
	if (index.column() == 5) 	ok = setOs_vendor(id, value.toString());
	refresh();
	return ok;
	}
 
void EditableSqlModel::refresh()
	{
	setQuery("SELECT host.id, user.name, user.country, host.total, host.machine, host.os_vendor FROM host left join user on host.id = user.id where user.name IS NOT NULL order by host.total");
	}
 
bool EditableSqlModel::setName(int id, const QString &name)
	{
	QSqlQuery query;
	query.prepare("update user set name = ? where id = ?");
	query.addBindValue(name);
	query.addBindValue(id);
	return query.exec();
	}
 
bool EditableSqlModel::setCountry(int id, const QString &Country)
	{
	QSqlQuery query;
	query.prepare("update user set country = ? where id = ?");
	query.addBindValue(Country);
	query.addBindValue(id);
	return query.exec();
	}
 
bool EditableSqlModel::setTotal(int id, const double &Total)
	{
	QSqlQuery query;
	query.prepare("update host set total = ? where id = ?");
	query.addBindValue(Total);
	query.addBindValue(id);
	return query.exec();
	}
 
bool EditableSqlModel::setMachine(int id, const QString &Machine)
	{
	QSqlQuery query;
	query.prepare("update host set machine = ? where id = ?");
	query.addBindValue(Machine);
	query.addBindValue(id);
	return query.exec();
	}
 
bool EditableSqlModel::setOs_vendor(int id, const QString &Os_vendor)
	{
	QSqlQuery query;
	query.prepare("update host set os_vedor = ? where id = ?");
	query.addBindValue(Os_vendor);
	query.addBindValue(id);
	return query.exec();
	}

Qt::ItemFlags EditableSqlModel::flags(const QModelIndex &index) const
	{
	Qt::ItemFlags flags = QSqlQueryModel::flags(index);
	if (index.column() == 1 || index.column() == 2 || index.column() == 3 || index.column() == 4 || index.column() == 5)
		flags |= Qt::ItemIsEditable;
	return flags;
	}

bool EditableSqlModel::setData(const QModelIndex &index, const QVariant &value, int /* role */)
	{
	if (index.column() < 1 || index.column() > 5)
		return false;
	
	QModelIndex primaryKeyIndex = QSqlQueryModel::index(index.row(), 0);
	int id = data(primaryKeyIndex).toInt();
	clear();
	bool ok=FALSE;
	if (index.column() == 1) 	ok = setName(id, value.toString());
	if (index.column() == 2) 	ok = setCountry(id, value.toString());
	if (index.column() == 3) 	ok = setTotal(id, value.toDouble());
	if (index.column() == 4) 	ok = setMachine(id, value.toString());
	if (index.column() == 5) 	ok = setOs_vendor(id, value.toString());
	refresh();
	return ok;
	}

void EditableSqlModel::refresh()
	{
	setQuery("SELECT host.id, user.name, user.country, host.total, host.machine, host.os_vendor FROM host left join user on host.id = user.id where user.name IS NOT NULL order by host.total");
	}

bool EditableSqlModel::setName(int id, const QString &name)
	{
	QSqlQuery query;
	query.prepare("update user set name = ? where id = ?");
	query.addBindValue(name);
	query.addBindValue(id);
	return query.exec();
	}

bool EditableSqlModel::setCountry(int id, const QString &Country)
	{
	QSqlQuery query;
	query.prepare("update user set country = ? where id = ?");
	query.addBindValue(Country);
	query.addBindValue(id);
	return query.exec();
	}

bool EditableSqlModel::setTotal(int id, const double &Total)
	{
	QSqlQuery query;
	query.prepare("update host set total = ? where id = ?");
	query.addBindValue(Total);
	query.addBindValue(id);
	return query.exec();
	}

bool EditableSqlModel::setMachine(int id, const QString &Machine)
	{
	QSqlQuery query;
	query.prepare("update host set machine = ? where id = ?");
	query.addBindValue(Machine);
	query.addBindValue(id);
	return query.exec();
	}

bool EditableSqlModel::setOs_vendor(int id, const QString &Os_vendor)
	{
	QSqlQuery query;
	query.prepare("update host set os_vedor = ? where id = ?");
	query.addBindValue(Os_vendor);
	query.addBindValue(id);
	return query.exec();
	}

To copy to clipboard, switch view to plain text mode 

[jpn]

The problem is that QSqlQueryModel represents content of a QSqlQuery. It is insufficient to emit dataChanged() because the content of the original query hasn't changed so nothing would change in the view anyway. So, one is forced to set a new query to get anything updated. On the other hand, setting a new query means rebuilding the model from scratch and losing current state of the view. I'm afraid I can't come up with any better solution than storing and restoring current index, possibly together with scroll bar values to retain exactly the same viewport position.

[norobro]

I'm going to try experimenting with the viewport position.

Thank you very much for taking time to think about my problem.

Norm