QSqlRelationalTableModel and complex queries

Panoss

I was using QSqlRelationalTableModel for making the model of a QTableView.
But I had to had some complex queries (LEFT JOINS, plus concatenating fields) (can I concatenate fields with QSqlRelationalTableModel? I think not).
But, QSqlRelationalTableModel cannot accept queries (the ->setQuery is private), only the ->setTable() works.
So I replaced QSqlRelationalTableModel with QSqlQueryModel.

Is this the best approach or am I missing something?

VRonin

The huge difference is that one is editable and the other isn't. If you just need to display data then QSqlQueryModel should work for you, if you need to edit the model and changes be reflected in the database you'll have to implement your own model or use a combination of QStandardItemModel and QSqlQuery

Panoss

@VRonin said in QSqlRelationalTableModel and complex queries:

if you need to edit the model and changes be reflected in the database you'll have to implement your own model

I 'm thinking of implementing my own model, but I 'll need some help.
It's the QSqlTableModel I 'ii have to subclass, right?
EDIT: abandoned, too complicated, I 'll just use QSqlQueryModel and QSqlQuery. (about QStandardItemModel: I read some thing, but didn't understand much).

VRonin

@Panoss said in QSqlRelationalTableModel and complex queries:

It's the QSqlTableModel I 'ii have to subclass

I'd start from scratch with a QAbstractItemModel

about QStandardItemModel: I read some thing, but didn't understand much

a good starting point is http://doc.qt.io/qt-5/modelview.html

Panoss

Thanks VRonin, but as I mentioned, I abandon the subclassing.

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?