[SOLVED] QSqlRelationalTableModel doesn't 'see' the data in my database

[HeavenOfSevens]

I'm a Qt beginner, building my first real Qt application, and I have, so far, been impressed with the library, and how things 'just work' without me having to coax the compiler into running my code. I have, however, an issue where my code does not 'just work', and I am sure that it is a problem of my making rather than Qt's, but I can't find it! I'm sorry if I've missed obvious documentation here, but I can't seem to solve the problem...

I have created a database connection with QSqlDatabase::addDatabase(), using a SQLite driver, which is the only connection in the program. I know that this connection works, as I can successfully run queries on it, inserting tables and retrieving records, both with QSqlQuery objects and the deprecated QSqlDatabase::exec() function. I have also added QT += sql to my .pro file.

I have declared a model in the header file:

@QSqlRelationalTableModel *newWords;@

And I initialise it in my source like this:

@newWords = new QSqlRelationalTableModel(this);
newWords->setTable("words_learning");
newWords->setRelation(WordsKnown_topic, QSqlRelation("topics", "id", "name"));
newWords->setEditStrategy(QSqlRelationalTableModel::OnFieldChange);
newWords->select();@

My belief is that this code is equivalent to the query

@SELECT * FROM words_learning@

where the topic field of words_learning (WordsKnown_topic, part of an enum I have set up which defines the column numbers, starting from 0, in the same order as I have created them in the table) is a foreign key which references the id field of the table topics, and which should show the user the name field which corresponds to that id field.

I have used an external SQL editor to add two valid records to the database, and have this debug line:

@qDebug() << newWords->rowCount();@

I expect this to output 2. Instead, I get 0.

According to all the QtSQL resources I have found, I have set up everything correctly, but I cannot make newWords 'see' the two records in my table, even if I add QSqlDatabase::database() as the second paramater to the QSqlRelationalTableModel constructor. I have also tried using a QSqlTableModel and commenting out the relation, but this doesn't work either.

I would appreciate any assistance in solving this; I'm almost certain it's something blatantly obvious that I've overlooked.
Thank you in advance.

[HeavenOfSevens]

Does anybody have any idea what my issue is? Or should my code as written work? I don't mean to be impatient, but the topic seems to be slipping away into the later pages of the forum...

[Hostel]

QSqlRelationalTableModel don't makes a simple select * from table. It makes join if you set relation. If you insert a data only to words_learning then your model will return 0 rows because it will execue join - not left join.

[HeavenOfSevens]

Thank you for your reply. This was something I did not appreciate, and I have now inserted a record in the "topics" table referenced by the foreign key in "words_learning", and set the foreign key for the two records in "words_learning" to this record. However, having done this, I still get an output of 0 rows when running my code. If I include a call to newWords->setFilter(), setting a filter that matches one of the records, and then call newWords->select() again, I still have the same problem of 0 rows. Is there something else I'm overlooking?

[Hostel]

There is something in:
@
model->lastError();
@

Try see a sql query by:
@
model->query().lastQuery();
@
Maybe this will show what is going wrong.

[HeavenOfSevens]

Thank you very much - that's helped me to solve the problem! For some reason, the model was turning the wrong field into a primary key, so I swapped the enum I'd defined for the raw number, and it started working! Then, I put the enum back, and it still works! I've no idea what went on there, but it's fixed now, so thank you everybody for your help.

[Vaquita Tim]

I have had just the same problem as this - that is, when using a QSqlRelationalTableModel and using setRelation to a second table, no records were found. However, removing the setRelation call results in a correct records being found.

There was no error message, and the Query().lastQuery() looked okay (I'm not an SQL expert though).

Fiddling with the field number didn't help, and I couldn't see any difference in the model->primaryKey()

Finally I pinned the problem down to the database schema - the "relational" field (i.e. equivalent to the WordsKnown_topic field in the words_learning table above) was marked as "NULL" (allowed). Changing this to "NOT NULL" fixed it.

I'm using MS SQL via ODBC. I'm guessing that the main issue is that you don't get an error message - it makes some sense to me that this field should be NOT NULL.

[Bogdan Onyshenko]

Kind of stupid on my side, I had a same issue just now. But I scrued up that column count starts in set relation counts from 0. But, I discovered somethin new for myself for example how to get last query and sql error if any. Thanks for creating this topic and to all who responded :)