So I been reading an working on figuring out how to implement a sorting feature using sortProxyFilter class But I'm little confused on how it works with the model... I know its filters between the QML and the model data. I want to search by userName (separately) want to search by Date (separately) and then be able to search by userName and date... I was thinking at first I could use sql script just to select the data from the data base then display using model but not sure how that would work with re-running slq statement then refreshing model. Seems like every one uses a ProxyFilter class to filter tableView.
I started by creating a Proxy Filter class and subclass QSortProxyFilter and start of with overriding the sortAsspectRow() method but relaized that will just filter the column and not use the search fields given, can you help me get started in the right direction for what I need? I'm not sure if I should be passing a string to compare with the filter? I need to set the filterString and use it to filter tableView (evaluate the data based on a string)
============= Main.cpp ===========
#include <QGuiApplication>
#include <QQmlApplicationEngine>
#include <QSqlDatabase>
#include <QAbstractTableModel>
#include <QAbstractItemModel>
#include "sqlitemodel.h"
#include "sortproxyfilter.h"
#include <QUrl>
int main(int argc, char *argv[])
{
QGuiApplication app(argc, argv);
sqliteModel *model = new sqliteModel;
sortProxyFilter *myProxyFilter = new sortProxyFilter;
myProxyFilter->setSourceModel(model);
QQmlApplicationEngine engine;
QQmlContext *contxt = engine.rootContext();
contxt->setContextProperty("sqliteModel", model);
contxt->setContextProperty("proxyFilter", myProxyFilter);
engine.
load(QUrl("qrc:/main.qml"));
//---Insert Proxy Model between model and view---//
//proxy = new QSortFilterProxyModel(parent);
//proxy->setSourceModel(model);
//engine.setModel(proxy);
return app.exec();
}
#include <QGuiApplication>
#include <QQmlApplicationEngine>
#include <QSqlDatabase>
#include <QAbstractTableModel>
#include <QAbstractItemModel>
#include "sqlitemodel.h"
#include "sortproxyfilter.h"
#include <QUrl>
int main(int argc, char *argv[])
{
QGuiApplication app(argc, argv);
sqliteModel *model = new sqliteModel;
sortProxyFilter *myProxyFilter = new sortProxyFilter;
myProxyFilter->setSourceModel(model);
QQmlApplicationEngine engine;
QQmlContext *contxt = engine.rootContext();
contxt->setContextProperty("sqliteModel", model);
contxt->setContextProperty("proxyFilter", myProxyFilter);
engine.load(QUrl("qrc:/main.qml"));
//---Insert Proxy Model between model and view---//
//proxy = new QSortFilterProxyModel(parent);
//proxy->setSourceModel(model);
//engine.setModel(proxy);
return app.exec();
}
To copy to clipboard, switch view to plain text mode
===========sortProxyFilter.cpp=============
#include "sortproxyfilter.h"
#include "sqlitemodel.h"
{
}
sortProxyFilter::~sortProxyFilter()
{
}
//Need to figure out what functions I will need (filterString(), setFilterString etc...)
#include "sortproxyfilter.h"
#include "sqlitemodel.h"
sortProxyFilter::sortProxyFilter(QObject *parent):QSortFilterProxyModel(parent)
{
}
sortProxyFilter::~sortProxyFilter()
{
}
//Need to figure out what functions I will need (filterString(), setFilterString etc...)
To copy to clipboard, switch view to plain text mode
============sortProxyFilter.h============
#ifndef SORTPROXYFILTER_H
#define SORTPROXYFILTER_H
#include <QSortFilterProxyModel>
{
public:
explicit sortProxyFilter
(QObject *parent
= 0);
~sortProxyFilter();
//add filter string functions
signals:
public slots:
private:
};
#endif // SORTPROXYFILTER_H
#ifndef SORTPROXYFILTER_H
#define SORTPROXYFILTER_H
#include <QSortFilterProxyModel>
class sortProxyFilter:public QSortFilterProxyModel
{
public:
explicit sortProxyFilter(QObject *parent = 0);
~sortProxyFilter();
//add filter string functions
signals:
public slots:
private:
};
#endif // SORTPROXYFILTER_H
To copy to clipboard, switch view to plain text mode
===========slqiteModel.cpp============
#include "sqlitemodel.h"
}
sqliteModel::~sqliteModel(){
}
int sqliteModel
::rowCount(const QModelIndex &parent
) const{ Q_UNUSED(parent);
return m_msgList.count();
qDebug()<< m_msgList.count();
}
QHash<int, QByteArray> sqliteModel::roleNames() const{
QHash<int, QByteArray> roleNames;
roleNames.insert(idRole, "id");
roleNames.insert(nameRole, "userName");
roleNames.insert(msgRole, "eventMessage");
roleNames.insert(dateRole, "dateTime");
qDebug()<< roleNames;
return roleNames;
}
{
if (index.row() < 0 || index.row() >= m_msgList.count()){
}
if(role == idRole){
userEventLogMsg msg = m_msgList[index.row()];
text = msg.id;
qDebug() << text;
}
else if(role == nameRole){
userEventLogMsg msg = m_msgList[index.row()];
text = msg.username;
qDebug() << text;
}
else if(role == msgRole){
userEventLogMsg msg = m_msgList[index.row()];
text = msg.eventmessage;
qDebug() << text;
}
if(role == dateRole){
userEventLogMsg msg = m_msgList[index.row()];
text = msg.datetime;
qDebug() << text;
}
return text;
}
void sqliteModel::addEvent(const userEventLogMsg &msg){
m_msgList.insert(0, msg);
endInsertRows();
}
void sqliteModel::dbConnect() {
if(!m_selectDataBase.isValid()){
qDebug() << "error in opening DB";
m_selectDataBase
= QSqlDatabase::addDatabase("QSQLITE",
"conn2");
m_selectDataBase.setDatabaseName("/home/amet/userLog.db");
}
else{
qDebug() <<"connected to DB" ;
}
m_selectDataBase.open();
}
void sqliteModel::sqlSelect() {
dbConnect();
if(!m_selectDataBase.open())
{
qDebug() << "database was closed";
m_selectDataBase.open();
}
else{
qDebug() << "database is open";
}
QSqlQuery selectQuery
("SELECT id, userName, eventMessage, dateTime FROM userlogevents", m_selectDataBase
);
if(selectQuery.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with sql statement";
qDebug() << selectQuery.lastError();
}
userEventLogMsg msg;
while (selectQuery.next()){
msg.id = selectQuery.value(0).toString();
msg.username = selectQuery.value(1).toString();
msg.eventmessage = selectQuery.value(2).toString();
msg.datetime = selectQuery.value(3).toString();
addEvent(msg);
}
m_selectDataBase.close();
}
void sqliteModel::createDailyTable()
{
dbConnect();
createTableQry.prepare("CREATE TABLE userlogevents1 AS SELECT * FROM userlogevents WHERE 0");
createTableQry.exec();
m_selectDataBase.close();
}
void sqliteModel::deleteDailyTable()
{
dbConnect();
//---Selects all tables older than 30 days in database | Gets date created---//
selectTables.prepare("SELECT usereventlog, create_date FROM sys.tables WHERE DATEDIFF(day, create_date, getdate()) > 30");
selectTables.exec();
selectTableResult = selectTables.value(0).toString();
selectTableResult.append(selectTables.value(1).toString());
selectTableResult.append(selectTables.value(2).toString());
qDebug() << selectTableResult;
//--- If the table is older than 30 days drop it---//
deleteTableQry.prepare("DROP TABLE userlogevetns");
deleteTableQry.exec();
m_selectDataBase.close();
}
void sqliteModel
::searchDateText(const QString &dateText
) {
qDebug() << "c++: sqliteModel::searchDateText:" << dateText;
dbConnect();
searchDateQry.prepare("SELECT id, userName, eventMessage, dateTime FROM usereventlog WHERE dateTime = "+ dateText);
searchDateQry.exec();
m_selectDataBase.close();
}
void sqliteModel
::searchUserNameText(const QString &userNameText
) {
qDebug() << "c++: sqliteModel::searchUserNameText:" << userNameText;
dbConnect();
searchDateQry.prepare("SELECT id, userName, eventMessage, dateTime FROM usereventlog WHERE userName = "+ userNameText);
searchDateQry.exec();
m_selectDataBase.close();
}
#include "sqlitemodel.h"
sqliteModel::sqliteModel(QObject *parent):QAbstractListModel(parent){
}
sqliteModel::~sqliteModel(){
}
int sqliteModel::rowCount(const QModelIndex &parent) const{
Q_UNUSED(parent);
return m_msgList.count();
qDebug()<< m_msgList.count();
}
QHash<int, QByteArray> sqliteModel::roleNames() const{
QHash<int, QByteArray> roleNames;
roleNames.insert(idRole, "id");
roleNames.insert(nameRole, "userName");
roleNames.insert(msgRole, "eventMessage");
roleNames.insert(dateRole, "dateTime");
qDebug()<< roleNames;
return roleNames;
}
QVariant sqliteModel::data(const QModelIndex &index, int role) const
{
if (index.row() < 0 || index.row() >= m_msgList.count()){
return QVariant();
}
QVariant text;
if(role == idRole){
userEventLogMsg msg = m_msgList[index.row()];
text = msg.id;
qDebug() << text;
}
else if(role == nameRole){
userEventLogMsg msg = m_msgList[index.row()];
text = msg.username;
qDebug() << text;
}
else if(role == msgRole){
userEventLogMsg msg = m_msgList[index.row()];
text = msg.eventmessage;
qDebug() << text;
}
if(role == dateRole){
userEventLogMsg msg = m_msgList[index.row()];
text = msg.datetime;
qDebug() << text;
}
return text;
}
void sqliteModel::addEvent(const userEventLogMsg &msg){
beginInsertRows(QModelIndex(), 0, 0);
m_msgList.insert(0, msg);
endInsertRows();
}
void sqliteModel::dbConnect() {
if(!m_selectDataBase.isValid()){
qDebug() << "error in opening DB";
m_selectDataBase = QSqlDatabase::addDatabase("QSQLITE", "conn2");
m_selectDataBase.setDatabaseName("/home/amet/userLog.db");
}
else{
qDebug() <<"connected to DB" ;
}
m_selectDataBase.open();
}
void sqliteModel::sqlSelect() {
dbConnect();
if(!m_selectDataBase.open())
{
qDebug() << "database was closed";
m_selectDataBase.open();
}
else{
qDebug() << "database is open";
}
QSqlQuery selectQuery("SELECT id, userName, eventMessage, dateTime FROM userlogevents", m_selectDataBase);
if(selectQuery.exec()){
qDebug()<<"sql statement exicuted fine";
}
else{
qDebug() << "Errors accured with sql statement";
qDebug() << selectQuery.lastError();
}
userEventLogMsg msg;
while (selectQuery.next()){
msg.id = selectQuery.value(0).toString();
msg.username = selectQuery.value(1).toString();
msg.eventmessage = selectQuery.value(2).toString();
msg.datetime = selectQuery.value(3).toString();
addEvent(msg);
}
m_selectDataBase.close();
}
void sqliteModel::createDailyTable()
{
dbConnect();
QSqlQuery createTableQry(m_selectDataBase);
createTableQry.prepare("CREATE TABLE userlogevents1 AS SELECT * FROM userlogevents WHERE 0");
createTableQry.exec();
m_selectDataBase.close();
}
void sqliteModel::deleteDailyTable()
{
dbConnect();
QSqlQuery selectTables(m_selectDataBase);
//---Selects all tables older than 30 days in database | Gets date created---//
selectTables.prepare("SELECT usereventlog, create_date FROM sys.tables WHERE DATEDIFF(day, create_date, getdate()) > 30");
selectTables.exec();
QString selectTableResult;
selectTableResult = selectTables.value(0).toString();
selectTableResult.append(selectTables.value(1).toString());
selectTableResult.append(selectTables.value(2).toString());
qDebug() << selectTableResult;
//--- If the table is older than 30 days drop it---//
QSqlQuery deleteTableQry(m_selectDataBase);
deleteTableQry.prepare("DROP TABLE userlogevetns");
deleteTableQry.exec();
m_selectDataBase.close();
}
void sqliteModel::searchDateText(const QString &dateText)
{
qDebug() << "c++: sqliteModel::searchDateText:" << dateText;
dbConnect();
QSqlQuery searchDateQry(m_selectDataBase);
searchDateQry.prepare("SELECT id, userName, eventMessage, dateTime FROM usereventlog WHERE dateTime = "+ dateText);
searchDateQry.exec();
m_selectDataBase.close();
}
void sqliteModel::searchUserNameText(const QString &userNameText)
{
qDebug() << "c++: sqliteModel::searchUserNameText:" << userNameText;
dbConnect();
QSqlQuery searchDateQry(m_selectDataBase);
searchDateQry.prepare("SELECT id, userName, eventMessage, dateTime FROM usereventlog WHERE userName = "+ userNameText);
searchDateQry.exec();
m_selectDataBase.close();
}
To copy to clipboard, switch view to plain text mode
===========sqliteModel.h===========
#ifndef SQLITEMODEL_H
#define SQLITEMODEL_H
#include <assert.h>
#include <list>
#include <QList>
#include <QColor>
#include <QObject>
#include <QDebug>
#include <QString>
#include <QDateTime>
#include <QQmlError>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlError>
#include <QtSql/QSqlRecord>
#include <QModelIndex>
#include <QAbstractListModel>
struct userEventLogMsg{
};
{
Q_OBJECT
public:
explicit sqliteModel
(QObject *parent
= 0);
~sqliteModel();
enum userEventRoles {idRole= Qt::UserRole + 220, nameRole, msgRole, dateRole};
QHash<int, QByteArray> roleNames() const;
Q_INVOKABLE void addEvent(const userEventLogMsg &msg);
Q_INVOKABLE void dbConnect();
Q_INVOKABLE void sqlSelect();
void createDailyTable();
void deleteDailyTable();
public slots:
void searchDateText
(const QString &dateIn
);
void searchUserNameText
(const QString &userNameIn
);
private:
QList<userEventLogMsg> m_msgList;
};
#endif // SQLITEMODEL_H
#ifndef SQLITEMODEL_H
#define SQLITEMODEL_H
#include <assert.h>
#include <list>
#include <QList>
#include <QColor>
#include <QObject>
#include <QDebug>
#include <QString>
#include <QDateTime>
#include <QQmlError>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlError>
#include <QtSql/QSqlRecord>
#include <QModelIndex>
#include <QAbstractListModel>
struct userEventLogMsg{
QString id;
QString username;
QString eventmessage;
QString datetime;
};
class sqliteModel:public QAbstractListModel
{
Q_OBJECT
public:
explicit sqliteModel(QObject *parent = 0);
~sqliteModel();
enum userEventRoles {idRole= Qt::UserRole + 220, nameRole, msgRole, dateRole};
int rowCount(const QModelIndex & parent) const;
QHash<int, QByteArray> roleNames() const;
QVariant data(const QModelIndex &index, int role=Qt::DisplayRole) const;
Q_INVOKABLE void addEvent(const userEventLogMsg &msg);
Q_INVOKABLE void dbConnect();
Q_INVOKABLE void sqlSelect();
void createDailyTable();
void deleteDailyTable();
public slots:
void searchDateText(const QString &dateIn);
void searchUserNameText(const QString &userNameIn);
private:
QList<userEventLogMsg> m_msgList;
QSqlDatabase m_selectDataBase;
QSqlQuery m_selectQuery;
};
#endif // SQLITEMODEL_H
To copy to clipboard, switch view to plain text mode
=========Main QML============
import QtQuick 2.5
import QtQuick.Layouts 1.1
import QtQuick.Controls 1.3
import QtQuick.Window 2.2
import QtQuick.Dialogs 1.2
import QtQuick.Layouts 1.1
import QtQuick.Controls 1.4
Window {
signal submitDateText(string text)
signal submitUserNameText(string text)
visible: true
width: 760
height: 450
title: "User Event Log"
TableView {
model: sqliteModel
width: 750;
height: 350;
anchors.centerIn: parent;
horizontalScrollBarPolicy: 0
frameVisible: true
//sortIndicatorColumn : 1
//sortIndicatorVisible: true
TableViewColumn {
role: "id"
title: "id"
width: 100
}
TableViewColumn {
role: "userName"
title: "User Name"
width: 200
}
TableViewColumn {
role: "eventMessage"
title: "Event Message"
width: 200
}
TableViewColumn {
role: "dateTime"
title: "Date Time"
width: 200
}
}
RowLayout {
id: row1
x: 201
y: 403
anchors.horizontalCenter: parent.horizontalCenter;
anchors.bottom: parent.bottom
width: 750
height: 47;
clip: false
opacity: 0.9
Button {
id: load_btn
text: qsTr("Load")
MouseArea{
anchors.fill: parent
onClicked: {
sqliteModel.sqlSelect();
}
}
}
Label {
id: userNameLabel
text: qsTr("User Name")
}
TextField {
id: userNameTextField
placeholderText: qsTr("User Name")
}
Label {
id: dateLabel
width: 39
height: 17
text: qsTr("Date")
}
TextField {
id: dateTextField
width: 125
height: 25
placeholderText: qsTr("mm//dd/yyyy")
}
Button {
id: searchBtn
text: qsTr("Search")
MouseArea{
anchors.fill: parent
onClicked: {
//---emit the submitDateText & submitUserNameText signal---//
//sqliteModel.searchDateText(dateTextField.text);
//sqliteModel.searchUserNameText(userNameTextField.text);
}
}
}
Button {
id: exit_btn
text: qsTr("Exit")
MouseArea{
anchors.fill: parent
onClicked: close();
}
}
}
}
import QtQuick 2.5
import QtQuick.Layouts 1.1
import QtQuick.Controls 1.3
import QtQuick.Window 2.2
import QtQuick.Dialogs 1.2
import QtQuick.Layouts 1.1
import QtQuick.Controls 1.4
Window {
signal submitDateText(string text)
signal submitUserNameText(string text)
visible: true
width: 760
height: 450
title: "User Event Log"
TableView {
model: sqliteModel
width: 750;
height: 350;
anchors.centerIn: parent;
horizontalScrollBarPolicy: 0
frameVisible: true
//sortIndicatorColumn : 1
//sortIndicatorVisible: true
TableViewColumn {
role: "id"
title: "id"
width: 100
}
TableViewColumn {
role: "userName"
title: "User Name"
width: 200
}
TableViewColumn {
role: "eventMessage"
title: "Event Message"
width: 200
}
TableViewColumn {
role: "dateTime"
title: "Date Time"
width: 200
}
}
RowLayout {
id: row1
x: 201
y: 403
anchors.horizontalCenter: parent.horizontalCenter;
anchors.bottom: parent.bottom
width: 750
height: 47;
clip: false
opacity: 0.9
Button {
id: load_btn
text: qsTr("Load")
MouseArea{
anchors.fill: parent
onClicked: {
sqliteModel.sqlSelect();
}
}
}
Label {
id: userNameLabel
text: qsTr("User Name")
}
TextField {
id: userNameTextField
placeholderText: qsTr("User Name")
}
Label {
id: dateLabel
width: 39
height: 17
text: qsTr("Date")
}
TextField {
id: dateTextField
width: 125
height: 25
placeholderText: qsTr("mm//dd/yyyy")
}
Button {
id: searchBtn
text: qsTr("Search")
MouseArea{
anchors.fill: parent
onClicked: {
//---emit the submitDateText & submitUserNameText signal---//
//sqliteModel.searchDateText(dateTextField.text);
//sqliteModel.searchUserNameText(userNameTextField.text);
}
}
}
Button {
id: exit_btn
text: qsTr("Exit")
MouseArea{
anchors.fill: parent
onClicked: close();
}
}
}
}
To copy to clipboard, switch view to plain text mode
using an isntance of QSqlDatabase for connection defiinition
Reply With Quote
Originally Posted by Lesiok
Bookmarks