QSqlRelationalTableModel and complex queries

Panoss

I changed my mind again because I found the code of QSqlTableModel.
So I 'll go subclassing QSqlTableModel.
I 'll just add a new function that will set a query.
What do you think?

Panoss

This post is deleted!

Panoss

How about QAbstractListModel instead of QAbstractItemModel?
Because I have a list (a QTableView), I suppose this is the one I should use.
My header:

#ifndef PGSSQLMODEL_H
#define PGSSQLMODEL_H

#include <QAbstractListModel>
#include <QStringList>

class PGsSqlModel : public QAbstractListModel
{
    Q_OBJECT
public:
    explicit PGsSqlModel(QObject *parent = 0);

    PGsSqlModel(const QStringList &strings, QObject *parent = 0)
             : QAbstractListModel(parent), stringList(strings) {}

     int rowCount(const QModelIndex &parent = QModelIndex()) const;
     QVariant data(const QModelIndex &index, int role) const;
     QVariant headerData(int section, Qt::Orientation orientation,
                         int role = Qt::DisplayRole) const;

private:
    QStringList stringList;

signals:
    
public slots:
    
};

#endif // PGSSQLMODEL_H

My Source:

#include "pgssqlmodel.h"

PGsSqlModel::PGsSqlModel(QObject *parent) : QAbstractListModel(parent)
{
    stringList << "bbb" << "aaa";
}


int PGsSqlModel::rowCount(const QModelIndex &parent) const
{
    return stringList.count();
}

QVariant PGsSqlModel::data(const QModelIndex &index, int role) const
{
    if (!index.isValid())
        return QVariant();

    if (index.row() >= stringList.size())
        return QVariant();

    if (role == Qt::DisplayRole)
        return stringList.at(index.row());
    else
        return QVariant();
}
QVariant PGsSqlModel::headerData(int section, Qt::Orientation orientation, int role) const
{
    if (role != Qt::DisplayRole)
        return QVariant();

    if (orientation == Qt::Horizontal)
        return QString("Column %1").arg(section);
    else
        return QString("Row %1").arg(section);
}

I use set model for a QTableView, but I get a table with two empty rows.
I should get two rows containing:
bbb
aaa

What ust I do?
And how can I access the database?

SGaist

Hi,

How are you setting that model ?

Panoss

Hi SGaist.
It's ok now, I'm getting the expected result. I was just hiding a column (forgotten code) without noticing it (ok, very silly of me :)).

I use this to set the model(if this is what you mean):

PGsSqlModel *manufacturers_tbl_model = new PGsSqlModel;
ui.manufacturers_tbl->setModel(manufacturers_tbl_model);

How can I access the database from within the class?

SGaist

Through QSqlQuery

Panoss

I ended up using QAbstractTableModel, I think this is the one I need.
Now, let 's say I make a function getDataFromDB, and result set is stored in a QStringList (?? I don't know, I 'm asking, maybe you can suggest me what to use).

Then in ::data function, how shall I use this variable to return the data to the "user"? (I mean the QTableView that will use the data to display them)

VRonin

I'd suggest a QHash<quint64,QVariant> where the key has the most significant 32 bits as the row index and the least significant 32 as the column

Panoss

I 've made this function: (I call it of course, like this: model->setQuery("SELECT id, name FROM parts"))

 void TableModel::setQuery(const QSqlQuery &qry) {
     query = qry;
 }

I suppose this is not enough for the model to get the data from db and then be available (I mean the data from db) to the model? (a QAbstractTableModel)
Must I do something more?

And this is the data function:

 QVariant TableModel::data(const QModelIndex &index, int role) const
 {
     if (!index.isValid())
         return QVariant();
     if (index.row() >= query.size())
         return QVariant();
     if (role == Qt::DisplayRole) {
         query.seek(index.row());
         return query.value(index.column()); 
     } else {
         return QVariant();
     }
}

Where I get an error:
...\tablemodel.cpp:74: error: C2662: 'QSqlQuery::seek' : cannot convert 'this' pointer from 'const QSqlQuery' to 'QSqlQuery &'
Conversion loses qualifiers

It's at the line 'query.seek(index.row());'.

SGaist

How did you declared your query member variable ?

Panoss

private:

 QSqlQuery query;

I read from this:
"get rid of the const with const_cast"

What does this mean?

VRonin

"get rid of the const with const_cast"

NOOOOOOOOOOOOOOOOOOO!!!!
mutable QSqlQuery query; solves your compilation issue but I don't think your logic is going anywhere.

Just a second here. let's take a step back: what do you want to achieve?

does the model need to be editable and changes be reflected in the database?
will you have more than 1 column?

Panoss

I want my QTableView to display data from two tables.
This in SQL means my model should have the ability to:

make LEFT JOINS on the second table
Concatenate two fields.

For these things, QSqlQueryModel works fine.

But I also need to add and remove records, which QSqlQueryModel cannot do.

So, my conclusion is I should subclass QSqlQueryModel (this is what I'm doing right now, but I have problems...) and add custom functionality for adding and removing rows.

What do you think?

VRonin

Personally I would not subclass anything, I'd use a QStandardItemModel fill it with a QSqlQuery and react to QStandardItemModel::rowsInserted and QStandardItemModel::rowsRemoved signals to trigger the update in the db via another QSqlQuery

Panoss

The problem is I don't have the slightest idea how to do all this.
Is there some example I could look at?

EDIT: QStandardItemModel has no setQuery function.
How shall I "fill it with a QSqlQuery"?
You mean wit QStandardItemModel::setItem?

VRonin

@Panoss said in QSqlRelationalTableModel and complex queries:

How shall I "fill it with a QSqlQuery"

manually, outside the model

    model->removeRows(0, model->rowCount());
    model->removeColumns(0, model->columnCount());
    QSqlQuery testQuery;
    testQuery.prepare("select * from MyTable");
    if (testQuery.exec()) {
        for (bool firstRun = true; testQuery.next();) {
            const QSqlRecord currRecord = testQuery.record();
            if (firstRun) {
                firstRun = false;
                model->insertColumns(0, currRecord.count());
                for (int i = 0; i < currRecord.count(); ++i)
                    model->setHeaderData(i, Qt::Horizontal, currRecord.fieldName(i));
            }
            const int newRow = model->rowCount();
            model->insertRow(newRow);
            for (int i = 0; i < currRecord.count(); ++i)
                model->setData(model->index(newRow, i), currRecord.value(i));
        }
    }

Panoss

Works, the model->removeRow works, it removes a row from the model ,but how do I remove this row from the db too? Something like this?

QSqlQuery query;
    query.prepare("DELETE FROM MyTable WHERE id=1");
    if (query.exec()) {
etc. etc.

SGaist

You'll likely have to make a more precise delete query but otherwise, yes.

Panoss

What do you mean by "more precise"? I thought "WHERE id=1" makes it absolutely precise (the value "1" was chosen for the shake of the example).

SGaist

Sorry, that was badly written, I meant that you would have to ensure that you are passing the correct parameter(s) to the delete query to ensure you are deleting the same row in the database.