Is it possible to Convert a QSqlQuery Model to a QsqlTableModel

Asimov

I have a bit of a problem. I am using a QsqlTablemodel because it is the easiest to edit the cells and everything.
The problem with the tablemodel is that it only uses the filter command.

I need to do a complex query while joining two tables together. The problem is that you can't use the tablemodel for queries, and the trouble with QSqlquerymodel is that it cannot be edited.

So is it possibl to do the query in QSsqlquerymodel and then convert this to a QSqltablemodle so that I can edit my table afterwards.

If this is not possible, then how do you do a query model where you can edit?

VRonin

Do you need the changes you make to be sent back to the DB?

Asimov

Yes I do need it to update my database when data is changed. At the moment I have single click to select the line and double click to edit the data, my original model is made like this

void DbManager::boxList(Ui::MainWindow* ui){
    modelBox=new QSqlTableModel();
    modelBox->setTable("boxes");
    modelBox->select();
    modelBox->setHeaderData(1, Qt::Horizontal, QObject::tr("Box ID / Name"));
    modelBox->setHeaderData(2, Qt::Horizontal, QObject::tr("Box Location"));
    ui->boxTable->setModel(modelBox);
    ui->boxTable->setColumnHidden(0, true);
    ui->boxTable->setStyleSheet("QHeaderView::section { background-color:#d1d1d1;border-color:black; }");
}

I need to do a joined query on two tables, and you can't do a query with QSqlTableModel. I know you can do a filter, but it is not good enough for my purposes in this occasion.

VRonin

if QSqlRelationalTableModel is ok for your needs use that one otherwise use a QStandardItemModel and manually fill it and react to changes

Asimov

Hi VRonin,

I am trying to do this kind of query

SELECT `boxID`,`boxName`,`boxLocation` from `boxes`
LEFT JOIN 	`boxContents` on `boxes`.`boxID`=`boxContents`.`boxID`
where `itemName` Like ?

It is a one to many relationship.
Does that give you any clue as to whether I should be using a QSqlRelationalTableModel or QStandarditemModel.

The problem is that the QsqlTableModel lets me edit the cells and save to the database.
If I use th eQStandardModel, I think I have to do all that manually in code, which would be a shame, as all I am building is a search function.

In the future is there no possibility to add a query to a QSTableModel, or is there no way to do this?

You see I mainly work in php and mysql and for this project I am using QT and mysqli, so I have a bit to learn.

VRonin

I'm afraid you have to do it manually. there is no way for the framework to find out what record to update in the db so I doubt it will ever be implemented.

Asimov

Thanks Ronin I have worked out how to use the QStandardItemModel.

void DbManager::boxList(Ui::MainWindow* ui){

    QSqlQuery* query=new QSqlQuery(mydb);
    query->prepare("select `boxID`,`boxName`,`boxLocation` from `boxes`");
    query->exec();
    modelBox= new QStandardItemModel(sqlSize(query), 3);
    int i=0;
    while (query->next())
    {
        modelBox->setData(modelBox->index(i, 0), query->value("boxID"));
        modelBox->setData(modelBox->index(i, 1), query->value("boxName"));
        modelBox->setData(modelBox->index(i, 2), query->value("boxLocation"));
        i++;
    }
    modelBox->setHeaderData(1, Qt::Horizontal, QObject::tr("Box ID / Name"));
    modelBox->setHeaderData(2, Qt::Horizontal, QObject::tr("Box Location"));
    ui->boxTable->setModel(modelBox);
    ui->boxTable->setColumnHidden(0, true);
}

So now I can use this to do my query. Good thing is that most of my functions work, but I am still having a small problem.
I have got delete to work, but getting the id of the clicked line and then doing a query that is good, but I am having trouble with the enter key.

I have it set that when I double click on a line it goes into edit mode. Obviously I have to handle saving to the database on my own, but I don't know how to run my event when the enter/return key is pressed.

Basically I need to catch the return key event, and run my code to add the line to the database. I have looked through the list of slots for the table key, and tried a few but none of them seem to work when the return is pressed.

I have tried:
entered(QModelIndex)
pressed(QModelindex)

