QCompleter and QSortFilterProxyModel - what to do to be able to choose which column to search from-CodePudding

I made a model for data mining in TableView - the code looks something like this and runs in the constructor of the MainWindow class

(...)

leEdtUserSearch = new QLineEdit(this);

modelSqlBorrowingUsers = new SqlQueryModelBorrowingUsers(this, accessDB); //my model inheriting from QSqlQueryModel

tbViewUserList = new QTableView(this); //model table view above

srtFltrMdl = new QSortFilterProxyModel(this);
cmpltrNameAndSurname = new QCompleter(this);

srtFltrMdl->setSourceModel(modelSqlBorrowingUsers);
tbViewUserList->setModel(srtFltrMdl);
tbViewUserList->setColumnHidden(0,true);

cmpltrNameAndSurname->setModel(modelSqlBorrowingUsers);
leEdtUserSearch->setCompleter(cmpltrNameAndSurname);

QObject::connect(bttnGrpUserSearch, &QButtonGroup::idClicked, this, &MainWindow::searchByFirstNameOrLastName); //slot to search from the selected column

//below is theoretically correct search but ... something doesn't work

QObject::connect(leEdtUserSearch, &QLineEdit::textChanged, srtFltrMdl, qOverload<const QString &>(&QSortFilterProxyModel::setFilterRegularExpression));

(...)

slot to search from the selected column

void MainWindow::searchByFirstNameOrLastName(int idSearch)
{
    if(idSearch==1){
        srtFltrMdl->setFilterKeyColumn(1);
    }
    else if(idSearch==2){
        srtFltrMdl->setFilterKeyColumn(2);
    }
}

I probably know what could be wrong, the error may be in the model function - the data seems to be returned correctly but QCompleter and QSortFilterProxyModel see them as empty and I don't know why. Any of you see a bug here?

QVariant SqlQueryModelBorrowingUsers::data(const QModelIndex &index, int role) const
{
    if(this->query().isSelect()){
        if(this->query().first() && this->query().isValid()){
            if(this->query().isActive()){
                QVariant myData;

                if(role==Qt::EditRole || role==Qt::DisplayRole){
                    myData = QSqlQueryModel::data(index, role); //here somehow strangely returned data that QCompleter and QSortFilterProxyModel cannot see
                }

                if(role==Qt::FontRole && index.column()==1){
                    QFont font;
                    font.setWeight(QFont::ExtraBold);

                    myData = font.toString();
                }

                if(role==Qt::TextAlignmentRole && (index.column()==1 || index.column()==2)){
                    return Qt::AlignCenter;
                }

                if(role==Qt::BackgroundRole && index.row() % 2 == 0){
                    QColor color;
                    color.setRgb(242,242,242,255);

                    myData = color;
                }

                if(role==Qt::BackgroundRole && index.row() % 2 == 1){
                    QColor color;
                    color.setRgb(217,217,217,170);

                    myData = color;
                }

                return myData;
            }
        }
    }
    else if(this->query().isValid()){
        return QVariant();
    }

    return QVariant();
}

where is the error and how to correct it?

CodePudding user response：

Ok, let's start with your model class first. I do not review your model editing, only data reading.

Remove ' (apostrophes) from query strings.

What is purpose to pass QSqlDatabase to the model's constructor? Moreover you pass it as pointer. Why, if you can get it anywhere by calling static method QSqlDatabase::database, see docs?

I suggest you to remove QSqlDatabase *dbConnect from constructor arguments.

Everywhere where you need a db connection, you should call QSqlDatabase::database. And you should not store it in a model class.

Further

this->setQuery(userData, *dbConnect);

this->query().exec();

According to the docs setQuery method

Executes the query query for the given database connection db

So, remove this->query().exec();. The same applies to the refresh method.

Remove rowCount method, you do not need to override the one implemented in base class, it returns a query size by default.

columnCount should return the count of columns of the query. Your query SELECT pIDuserWyp, pImie, pNazwisko FROM 'tUzytkownicyWypozyczajacy' has 3 columns, so your columnCount reimplementation should return 3.

Now, let's look at reimplemented data method.

Remove all this unnecessary check-outs

