CODEWITHSUNDEEP
c++qtsqlite
cmannett85
cmannett85

Reputation: 22346

QSqlQuery ignoring sort order for SQLite DB

The results returned by my QSqlQuery are always in the same order, regardless of the ORDER BY state:

void Sy_loggingModel::reload()
{
    auto query = d_->buildQuery();
    query.setForwardOnly( true );
    if ( !query.exec() ) {
        throw Sy_exception( QObject::tr( "Failed to query logging data: " ) +
                            query.lastError().text() );
    }

    beginResetModel();

    qDebug() << query.lastQuery()
             << d_->filter_        // First ? param
             << d_->sortedColumn_; // Second ? param

    d_->entries_.clear();
    while ( query.next() ) {
        auto timestamp = query.value( 1 ).toLongLong();
        auto level = query.value( 2 ).toInt();

        d_->entries_ << Sy_loggingModel_d::Entry{
                            query.value( 0 ).toLongLong(),
                            QDateTime::fromMSecsSinceEpoch( timestamp ).toString(),
                            static_cast< Sy_loggerInterface::DebugLevel >( level ),
                            query.value( 3 ).toString() };

        qDebug() << "\t" << query.value( 0 ).toLongLong()
                         << timestamp
                         << level
                         << query.value( 3 ).toString();
    }

    endResetModel();
}

Produces this output when alternating between sort orders:

"SELECT rowid, timestamp, debugLevel, message FROM Sy_logger WHERE rowid >= ? AND debugLevel IN ( 0, 1, 2 ) ORDER BY ? DESC;" 0 1
     1 1415399097350 0 "Opened database ./logs/Syren2.log"
     2 1415399097382 1 "Listening on port 23000"
     3 1415399418377 2 "New log rotation settings received, Metric: 0, Interval: 720"
     4 1416178611851 2 "Opened database ./logs/Syren2.log"
     5 1416178611852 2 "Listening on port 23000"
     6 1416178612776 2 "New log rotation settings received, Metric: 0, Interval: 720"


"SELECT rowid, timestamp, debugLevel, message FROM Sy_logger WHERE rowid >= ? AND debugLevel IN ( 0, 1, 2 ) ORDER BY ? ASC;" 0 1
     1 1415399097350 0 "Opened database ./logs/Syren2.log"
     2 1415399097382 1 "Listening on port 23000"
     3 1415399418377 2 "New log rotation settings received, Metric: 0, Interval: 720"
     4 1416178611851 2 "Opened database ./logs/Syren2.log"
     5 1416178611852 2 "Listening on port 23000"
     6 1416178612776 2 "New log rotation settings received, Metric: 0, Interval: 720"

The SQL statement returns the expected result set when used from the command line. Any suggestions? I'm using Qt v5.3.2.

Upvotes: 0

Views: 415

Answers (1)

CL.
CL.

Reputation: 180080

The documentation says:

If the ORDER BY expression is a constant integer K then the expression is considered an alias for the K-th column of the result set.

However, parameters are not considered constants, so the value you use for this parameters is used as an expression that happens to be the same for all rows.

If you want to sort by different columns, you have to construct the SQL statement dynamically.

Upvotes: 2

Related Questions