In my opinion, any SQL SELECT query, or for that matter any select query, can be described by four properties:
SELECTED OBJECTS
A table, some joined tables or a query. The source always represents one business object, which is optionally extended with one or more other business objects, through a relation. In SQL this goes in the FROM clause, and has effect on the GROUP BY clause.
SELECTED PROPERTIES
A list of tablefields, values, functions and derived things. The property always represents a property of a business object that is listed in SELECTED OBJECTS. In SQL this goes in the SELECT clause.
CONDITIONS
An evalution that determines wether a record is selected or not. Multiple evaluations can be combined through boolean operators. An evalulation always represents a property or a relation of a business object that is listed in SELECTED OBJECTS. In SQL this goes in the WHERE or HAVING clause.
ORDERING
A list of tablefields, values, functions and derived things. The property always represents a property of a business object that is listed in SELECTED OBJECTS, optionally accompanied with a property ‘invert’, which reverses the sort order. In SQL this goes in the ORDER BY clause.
The SELECTED PROPERTIES, CONDITIONS and ORDERING are all dependent on SELECTED OBJECTS. With a map from our business model to the database we can generate the SELECT query from a business representation using only these four properties.
On the business side, the structure looks something like this:
object -> property*
object -> relation*
property -> datatype
relation -> object
On the database side, it looks like this:
table -> field*
table -> foreignkey*
field -> datatype
foreignkey -> table
As you can see, the two models can be mapped very easily. So what are the benefits of using a map instead of directly writing the query?
You can have:
- Nice field names
- Customization of the presentation
- Formatting
- Multilingual support
- Input validation
- Form generation