Simplifying SQL SELECT

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

All these things require some sort of ORM, since most databases do not provide these functionality by default.

As for the select simplification, Doctrine seems to deliver a lot of that:

http://www.doctrine-project.org/docs/orm/2.0/en/reference/query-builder.html