Ordering and Grouping
In the previous sections, we covered the basics of selecting data from single and multiple tables. In this section and the ones that follow, we look briefly at ways to use class
RWDBSelector to construct more complex queries, starting with ordering and grouping.
In SQL, ordering and grouping are specified with the
ORDER BY and
GROUP BY clauses, respectively. The DB Interface Module models these with the
orderBy() and
groupBy() methods of
RWDBSelector. There are variants to support ordering or grouping by column name, column number, or an expression. The following fragment assumes we have the
RWDBSelector instance declared in
Joins.
select.orderBy(videos["title"]); //1
select.orderBy(1); //2
Line //1 says that the result should be sorted according to the videos.title column. Line //2 says to order by the first item in the select list, which is purchases.orderNum. C++ indexing starts at 0 but SQL indexing starts at 1.
Calls to orderBy() and groupBy() cause items to accumulate within a selector. If we included //1 and //2 in our program, we would order by both the videos.title and column number 1.
NOTE: To replace a selector's order by items, first use the orderByClear() method, then call orderBy() once for each new item. The same is true of groupBy() and groupByClear().
Some database implementations place restrictions on ordering and grouping. In particular, ordering or grouping by a complex expression, rather than a simple column reference, may not be supported. It may also be impossible to order or group by an item that doesn’t appear in the select list. These restrictions are typically not detectable until runtime. In these cases, the DB Interface Module catches any errors reported by the database and passes them back via your application's error handler. The application may need to be changed to allow its use with the given database. See Tutorial 6 in
Chapter 22, A Complicated Query, for a good example of this.
