[Solved] QSqlQueryModel supporting server-side sorting and filtering.

? Offline

Hi.

You all know QSqlQueryModel doesn't support neither sorting nor filtering.
I need this functionality.

I've been doing sorting and filtering with a QSortFilterProxyModel attached to my QSqlQueryModel, but that's highly unefficient since it does the sorting locally, and has to fetch the full query data to do it. When you have Query results with more than 20.000 rows, from a remote database (connection lag counts), it will freeze the GUI and take a lot of time to process. So this option is no longer available to me.

It seems logical to have a QSqlQueryModel subclass that allows sorting and filtering remotely by using query language ("ORDER BY", "WHERE" clauses), and taking advantage of the QSqlQueryModel's lazy loading (fetchMore).

It seems almost too logical, so I wonder if anyone has already done something like this.. ¿?

Note: Can't use QSqlTableModel nor QSqlRelationalTableModel because of the query complexity. Here is an example:
@
SELECT c.id, c.name, co.name AS country, st.name AS state, ci.name AS city, ci.postal_code AS postal_code,
c.address, c.phone, c.fax, c.email, c.cuit, u1.username AS created_by, FROM_UNIXTIME(c.created_time) AS created_time,
u2.username AS modified_by, FROM_UNIXTIME(c.modified_time) AS modified_time
FROM customer AS c
LEFT JOIN geo_city AS ci ON (c.city_id = ci.id)
LEFT JOIN geo_state AS st ON (ci.state_id = st.id)
LEFT JOIN geo_country AS co ON (st.country_id = co.id)
LEFT JOIN user AS u1 ON (c.created_by = u1.id)
LEFT JOIN user AS u2 ON (c.modified_by = u2.id)
@

? Offline

Set the SQL with "QSqlQueryModel::setQuery(QString) ":http://doc.qt.nokia.com/stable/qsqlquerymodel.html#setQuery-2 on the model.

Or did you try that already? If so, what's wrong with it?

? Offline

[quote author="Volker" date="1295027869"]Set the SQL with "QSqlQueryModel::setQuery(QString) ":http://doc.qt.nokia.com/stable/qsqlquerymodel.html#setQuery-2 on the model.

Or did you try that already? If so, what's wrong with it?[/quote]
Hi, Volker! Seems that you should get a customer care job in Qt :)

I think I didn't express myself very well.
I need a QSqlQueryModel subclass that reimplements sort , so I can use it in Qt views. And the sorting background should be done by indeed using setQuery(oldQuery + aNewOrderByClause);

Also a filter function that allows to filter by a column and string using a whereClause in the setQuery method.

I know how to do it roughly. But maybe there's already a 3rd Party QSqlQueryModel subclass with this functionality that I'm not aware of. And maybe someone in this forum knows about it :)

? Offline

Not that I'm aware of.

I know for sure that "ORDER BY 2" sorts by the second column in the query at least on Oracle and MySQL. If that's possible with your database too, it should not be too hard, to implement a sort method on a subclass. Be aware that the column index in SQL starts with 1 whereas the columns in the model start with 0!

Another option could be to define a view using the query and use the view as input for a QSqlTableModel (not tested, but should work). Though you will not be able to modify the data of a view via QSqlTableModel.

? Offline

[quote author="Volker" date="1295029378"]Not that I'm aware of.

I know for sure that "ORDER BY 2" sorts by the second column in the query at least on Oracle and MySQL. If that's possible with your database too, it should not be too hard, to implement a sort method on a subclass. Be aware that the column index in SQL starts with 1 whereas the columns in the model start with 0!

Another option could be to define a view using the query and use the view as input for a QSqlTableModel (not tested, but should work). Though you will not be able to modify the data of a view via QSqlTableModel.[/quote]

Yeap, I'm going for the first option since creating a view for every query is not an option.
Thanks, Volker.

I'll make a "QSortFilterSqlQueryModel" hehe. I'll post here later in case anyone needs it too.

? Offline

[quote author="ivan.todorovich" date="1295029814"]
I'll make a "QSortFilterSqlQueryModel" hehe. I'll post here later in case anyone needs it too.[/quote]

This would surely make some other users happy. Best would be to put it on a page in the "Code Snippets":http://developer.qt.nokia.com/wiki/Category:snippets and/or "HowTo":http://developer.qt.nokia.com/wiki/Category:HowTo section of the wiki.

? Offline

[quote author="Volker" date="1295094927"][quote author="ivan.todorovich" date="1295029814"]
I'll make a "QSortFilterSqlQueryModel" hehe. I'll post here later in case anyone needs it too.[/quote]

This would surely make some other users happy. Best would be to put it on a page in the "Code Snippets":http://developer.qt.nokia.com/wiki/Category:snippets and/or "HowTo":http://developer.qt.nokia.com/wiki/Category:HowTo section of the wiki.[/quote]
Ok, Volker ^^
I've created a Code Snippets' page. You can check it out here: "Sort and Filter a QSqlQueryModel QSortFilterSqlQueryModel":http://developer.qt.nokia.com/wiki/Sort_and_Filter_a_QSqlQueryModel_QSortFilterSqlQueryModel

? Offline

I would only suggest to rename the class, as the class prefix "Q" is kind-of reserved for the classes within the Qt libs.

? Offline

Ups.. bleh, it's just a code snippet. The name doesn't really matter.

? Offline

What file not found is Empty