Manually selecting Items from a database



  • Gents;

    I am about to start coding a program that gives the user the choice of selecting items from a database. The database contains a table with few columns (reference, description, unit, price). The program connects to the database through a pushbutton and displays all the items in a dialog window, the user will be able to select the items he wants using a checkbox for example. My question is, is that possible with sql database or is there any other way to do that.

    Regards;


  • Lifetime Qt Champion

    Hi,

    Unless I misunderstand you can use a QSqlTableModel to show the content of the table you are interested in.

    Hope it helps



  • @SGaist

    Yeah, I can display the database in a QSqlTableModel in the dialog window, the question is can I select manually the items I need and then display them in a QTablewidget where I can add the quantity of each item and calculate the price per row?



  • I have two solutions but they depend on a very important question:
    Do any change done in either the Dialog or the final QTableWidget need to be reflected in the DB (i.e. if I change something in the app it gets changed in the database)?



  • @VRonin
    Absolutely not. The database is something fixed, kind of contract that I just want to take items from.

    cheers;



  • flaggableproxy.h

    #ifndef FLAGGABLEPROXY_H
    #define FLAGGABLEPROXY_H
    
    #include <QSortFilterProxyModel>
    
    class FlaggableProxy : public QSortFilterProxyModel
    {
        Q_OBJECT
    	Q_DISABLE_COPY(FlaggableProxy)
    public:
        explicit FlaggableProxy(QObject *parent=nullptr);
        virtual ~FlaggableProxy() = default;
        virtual Qt::ItemFlags flags(const QModelIndex & index) const override;
        virtual void setFlagForColumn(qint32 col, Qt::ItemFlags val);
        virtual void removeFlagForColumn(qint32 col);
        virtual Qt::ItemFlags flagsForColumn(qint32 col) const;
        virtual void setFlagForRow(qint32 row, Qt::ItemFlags val);
        virtual void removeFlagForRow(qint32 row);
        virtual Qt::ItemFlags flagsForRow(qint32 row) const;
        virtual void setFlag(qint32 row, qint32 col, Qt::ItemFlags val);
        virtual void removeFlag(qint32 row, qint32 col);
        virtual Qt::ItemFlags getFlag(qint32 row, qint32 col)const;
        virtual void clearFlags();
    
        virtual void sort(int column, Qt::SortOrder order = Qt::AscendingOrder) override;
        virtual void setSortRoleForColumn(qint32 col, int val);
        virtual int sortRoleForColumn(qint32 col) const;
        virtual void clearSortRoles();
    private:
        QHash<qint64, Qt::ItemFlags> m_flagStorage;
        QHash<qint32, Qt::ItemFlags> m_rowFlagStorage;
        QHash<qint32, Qt::ItemFlags> m_colFlagStorage;
        QHash<qint32, int> m_sortRoles;
    };
    #endif // FLAGGABLEPROXY_H
    

    flaggableproxy.cpp

    #include "FlaggableProxy.h"
    
    FlaggableProxy::FlaggableProxy(QObject *parent)
        : QSortFilterProxyModel(parent)
    {
    
    }
    
    
    Qt::ItemFlags FlaggableProxy::flags(const QModelIndex & idx) const
    {
        const QModelIndex index = mapToSource(idx);
        const qint64 hashIndex = (static_cast<qint64>(index.row()) << 32) | static_cast<qint64>(index.column());        
        if (m_flagStorage.contains(hashIndex))
            return m_flagStorage.value(hashIndex) | Qt::ItemNeverHasChildren;
        if (m_rowFlagStorage.contains(index.row()))
            return m_rowFlagStorage.value(index.row()) | Qt::ItemNeverHasChildren;
        if (m_colFlagStorage.contains(index.column()))
            return m_colFlagStorage.value(index.column()) | Qt::ItemNeverHasChildren;
        return QSortFilterProxyModel::flags(idx) | Qt::ItemNeverHasChildren;
    }
    
    void FlaggableProxy::setFlagForColumn(qint32 col, Qt::ItemFlags val)
    {
        m_colFlagStorage[col] = val;
    }
    
    void FlaggableProxy::removeFlagForColumn(qint32 col)
    {
        m_colFlagStorage.remove(col);
    }
    
    Qt::ItemFlags FlaggableProxy::flagsForColumn(qint32 col) const
    {
        return m_colFlagStorage.value(col, QSortFilterProxyModel::flags(index(0, col)));
    }
    
    void FlaggableProxy::setFlagForRow(qint32 row, Qt::ItemFlags val)
    {
        m_rowFlagStorage[row] = val;
    }
    
    void FlaggableProxy::removeFlagForRow(qint32 row)
    {
        m_colFlagStorage.remove(row);
    }
    
    Qt::ItemFlags FlaggableProxy::flagsForRow(qint32 row) const
    {
        return m_rowFlagStorage.value(row, QSortFilterProxyModel::flags(index(row, 0)));
    }
    
    void FlaggableProxy::setFlag(qint32 row, qint32 col, Qt::ItemFlags val)
    {
        const qint64 hashIndex = (static_cast<qint64>(row) << 32) | static_cast<qint64>(col);
        m_flagStorage[hashIndex] = val;
    }
    
    void FlaggableProxy::removeFlag(qint32 row, qint32 col)
    {
        const qint64 hashIndex = (static_cast<qint64>(row) << 32) | static_cast<qint64>(col);
        m_flagStorage.remove(hashIndex);
    }
    
    Qt::ItemFlags FlaggableProxy::getFlag(qint32 row, qint32 col) const
    {
        const qint64 hashIndex = (static_cast<qint64>(row) << 32) | static_cast<qint64>(col);
        return m_flagStorage.value(hashIndex, QSortFilterProxyModel::flags(index(row, col)));
    }
    
    void FlaggableProxy::clearFlags()
    {
        m_flagStorage.clear();
        m_rowFlagStorage.clear();
        m_colFlagStorage.clear();
    }
    
    void FlaggableProxy::sort(int column, Qt::SortOrder order)
    {
        setSortRole(m_sortRoles.value(column, sortRole()));
        QSortFilterProxyModel::sort(column, order);
    }
    
    void FlaggableProxy::setSortRoleForColumn(qint32 col, int val)
    {
        if (val == Qt::DisplayRole) {
            m_sortRoles.remove(col);
            return;
        }
        m_sortRoles[col] = val;
    }
    
    int FlaggableProxy::sortRoleForColumn(qint32 col) const
    {
        return m_sortRoles.value(col, Qt::DisplayRole);
    }
    
    void FlaggableProxy::clearSortRoles()
    {
        m_sortRoles.clear();
    }
    

    now:

        enum ModelColumns{
            mcReference
            ,mcDescription
            ,mcUnit
            ,mcPrice
            ,mcQuantity
            ,mcTotalPrice
            
            ,ModColCount
        };
        
        QAbstractItemModel* m_model=new QStandardItemModel(this);
        m_model->insertColumns(0,ModColCount);
        m_model->setHeaderData(mcReference,Qt::Horizontal,QStringLiteral("Reference"));
        m_model->setHeaderData(mcDescription,Qt::Horizontal,QStringLiteral("Description"));
        m_model->setHeaderData(mcUnit,Qt::Horizontal,QStringLiteral("Unit"));
        m_model->setHeaderData(mcPrice,Qt::Horizontal,QStringLiteral("Price"));
        m_model->setHeaderData(mcQuantity,Qt::Horizontal,QStringLiteral("Quantity"));
        m_model->setHeaderData(mcTotalPrice,Qt::Horizontal,QStringLiteral("Total Price"));
        
        
        QSqlQuery dataQuery;
        dataQuery.prepare("select Reference,Description,Unit,Price from MyTable");
        if(dataQuery.exec()){
            while(dataQuery.next()){
                const QSqlRecord currRec= dataQuery.record();
                const int newRow=m_model->rowCount();
                m_model->insertRow(newRow);
                m_model->setData(m_model->index(newRow,mcReference),currRec.value(QStringLiteral("Reference")));
                m_model->setData(m_model->index(newRow,mcDescription),currRec.value(QStringLiteral("Description")));
                m_model->setData(m_model->index(newRow,mcUnit),currRec.value(QStringLiteral("Unit")));
                m_model->setData(m_model->index(newRow,mcPrice),currRec.value(QStringLiteral("Price")));
            }
        }
        
        connect(m_model,&QAbstractItemModel::dataChanged,[=](const QModelIndex& idx)->void{
            if(idx.column()==mcQuantity || idx.column()==mcPrice)
                m_model->setData(
                            m_model->index(id.row(),mcTotalPrice)
                            , m_model->index(id.row(),mcQuantity).data().toDouble()*m_model->index(id.row(),mcPrice).data().toDouble()
                            );
        });
        
        FlaggableProxy* dialogModelProxy = new FlaggableProxy(this);
        dialogModelProxy->setSourceModel(m_model);
        dialogModelProxy->setFlagForColumn(0,Qt::ItemIsSelectable | Qt::ItemIsUserCheckable | Qt::ItemIsEnabled);
        
        QSortFilterProxyModel* finalProxy = new QSortFilterProxyModel(this);
        finalProxy->setFilterKeyColumn(0);
        finalProxy->setFilterRole(Qt::CheckStateRole);
        finalProxy->setFilterFixedString(QString::number(Qt::Checked));
    

    finalProxy is the model for the QTableView that will replace the final QTableWidget and dialogModelProxy is the model for the QTableView in the dialog.

    you can use QTableView::setColumnHidden to decide which columns to hide in each table


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.