Play Framework: How to render a table structure from plain SQL table

Question

I would be happy to get a good way to get the "table" structure from a plain SQL table.

In my specific case, I need to render JSON structure used by Google Visualization API "datatable" object: http://code.google.com/apis/chart/interactive/docs/reference.html#DataTable However, having an example in HTML would help either.

My "source" is a plain SQL table of "DailySales": its columns are "Day" (date), "Product" and "DailySaleTotal" (daily sale for that product). Please recall that my "model" reflects the 3-column table above.

The table columns should be "products" (suppose we have very small number of such). Each row should represent a specific date, and the row data are the actual sales for that day.

    Date             Product1   Product2   Product3
    01/01/2012       30         50         60
    01/02/2012       35         3          15

I was trying to use nested #{list} tags in a template, but unfortunately I failed to find a natural way to provide a template with a "list" to represent the "row data".

Of course, I can build a "helper object" in Java that will build a list of the "sales data" items per date - but this looks very weird to me.

I would be thankful to anyone who can provide an elegant solution.

Max

Answer 1

When you load your model order it by date and product name. Then in your controller build a map with date as index and list of model objects that have the same date as value of the map

Then in your template you have a first list iteration on map keys for the rows and a second list iteration on the list value for the columns.

Something like

[
#{list modelMap.keys, as: 'date'}
[${date},#{list modelMap.get(date), as: 'product'}${product.dailySaleTotal}#{ifnot product_isLast},#{/ifnot}#{/list}]#{ifnot date_isLast},#{/ifnot}
#{/list}
]

you can then adapt your json rendering to the exact structure you want to have. Here it is an array of arrays.

Answer 2

Instead of generating the JSON yourself, like Seb suggested, you can generate it:

private static Result queryToJsonResult(String sql) {
  SqlQuery sqlQuery = Ebean.createSqlQuery(sql);
  return ok(Json.toJson(sqlQuery.findList()));
}