Wt::Dbo meets Wt Views: QueryModel

  • Posted by koen
  • Monday, May 10, 2010 @ 21:10

Although Wt::Dbo (Wt’s ORM library) comes packaged together with Wt, so far there was not a real integration between the two, other than that Wt::Dbo provides support for types provided by Wt that are missing in the C++ standard library (WString, WDate and WDateTime).

We have now added (in git) a first integration between the View classes in Wt and data Models built around a database: QueryModel<Result> is a tabular data model which can be used to display data from a database Query<Result>.

This can be used to display data from a database using a View widget such as Wt’s new WTableView or a WCartesianChart.

The model is configured with a query. For example, the model below will display all information related to a Post (from this blog example):

namespace dbo = Wt::Dbo;

dbo::QueryModel< dbo::ptr<Post> > *model = new dbo::QueryModel< dbo::ptr<Post> >();
model->setQuery(session.find<Post>());
model->addAllFieldsAsColumns();

You can then create a view that simply displays this model:

WTableView *view = new WTableView();
view->resize(800, 300);
view->setModel(model);

You may also selectively add particular columns instead of showing every field returned by the query.

The example below will display selected information on published posts, including a count of the number of comments. It shows how you can use an ad hoc data structure for your combined results using boost::tuple, and other improvements we made to the query API:

typedef boost::tuple<dbo::ptr<Post>, int> Item;

dbo::QueryModel<Item> *model = new dbo::QueryModel<Item>();
model->setQuery(session.query<Item>
                ("select Post, count(Comment.id) "
                 "from post Post join comment Comment on Comment.post_id = Post.id")
                .where("Post.state = ?").bind(Post::Published)
                .groupBy("Post"));
model->addColumn("Post.date");
model->addColumn("Post.title");
model->addColumn("count(Comment.id)");

WTableView *view = new WTableView();
view->resize(600, 300);
view->setSelectionMode(SingleSelection);
view->setModel(model);

When instantiating this view inside the application root, this gives:

WTableView displaying query a model

The model supports sorting out of the box, and implements this by sorting the underlying Sql query (the screenshot above was sorted on comment count).

The model fetches data from the database in small batches (using Query::limit() and Query::offset()) which it caches to minimize the load to the database. The default batch size is 40 rows (but you can change this). Combined with Wt’s views which implement virtual scrolling (the current implementation of WTableView provides virtual scrolling not only vertically but also horizontally!), this results in a highly efficient view on your database data.

The model does not just provide a tabular view on the data: you can also access the actual result objects, which are typically database objects or tuples. In this way, you can customize how data must be visualized, and provide editing using the standard ORM features of Wt::Dbo.

We are currently stabilizing the source tree in preparation of a new release (3.1.3), which provides quite a few nice additions, to be expected in the next week(s).

Tags:
7 comments
  • Posted by anonymous
  • 13 years ago
What is your suggested method of using a WSuggestionPopup with a dbo backend?
Is there a way to tie a querymodel to the popup directly, with filtering causing new dbo find(), or should we use the serverside filtering example and add query results to a StandardItemModel container?
  • Posted by anonymous
  • 14 years ago
Where Wt::WHTML5Video?
Class not exist in 3.1.2
  • Posted by koen
  • 14 years ago
It will be part of 3.1.3, to be released one of these next few days; the online documentation has already been updated (as well as the homepage and examples)
  • Posted by anonymous
  • 14 years ago
koen tnx.
  • Posted by anonymous
  • 14 years ago
Prety cool. i waitin
  • Posted by anonymous
  • 14 years ago
is there a similar way to work with JWt?

Raph
  • Posted by koen
  • 14 years ago
In fact, QueryModel was in great part based on a HibernateModel which we have implemented in the past for JWt as part of a customer project. We plan to provide a JPAModel as part of JWt, based on JPA (the standardized variant of Hibernate), which will look a bit different because of the differences between Wt::Dbo and JPA.

Contact us for more information
or a personalised quotation

Contact Us