Grouping Options in DBvolution

Grouping your results is very important functionality for database queries and it is supported in several ways in DBvolution.

Firstly, it's supported by trivial Java so even if DBvolution's built-in support isn't sufficient you can still loop over the data and get the results you need. That's expensive for your middleware tier or UI though so DBV has better ways to do it: distinct methods, and DBReport classes.

The simplest way to group your values is to use the getDistinctValuesOfColumn() method of your DBRow instance.

This method takes a DBDatabase and a field of the DBRow object and returns the distinct values of that field. Under the hood DBV creates a query for you that only returns that field/column and only the unique values of the column. It will return a list of objects representing the field you supplied, outside of a DBRow for clarity.

A common use case for getDistinctValuesOfColumn() is to populate a drop-down list with all the defined options.

The same functionality is also available from DBTable using a similar getDistinctValuesOfColumn() method with only a field of the tables exemplar object as a parameter.

A more complicated grouping example requires a more complex paradigm: grouping several columns within a DBQuery.

DBQuery offers the getDistinctCombinationsOfColumnValues() method, which takes a list of fields, and returns all the known combinations of those fields as a list of DBQueryRows. This is the most powerful method of retrieving grouped data and you should be aware of a peculiarity when using it: the DBQueryRows will have all the usual fields but only the fields specified will be set. So avoid using the other fields within the DBQueryRows as they will have some undefined value, probably null, that isn't representative. However it is the best method of producing the grouped columns that are sometimes vital.

The last method is the most complex and arguably my least favourite but it does have it's own advantages: DBReport subclasses.

DBReport is a brother to DBRow in that it defines a row that will be retrieved from the database. However DBReport selects from multiple tables and uses expressions to generate values from the columns of the tables. Creating a DBReport is a topic for another post but they automatically group results. So if you have a query that needs grouping and you prefer DBRow-like defined fields, consider using a DBReport.

So there are 5 main ways to produced grouped results in DBvolution: Java's looping; DBRow's getDistinctValuesOfColumn method; the similar getDistinctValuesOfColumn method of DBTable; the slightly more complicated getDistinctCombinationsOfColumnValues method of DBQuery; and DBReports.

I recommend you use the method is right for you and the particular task you are performing, though you might want to start with my first suggestion and work your way down the list :)