QSqlQueryModel GROUP BY

ZNohre

Does QSqlQueryModel support the GROUP BY statement?

The below code is returning the properly grouped records, but the sum field is incorrectly returning 0 despite other information in the database.

    QSqlQueryModel m;
    m.setQuery("SELECT ClassCode, SUM(EstimatedChargeablePayroll) AS Total FROM Payroll GROUP BY ClassCode")

Screenshot of the connected view:

Screenshot of SQL Server Management Studies with the same query:

JonB

@ZNohre
QSqlQueryModel does not understand, use or look at the SQL statement. That is interpreted at the database and the result set sent back. I don't know why you are seeing what you show.

ZNohre

@JonB Thanks, this rules out the issues regarding preparation/interpretation of the query so I'm now concerned it's an issue related to the results being sent back/presentation of the data by QSqlQueryModel. I ran a debug on the executed record and I'm getting the below.

QSqlRecord(2)
 0: QSqlField("ClassCode", QString, tableName: "Payroll", length: 255, required: no, generated: yes, autoValue: false, readOnly: false) "" 
 1: QSqlField("Total", double, tableName: "(not specified)", length: 38, precision: 2, required: no, generated: yes, autoValue: false, readOnly: false) "0"

Nothing stands out to me here, especially since the simplified query without GROUP BY is properly displaying the results to the view, and the record information looks identical

setQuery("SELECT SUM(EstimatedChargeablePayroll) AS Total FROM Payroll");

Results in the correct data being shown based on my test data:

qDebug output:

QSqlRecord(1)
 0: QSqlField("Total", double, tableName: "(not specified)", length: 38, precision: 2, required: no, generated: yes, autoValue: false, readOnly: false) "0"

If anyone has confirmation that the GROUP BY statement is working in their QSqlQueryModel it would be appreciated.

I'll keep digging.

Christian Ehrlicher

I don't see why it should not work. Please provide a minimal, compilable example of the problem - an in-memory sqlite db + some code, think it should not take more than 50loc to create a reproducer here.

JonB

@ZNohre
As I said, like @Christian-Ehrlicher I would have expected what you have to work. I just don't see what relevance the GROUP BY should have.

I would quickly test the following:

SELECT ClassCode, 350000.00 AS Total FROM Payroll GROUP BY ClassCode

I would also test your

SELECT ClassCode, SUM(EstimatedChargeablePayroll) AS Total FROM Payroll GROUP BY ClassCode

as a QSqlQuery without the Model. And examine results from all of these directly from model, eliminating the view.

ZNohre

@JonB Solved!! Thank you so much for the help here troubleshooting which made the issue obvious after trying your suggested tests.

As you expected, the QSqlQuery is not the issue as the proper data is being returned;

Test #2

    QSqlQuery q;
    qDebug() << "Prep: " << q.prepare("SELECT ClassCode, SUM(EstimatedChargeablePayroll) AS Total FROM Payroll GROUP BY ClassCode");
    qDebug() << "Exec: " << q.exec();

    while(q.next()){
        QVariant code = q.value(0);
        QVariant total =  q.value(1);

        qDebug() << code << total;
    }

Results in the correct records being returned.

Prep:  true
Exec:  true
QVariant(QString, "5555") QVariant(double, 350000)
QVariant(QString, "6666") QVariant(double, 50000)

This completely ruled out the QSqlQueryModel issue and forced me to review the rest of the code. The class I've been working on is derived from QSqlQueryModel and has an overridden data() method that was not properly presenting the data to the view. The model was always working in the background it just didn't look like it.

@Christian-Ehrlicher, I was spooling up the example when this dawned on me, but appreciate the assist here.