if(this->query().isSelect()){
    if(this->query().first() && this->query().isValid()){
        if(this->query().isActive()){

Your query is a SELECT query, it is valid (it should), it's active, because it is so.

Regarding the use of your model class. Do not share the same instance between different "consumers" (QSortFilterProxyModel, QCompleter, QLineEdit), create the new one for every of them.

CodePudding user response：

@Alexey

Please, show us how and where QSqlQuery is set to the model

the code of the entire model

#include "sqlquerymodelborrowingusers.h"
#include <QDebug>

SqlQueryModelBorrowingUsers::SqlQueryModelBorrowingUsers(QObject *parent, QSqlDatabase *dbConnect) : QSqlQueryModel{parent}
{
    db = dbConnect;

    QString userData = "SELECT pIDuserWyp, pImie, pNazwisko FROM 'tUzytkownicyWypozyczajacy'";

    this->setQuery(userData, *dbConnect);

    this->query().exec();
}

int SqlQueryModelBorrowingUsers::rowCount(const QModelIndex &parent) const
{
    Q_UNUSED(parent);
    int row=0;

    QString rows = "SELECT count(*) FROM 'tUzytkownicyWypozyczajacy'";

    QSqlQuery queryRow(*db);
    queryRow.exec(rows);

    QSqlRecord recRow;
    if(queryRow.first()){
        recRow = queryRow.record();
        row = recRow.value(0).toInt();

        return row;
    }
    else{
        return row;
    }

    return row;
}

int SqlQueryModelBorrowingUsers::columnCount(const QModelIndex &parent) const
{
    Q_UNUSED(parent);
    int column=0;

    QString columns = "SELECT count(*) FROM PRAGMA_table_info('tUzytkownicyWypozyczajacy')";

    QSqlQuery queryColumn(*db);
    queryColumn.exec(columns);

    QSqlRecord recColumn;
    if(queryColumn.first()){
        recColumn = queryColumn.record();
        column = recColumn.value(0).toInt();

        return column;
    }
    else{
        return column;
    }

    return column;
}

QVariant SqlQueryModelBorrowingUsers::headerData(int section, Qt::Orientation orientation, int role) const
{
    if(role==Qt::DisplayRole && orientation==Qt::Horizontal){
        if(section==1){
            QVariant header1;
            header1="Name";

            return header1;
        }
        if(section==2){
            QVariant header2;
            header2="Surname";

            return header2;
        }
    }

    if(role==Qt::DisplayRole && orientation==Qt::Vertical){
        return QString("%1").arg(section 1);
    }

    return QVariant();
}

QVariant SqlQueryModelBorrowingUsers::data(const QModelIndex &index, int role) const
{
    if(this->query().isSelect()){
        if(this->query().first() && this->query().isValid()){
            if(this->query().isActive()){
                QVariant myData;

                if(role==Qt::EditRole || role==Qt::DisplayRole){
                    myData = QSqlQueryModel::data(index, role);
                }

                if(role==Qt::FontRole && index.column()==1){
                    QFont font;
                    font.setWeight(QFont::ExtraBold);

                    myData = font.toString();
                }

                if(role==Qt::TextAlignmentRole && (index.column()==1 || index.column()==2)){
                    return Qt::AlignCenter;
                }

                if(role==Qt::BackgroundRole && index.row() % 2 == 0){
                    QColor color;
                    color.setRgb(242,242,242,255);

                    myData = color;
                }

                if(role==Qt::BackgroundRole && index.row() % 2 == 1){
                    QColor color;
                    color.setRgb(217,217,217,170);

                    myData = color;
                }

                return myData;
            }
        }
    }
    else if(this->query().isValid()){
        return QVariant();
    }

    return QVariant();
}

bool SqlQueryModelBorrowingUsers::setData(const QModelIndex &index, const QVariant &value, int role)
{
    QModelIndex pID = QSqlQueryModel::index(index.row(),0,QModelIndex());
    int _pID = data(pID,Qt::EditRole | Qt::DisplayRole).toInt();

    bool isSet=false;

    if(!index.isValid()){
        return false;
    }
    else if(index.column()<1){
        return false;
    }
    else{
        if(role==Qt::EditRole || role==Qt::DisplayRole){
            if(index.column()==1){
                isSet = name(_pID, value.toString());
                emit dataChanged(index,index,{Qt::EditRole | Qt::DisplayRole});

                this->refresh();
                return isSet;
            }
            if(index.column()==2){
                isSet = surname(_pID, value.toString());
                emit dataChanged(index,index,{Qt::EditRole | Qt::DisplayRole});

                this->refresh();
                return isSet;
            }
        }
    }

    return isSet;
}

Qt::ItemFlags SqlQueryModelBorrowingUsers::flags(const QModelIndex &index) const
{
    Qt::ItemFlags flag = QSqlQueryModel::flags(index); //export domyślnie ustawionych flag przez klasę nadrzędną

    if(!index.isValid()){
        return Qt::NoItemFlags;
    }
    else if(index.isValid()){
        if(index.column()==1 || index.column()==2){
            flag |= Qt::ItemIsEditable; //dopisanie nowej flagi do wcześniej wyeksportowanych flag

            return flag;
        }
    }

    return flag;
}

bool SqlQueryModelBorrowingUsers::name(int pID, const QString &pName)
{
    QSqlQuery queryUpdateName;
    QVariant _pID;

    _pID = pID;

    QString strUpdateName = "UPDATE 'tUzytkownicyWypozyczajacy' SET pImie = ? WHERE pIDuserWyp = ?";

    queryUpdateName.prepare(strUpdateName);
    queryUpdateName.addBindValue(pName);
    queryUpdateName.addBindValue(_pID.toString());

    return queryUpdateName.exec();
}

bool SqlQueryModelBorrowingUsers::surname(int pID, const QString &pSurname)
{
    QSqlQuery queryUpdateSurname;
    QVariant _pID;

    _pID = pID;

    QString strUpdateSurname = "UPDATE 'tUzytkownicyWypozyczajacy' SET pNazwisko = ? WHERE pIDuserWyp = ?";

    queryUpdateSurname.prepare(strUpdateSurname);
    queryUpdateSurname.addBindValue(pSurname);
    queryUpdateSurname.addBindValue(_pID.toString());

    return queryUpdateSurname.exec();
}

void SqlQueryModelBorrowingUsers::myAddRow()
{
    QSqlQuery lastID, addRecord;
    QString strGetID = "SELECT pIDuserWyp FROM 'tUzytkownicyWypozyczajacy'";
    lastID.prepare(strGetID);

    if(lastID.exec()){
        if((lastID.last() && lastID.isValid()) || !lastID.isValid()){
            QString strAddRecord = "INSERT INTO 'tUzytkownicyWypozyczajacy' (pImie, pNazwisko) VALUES (:Imie,:Nazwisko)";

            if(this->pName.isEmpty() && !this->pSurname.isEmpty()){
                emit this->messageOfNumber(1);
            }
            else if(this->pSurname.isEmpty() && !this->pName.isEmpty()){
                emit this->messageOfNumber(2);
            }
            else if(this->pName.isEmpty() && this->pSurname.isEmpty()){
                emit this->messageOfNumber(3);
            }
            else{
                addRecord.prepare(strAddRecord);
                addRecord.bindValue(":Imie",this->pName);
                addRecord.bindValue(":Nazwisko",this->pSurname);

                addRecord.exec();
                addRecord.clear();

                pName.clear();
                pSurname.clear();

                refresh();

                emit this->userDataIsSet(); //sygnał służący do wyczyszczenia QLineEditów po dodaniu danych do bazy
                emit this->messageOfNumber(0); //syngał służący do uruchomienia odpowiedniego komunikatu o błędzie
            }
        }
    }
    else{
        emit this->messageOfNumber(-1);
    }
}

void SqlQueryModelBorrowingUsers::myRemoveRow()
{
    QModelIndex getIDfromTable;
    QVariant getIDtoRemove;

    QSqlQuery currentID, removeRow;
    QString strGetCurrentID = "SELECT pIDuserWyp FROM 'tUzytkownicyWypozyczajacy'";
    currentID.prepare(strGetCurrentID);

    int i=0;

    if(currentID.exec()){
        if(currentID.isSelect()){
            if(currentID.isActive()){
                while (currentID.next()){
                    i=currentID.at();
                    if(i==this->row){
                        getIDfromTable=index(i,0,QModelIndex());
                        getIDtoRemove=getIDfromTable.data();
                    }
                }
            }
        }
    }

    QString strRemoveRow = "DELETE FROM 'tUzytkownicyWypozyczajacy' WHERE pIDuserWyp = (?)";
    removeRow.prepare(strRemoveRow);
    removeRow.addBindValue(getIDtoRemove.toString(),QSql::In);
    removeRow.exec();

    refresh();
}

void SqlQueryModelBorrowingUsers::refresh()
{
    this->query().clear();

    this->setQuery("SELECT pIDuserWyp, pImie, pNazwisko FROM 'tUzytkownicyWypozyczajacy'");

    this->query().exec();
}

void SqlQueryModelBorrowingUsers::setName(const QString &pName)
{
    this->pName = pName;
}

void SqlQueryModelBorrowingUsers::setSurname(const QString &pSurname)
{
    this->pSurname = pSurname;
}

SqlQueryModelBorrowingUsers::~SqlQueryModelBorrowingUsers()
{
    this->query().clear();
}

CodePudding user response：

@Alexey

What is purpose to pass QSqlDatabase to the model's constructor? Moreover you pass it as pointer. Why, if you can get it anywhere by calling static method QSqlDatabase::database, see docs?

I suggest you to remove QSqlDatabase *dbConnect from constructor arguments.

I pass the connection indicator with the base to the model because I have made such a class and I have one question about it.

See the code and tell me if I did it right

AccessToDB::AccessToDB()
{
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setHostName("127.0.0.1");
    db.setPort(-1);
    db.setDatabaseName("dbSpIT.db");
    db.setUserName("");
    db.setPassword("");
}

bool AccessToDB::connectDB()
{
    if(db.isValid()==false){
        errorConnectDB = db.lastError();
        qDebug()<< errorConnectDB.text() << "load driver";

        for(int i=0; i<db.drivers().count();   i){
            qDebug()<< db.drivers().at(i);
        }

        return false;
    }
    else{
        qDebug()<< "the database driver has been properly initialized";

        if(db.open()==true){
            qDebug()<< "database is open" << db.open() << db.connectionName();
            return true;
        }
        else{
            qDebug()<< "database not open";
            return false;
        }
    }
}

AccessToDB::~AccessToDB()
{
    db.close();
}

in the MainWindow class, this way I create a connection to the base

MainWindow::MainWindow(QWidget *parent) : QMainWindow(parent)
{
(...)

accessDB = new AccessToDB();
accessDB->connectDB();

(...)

//at this point I pass the connection from the base to the model

modelSqlBorrowingUsers = new SqlQueryModelBorrowingUsers(this, accessDB);

(...)
}

@Alexey

The same applies to the refresh method.

How can I refresh the view if I have added data to the database?