but none of these work.
So how do I catch the enter/return key in edit mode in a QTableView?
(I haven't started a new post to ask this question because it is on the same subject, but I can if I am supposed to)

SGaist

Hi,

You can use the dataChanged signal so you can retrieve the modified data and do what you need with it.

On a side note, you have a memory leak. You never delete your query object. Not that there's no need to create it on the heap at.

Asimov

Hi SGaist,

I have looked through the slots for the QTableView and I can't see a slot that says datachanged.
eg

activated
clicked
doubleClicked
entered
iconSize
pressed
viewportEntered
destroyed
objectNameChanged
customerContextMenuRequested
windowIconChanged
windowIconChanged
windowIconTextChanged
windowTitleChanged

And the documents are not very clear how to implement a datachange. There is a lot in the docs that show you what the command does, and does not give an example.

I didn' t know I had to delete the query object. In php you have query->close(), but I didn't know there was a similar thing in QT. I am going to have to look up how to do this now.

I have recently spent more time in php and mysql coding than I have doing C++, so I am a bit rusty and new to QT. In the past I have used visual studio and Codeblocks.

Thanks for the tip. Going to look into this now.

PS. I put this into my mainwindow.cpp constructor, but it didn't do anything

 connect(ui->boxTable, SIGNAL(dataChanged(QStandardItem*)),this,SLOT(on_edit_clicked()));

So still searching for an answer why it isn't working.

SGaist

Because it's not a slot from QTableView but a signal from your model base class.

You are allocating the query object on the heap, you have to delete it once you're done using it. It's nothing Qt specific, it's C++ object life time management. Like I wrote, in your case the simple solution is to allocate it on the stack.

The connect statement will show a warning at run time. I'd recommend moving to the new Qt 5 notation. Also the signal matches a QTableWidget but not a QTableView.

Asimov

Hi SGaist,

I have tried again this time I am using my model which is created in DbManager class. In the header file the class is set up DbManager myDatabase; and modelBox which is my model is created in that class.

connect(myDatabase.modelBox, SIGNAL(dataChanged(const QModelIndex&)),this,SLOT(test()));

I am just getting it to respond, but it isn't

void MainWindow::test(){
    qDebug() << "signal";
}

So the signal does not seem to be working. I am very perplexed.
I have tried to look at the new qt5 format and that is even more confusing. I don't even now where to put the callback on that. Once I have got the old one to work, I might look into the new one again.

On another note I am now using

delete query;

Which should free up the memory on the stack hopefully.

PS. I am getting this error

QObject::connect: No such signal QStandardItemModel::dataChanged(const QModelIndex&)
QObject::connect:  (receiver name: 'MainWindow')

Asimov

Hi,

After making my last post I decided to call connect from my DbManager class like this

void DbManager::boxList(Ui::MainWindow* ui){
    QSqlQuery* query=new QSqlQuery(mydb);
    query->prepare("select `boxID`,`boxName`,`boxLocation` from `boxes`");
    query->exec();

    modelBox= new QStandardItemModel(sqlSize(query), 3);
    int i=0;
    while (query->next())
    {
        modelBox->setData(modelBox->index(i, 0), query->value("boxID"));
        modelBox->setData(modelBox->index(i, 1), query->value("boxName"));
        modelBox->setData(modelBox->index(i, 2), query->value("boxLocation"));
        i++;
    }
    modelBox->setHeaderData(1, Qt::Horizontal, QObject::tr("Box ID / Name"));
    modelBox->setHeaderData(2, Qt::Horizontal, QObject::tr("Box Location"));
    ui->boxTable->setModel(modelBox);
    ui->boxTable->setColumnHidden(0, true);
    delete query;
    QObject::connect(modelBox, SIGNAL(dataChanged(const QModelIndex &, const QModelIndex &)),modelBox,SLOT(updateBoxList(ui,const QModelIndex & , const QModelIndex &)));
}

The good news is that I have now got rid of the signal error, but now I am getting a slot error like this

QObject::connect: No such slot QStandardItemModel::updateBoxList(ui,const QModelIndex & , const QModelIndex &)

In my DbManager class header I have

public slots:
        void updateBoxList(Ui::MainWindow *ui,const QModelIndex & , const QModelIndex &);

SGaist

Then, why do you pass modelBox as the object containing that slot ?

Again, there's no need to allocate query on the heap, use a local stack variable, that's enough.

Asimov

I used modelBox as the container of the slot because originally I used this, but then I got an error by doing this. Then I changed it to modelBox the name of my model. Anyway I changed it back to this, and it still says there is no slot. I am getting a little confused.

modelBox is the name of the model, which holds the data for the table. You said in your last post not to use the QTableView but the name of the model. So I used the name of the model instead of ui->boxTable which was the name of the QTableView.

I don't know how to use a local stack variable.
It has been a couple of years since I have done any C++, and I am trying to get back into it.
Also I have only been using QT for about a week, so I am new at all this, and having to learn fast.

SGaist

From the code you posted, the slot is in DbManager.

Note that you are trying to do something wrong here. It seems you try to pass the ui variable in SLOT which is not possible.

Since it seems that you manage the model directly in DbManager, there's no need to involve your MainWindow class. Once you update modelBox, it will trigger updates in boxTable automatically.

Asimov

Well SGaist you have solved my signal problem. Taking out the ui worked. My signal is now firing and running my update function, thankyou.

The problem is that I have been passing the ui so I can update the ui. You see when I call void DbManager::boxList(Ui::MainWindow* ui) I send in the ui because my class has no access to the ui, so if I don't pass it in, I can't update anything on the window. So I pass the ui from MainWindow to my class DbManager. This is why I was sending the ui to my function in the slot.

If my function needs to update any ui element then it won't be able to because I have not passed it the ui.
Hmm just had an idea, perhaps when I setup my Dbmanager class I can pass the ui in the contructor and then all my functions will have acces to the ui.

I think I solved the local stack problem now. I had to change query-> to query. all through the code. Here is my new code.

void DbManager::boxList(Ui::MainWindow* ui){
    QSqlQuery query;
    query.prepare("select `boxID`,`boxName`,`boxLocation` from `boxes`");
    query.exec();
    modelBox= new QStandardItemModel(sqlSize(query), 3);
    int i=0;
    while (query.next())
    {
        modelBox->setData(modelBox->index(i, 0), query.value("boxID"));
        modelBox->setData(modelBox->index(i, 1), query.value("boxName"));
        modelBox->setData(modelBox->index(i, 2), query.value("boxLocation"));
        i++;
    }
    modelBox->setHeaderData(1, Qt::Horizontal, QObject::tr("Box ID / Name"));
    modelBox->setHeaderData(2, Qt::Horizontal, QObject::tr("Box Location"));
    ui->boxTable->setModel(modelBox);
    ui->boxTable->setColumnHidden(0, true);
    QObject::connect(modelBox, SIGNAL(dataChanged(const QModelIndex &, const QModelIndex &)),this,SLOT(updateBoxList(const QModelIndex & , const QModelIndex &)));
}

SGaist

Then your design is not good. You are creating a tight coupling that doesn't do much good.

DbManager should not need to know MainWindow nor should it car about it. From what I gathered from your code, it should only operate on the model. If you need your MainWindow to do stuff based on actions from DbManager, then you should implement it through signals and slots. Make your DbManager share information as needed and plug your MainWindow on that. Doing so will make your encapsulation cleaner and if you need to change anything in your MainWindow to modify your GUI then you won't have to change anything in your DbManager class.

Asimov

@SGaist

Partly I agree with you, but it could cause me a few problems.
My DbManager class is to do all database related functions.

In my previous post I showed my function boxList which sets up the model from the database and this is in my Dbmanager class, but without the ui, I couldn't run the following line.

ui->boxTable->setModel(modelBox);

I think I could have more ui functions in MainWindow, and limit my use of ui in my DbManager class.
So I agree in principle, but whether I can get it to work is another thing LOL

SGaist

The request the model from your DbManager through a getter and let the MainWindow set it to whatever widget you want for this task. This will again make the DbManager independent from the GUI.

Asimov

I think I understand.
So I can have a function in DbManager like

QStandardItemModel DbManager::getModelBox()
{
     return modelBox;
}

Then in Windows Main I can call it like this

myDatabase.getModelbox()->setModel(modelBox);

I am going to give tihs a go now. Thanks.

PS. After playing with it for a while I realise my code above was wrong. So here is the code that worked, in case anyone is following this thread

QStandardItemModel* DbManager::getmodelBox()
{
     return modelBox;
}

And in MainWindow I can call it like tihs

ui->boxTable->setModel(myDatabase.getmodelBox());

Thanks SGaist I have learnt a lot. Will mark this thread as solved, even though I got a lot of answers, thanks