Navigating QSqlTableModel and QTableView in very large databases

q&a
, , , , , ,
1

After reading Martin’s excellent book, looking through tutorials, building some UI’s, I must confess I’m missing something fundamental about navigating through the TableViews when they are showing large databases. I’m building an app for my son, a sociolinguist. These folks search through enormous amounts of data from social media looking for word patterns of interest. Currently we have a database of 668,000 rows, each containing a transcipt of up to 30 seconds of conversation, maximum 2K characters, average length of 80 characters.

The use case is to filter on phrases of interest, then when found, unfilter, and jump to the same record of interest so as to see the rest of the conversation around that phrase.
Finally, click a button that launches the youtube video and start it at that phrase. I have all that working, EXCEPT the jump to the same record upon filter change. Also, we need to save the user’s place and restore it when restarting the app. The user can spend days searching.

Qt automatically pages this large dataset beautifully, in 256 record chunks.

So I thought, ok, use model.match to find, say, the record where the UID = 1000. But match only searches through the 256 records currently being displayed. There are several use cases where I know the record key from the database (UID), and I need to find and display that record and surrounding records in the tableview.

If I had the index of the record, I could do table.setCurrentIndex(index). Ah, but how do I get the index? If I knew the row, I could do table.selectRow(), except that it, too, is limited to the 256 records in the current page. table.selectRow(1000) throws no error, but does nothing.

This is starting to sound like a job for a proxy model. Filter for the UID so there is only one row that matches, then get the matching index in the source model and do sourcetable.setCurrentIndex(the found index).

Surely it’s not that hard, and I’m missing something fundamental.
Please advise.
thanks in advance.

2

The 256 row limit applies to filtering with a proxy model. UID is the integer key for the table. I got a TableView with a proxy model going, and can filter for e.g., UID 255 just fine. But not for UID 256. As expected, once I filter for and display UID 255 I can filter for the next 255 UIDs, as they are evidently loaded into the QTableView. But nothing past that.

This seems to leave me with three options that I can think of.
First, do some kind of “loop till end of database” and do Match, or SelectRow, or a proxyfilter inside the loop. I’ll bet that is prohibitively slow on a db of 600,000 records.

Second, create a second model and second tableview, filter the second model with self.model2.setFilter("UID >= 1000) (for example) If the user then does PgUp, I’ll have to reset model2 with a new WHERE clause. Ick

Third, abandon the desired UI consisting of a single TableView that toggles between filtered and unfiltered states, while keeping the same row selected. Replace it with a two TableView window (kind of like the two TreeView window in basicsortfiltermodel.py in the examples). Then for each selectionChanged event in the TableView, do some sort of selectRow in the unfiltered Table view, so the same record appears in both. The unfiltrered TableView then shows the context of the conversation, not just the rows with the phrase of interest to the user. That still leaves me without a way to just GoTO UID 43,000 without filtering the model via a WHERE clause, and so complicating things when the user decides to scroll or page toward the front of the record set. In protyping sessions I have seen this happen a lot as the user compares two different conversations, separated by thousands of rows, to see which best suits the research project he has in mind.

1 Like
3

I implemented option 3 above, writing my own crude paging logic. I use two separate sqltablemodels, each with a separate tableview, and using keyPressed, the selectionChanged signal, and the valueChanged.minimum signal for scrollbars. For those signals I reapply tablemodel.setFilter to the appropriate tablemodel, and scrollTo and selectRow for the Tableviews. The app is out to the user and so we’ll see how well this scheme works.