Inner join use QSqlTableModel

[primus454]

Hi everyone,

I am working on a music player and one thing I have been struggling with is QSql database. Right now I have my main library database with primary key ID. I want to drag and drop songs to my playlist which is currently a :memory: database. The information is exactly the same except the primary key of the playlist is playlist_ID. Currently I am copying all the the records from the library to the playlist. This is okay when not to much is being moved, but if you want to load the whole library (e.g. shuffle mode) hangs for longer than I like.

I am wondering if it is possible to do a full inner join (I would use a table in the library database instead then) such that my playlist table only contains

playlist_ID, ID

and the rest like title, artist, album, etc... are filled in by the join. The goal is to only have to copy over the ID's during drag events instead of all 35 or so fields per record. I looked into using QSqlQuery model a but I want to be able to edit the fields from the playlist and it seems like there must be a better solution than just recreating QSqlTableDatabase.

Thanks!

[anda_skoa]

Maybe with a QSqlRelationalTableModel?

Cheers,
_

[primus454]

Maybe with a QSqlRelationalTableModel?

Cheers,
_

I looked at that, but from what I could gather, it is strictly used on a 1 foreign key:1 value basis. I don't see where it allows joining multiple columns, even though there is clearly the joinMode enum.

[anda_skoa]

Hmm, ok, haven't use that model myself yet.

If it can't be used, then you'll have to implement a table model yourself by deriving from QAbstractTableModel and implementing columnCount(), rowCount() and data() and using QSqlQuery internally.

Cheers,
_

[ChrisW67]

The docs contain an exampe editable QSqlQueryModel (http://doc.qt.io/qt-5/qtsql-querymodel-example.html) that might serve as a starting point.

Another approach might be to use a model that lazy loads the entry details only when first requested. Drop would insert only the id, and data() would fetch the details of the entry (possibly cache them) when a view/consumer asked for the values.