January 14, 2011

ivan.todorovich ivan.todorov..
Lab Rat
80 posts

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

 

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:

  1. SELECT c.id, c.name, co.name AS country, st.name AS state, ci.name AS city, ci.postal_code AS postal_code,
  2.            c.address, c.phone, c.fax, c.email, c.cuit, u1.username AS created_by, FROM_UNIXTIME(c.created_time) AS created_time,
  3.            u2.username AS modified_by, FROM_UNIXTIME(c.modified_time) AS modified_time
  4. FROM customer AS c
  5. LEFT JOIN geo_city AS ci ON (c.city_id = ci.id)
  6. LEFT JOIN geo_state AS st ON (ci.state_id = st.id)
  7. LEFT JOIN geo_country AS co ON (st.country_id = co.id)
  8. LEFT JOIN user AS u1 ON (c.created_by = u1.id)
  9. LEFT JOIN user AS u2 ON (c.modified_by = u2.id)

 Signature 

o_o Sorry for my rusted english.

9 replies

January 14, 2011

Volker Volker
Ant Farmer
5331 posts

Set the SQL with QSqlQueryModel::setQuery(QString) [doc.qt.nokia.com] on the model.

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

January 14, 2011

ivan.todorovich ivan.todorov..
Lab Rat
80 posts

Volker wrote:
Set the SQL with QSqlQueryModel::setQuery(QString) [doc.qt.nokia.com] on the model.

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


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 :)

 Signature 

o_o Sorry for my rusted english.

January 14, 2011

Volker Volker
Ant Farmer
5331 posts

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.

January 14, 2011

ivan.todorovich ivan.todorov..
Lab Rat
80 posts

Volker wrote:
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.

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.

 Signature 

o_o Sorry for my rusted english.

January 15, 2011

Volker Volker
Ant Farmer
5331 posts
ivan.todorovich wrote:
I’ll make a “QSortFilterSqlQueryModel” hehe. I’ll post here later in case anyone needs it too.

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

January 15, 2011

ivan.todorovich ivan.todorov..
Lab Rat
80 posts
Volker wrote:
ivan.todorovich wrote:
I’ll make a “QSortFilterSqlQueryModel” hehe. I’ll post here later in case anyone needs it too.

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


Ok, Volker ^^
I’ve created a Code Snippets’ page. You can check it out here: Sort and Filter a QSqlQueryModel QSortFilterSqlQueryModel [developer.qt.nokia.com]

 Signature 

o_o Sorry for my rusted english.

January 16, 2011

Volker Volker
Ant Farmer
5331 posts

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

January 16, 2011

ivan.todorovich ivan.todorov..
Lab Rat
80 posts

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

 Signature 

o_o Sorry for my rusted english.

February 6, 2012

parcpaes parcpaes
Lab Rat
4 posts

What file not found is Empty

 
  ‹‹ [Moved] [Suggestion] Positive and negative lookbehind      QWebView with static compiled Qt ››

You must log in to post a reply. Not a member yet? Register here!