Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/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. https://lists.qt-project.org/pipermail/interest/2012-June/002420.html (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?

    EDIT:
    STOP PRESS!
    OK, I stumbled on QModelIndexList QAbstractItemModel::match(...) const (http://doc.qt.io/qt-5/qabstractitemmodel.html#match). 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?

    EDIT2:
    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