Important: Please read the Qt Code of Conduct -

How to search QSqlTableModel records for value

  • TL;DR: You cannot search a QSqlTableModel records for a value, at least not efficiently. Seriously?

    1. I have a QSqlTableModel.
    2. I have populated it from SQL with the rows in the table.
    3. The table has only one column; it's the primary key, and it's a string.
    4. I have set setSort(0, QtCore.Qt.AscendingOrder), so it's sorted by that column/pk.

    Now all I want to do is see whether a new value is there (before I insert it to be sent to the database). Reasonable? But I see no way to search models, especially given that I know the model is sorted/pk'ed on the column I want. Really?

    Before anyone proposes: no, of course I do not want to issue a new query to the db to discover this. That means I can't interpose a QSortFilterProxyModel and use setFilter...(), because that will generate a new query not search the data, right?? (I wish to goodness the docs there would state one way or the other whether QSortFilterProxyModel works by issuing a new query to the database or sticks with whatever happens to be in a QSqlTableModel etc. and searches that....)

    I have web-searched and found little. (no reply) seems to be exactly the same question.

    So is it really my job to put in code to call model's data() iteratively for every row to implement a search? And if I want it to be efficient implement my own binary search on the row indexes? Or maintain my own hashtable-mapping of the row values?

    OK, I stumbled on QModelIndexList QAbstractItemModel::match(...) const ( Better than nothing, at least that saves me iterating. So in that light let me refine my question:

    With a large number of rows in the model, is there anyway to efficiently (e.g. binary) search rows for a value given that we know which column the model is sorted on and we want to search that (for value equals)?

    Because QAbstractItemModel::match() does not indicate to me that it's going to know about/take advantage of the sortedness of the column it's searching?

    Having had to switch on MySQL logging as the only way to find out what is actually going on when, I'm now thinking that QSortFilterProxyModel around a QSqlTableModel only sorts/filters what's already in the model, it does not cause a new SQL query to be generated.

    I still don't know from the Qt docs what the time order of searching via
    QSortFilterProxyModel::setFilter...() vs QAbstractItemModel::match() is, so don't know what to bother to do. I've gone for QAbstractItemModel::match() for now, as it's less lines of code to add, but I'm not feeling like a particularly satisfied bunny....

Log in to reply