How do I find a record in a QSqlRelationalTableModel if i have the primary key value?

jdent

I have this

	bool ok = model->insertRecord(-1, record);
	ok = model->submitAll();
	auto id = model->query().lastInsertId();

??

Christian Ehrlicher

Iterate over the model and search for the key.

jdent

@Christian-Ehrlicher as in this sample?::

for (int row = 0; row < model->rowCount(); ++row)
{
	if (model->record(row).value(0).toInt() == id)
	{
		found = true;
		break;
	}
}

is this the best we can do? There is no find(id) method?

JonB

@jdent
No, there is no findId() or similar method, else you would see it on https://doc.qt.io/qt-6/qsqlrelationaltablemodel-members.html.

If you wish to find it efficiently, e.g. if you have a thousand records in memory and don't want to search them sequentially each time, you can add a QSortFilterProxyModel on top of your QSql[Relational]TableModel, or sort the underlying model. Then if you set the sort to be by primary key you know the rows are in ascending primary ley order so you can use a binary search to find a given ID very fast.

You do not need to use such an extra QSortFilterProxyModel for your view, you could just add it for the purpose of searching. You might get away without bothering to add one. Without a sort the statement will be SELECT * FROM table, with no ORDER BY clauses. Many/most SQL databases will deliver rows in primary key order when no ORDER BY is specified. If your ID column is the primary key they will appear in ID order (and so can be binary searched). However I would regard this as "fragile" as it relies on underlying SQL database behaviour.

You could also impose a QSortFilterProxyModel and use its setFilter("id = ...") to pick out a given ID. However this does its work by issuing a SELECT ... WHERE id = ... against the table, not be searching rows already read in, and I don't think this is what you are looking for.