How can I modify the default SELECT statement used by QSqlRelationalTableModel

[Jonathan]

SQLite doesn't have a date type, but has lots of convenience methods for converting strings and integers to dates, times, etc.

I can get the SELECT statement used by QSqlRelationalTableModel, (using QSqlRelationalTableModel::selectStatement()) but I can't see a way to set it.

For instance I want to change something like:
SELECT employee."id", employee."startDate" FROM employee ORDER BY employee."id" ASC

to
SELECT employee."id", date(employee."startDate") FROM employee ORDER BY employee."id" ASC

I could create my own SQL model, but then it would be a lot of work to provide the necessary editing and relational support.

[goetz]

"QSqlQueryModel":http://doc.qt.nokia.com/stable/qsqlquerymodel.html provides "setQuery() methods":http://doc.qt.nokia.com/stable/qsqlquerymodel.html#setQuery

QSqlTableModel and QSqlRelationalTableModel both inherit this class, so the methods are available on this level too.

[Jonathan]

I've thought about setQuery(), however:

(1) The documentation states "You should normally not call it on a QSqlTableModel. Instead, use setTable(), setSort(), setFilter(), etc., to set up the query"

(2) There are two setQuery() methods. I had assumed that both execute immediately. Does

@void QSqlQueryModel::setQuery ( const QSqlQuery & query )@

defer execution until select() is called? How would I create the QSqlQuery argument?

[fcrochik]

Just create an instance of QSqlQuery and pass the "SQL" statement in the constructor.

The idea behind QSqlRelationalTableModel and QSqlTableModel is to not have to mess with the SQL side of things. If you only want a readonly model you probably should use "QSqlQueryModel":http://doc.qt.nokia.com/latest/qsqlquerymodel.html

Depending on how you change the select statement, I assume, you may get errors when trying to add/update records.

[Jonathan]

Hello fcrochik, I'm not sure what you are proposing. If I create an instance of QSqlQuery and pass the “SQL” statement in the constructor, the statement is executed immediately (see the documentation).

I'm using QSqlRelationalTableModel because I want an updateable view (and foreign key support), not just a read-only view.

Rewording my original problem, I'm trying to "intercept" the SELECT statement used by QSqlRelationalTableModel so that I can modify it slightly.

[Jonathan]

I have the answer. Inevitably I was being a bit slow.

I'm already subclassing QSqlRelationalTableModel. All I had to do was override selectStatement() and return a modified version of the select statement returned by the parent class.

Here's my test code:

@QString SqlCustomModel::selectStatement() const
{
QString query = QSqlRelationalTableModel::selectStatement();
query = query.replace(QString("""), QString()); // makes the next step easier
query = query.replace(QString("employee.startDate"), QString("date(employee.startDate)"));
return query;
}@

[fcrochik]

[quote author="Jonathan" date="1293565515"]Hello fcrochik, I'm not sure what you are proposing. If I create an instance of QSqlQuery and pass the “SQL” statement in the constructor, the statement is executed immediately (see the documentation).

I'm using QSqlRelationalTableModel because I want an updateable view (and foreign key support), not just a read-only view.

Rewording my original problem, I'm trying to "intercept" the SELECT statement used by QSqlRelationalTableModel so that I can modify it slightly.[/quote]

It is not a problem that the query is executed as long as it is not "forward only". See "documentation":http://doc.qt.nokia.com/latest/qsqlquerymodel.html#setQuery

[fcrochik]

Jonathan:
A better question is why don't you use the "other setQuery overload":http://doc.qt.nokia.com/latest/qsqlquerymodel.html#setQuery-2 that will take a SQL argument?