Guide to Database Constraints

Table of Contents

• Constraints
• Limits
• Order By

Constraints

The constraints array is used to build the WHERE clause of a query, but in an array format that may be easier to build programmatically and understand (if formatted properly). Many functions that use DBI2 and SModel2 make use of the constraints array, such as getList(), DBI2::select(), etc. Thus, it is useful to understand how it works. Most of these functions have links to this documentation page.

The simplest constraints array is simply an empty array, which, of course, specifies no constraints at all. You can also have an array that is a list of key value pairs, where the keys are table attributes and the values specify what those attributes should be set to in the database. For example:

array('age' => 22, 'sex' => 'male')

Specifies that all results should have '22' in the age column and that they should be male (i.e., have 'male' in the sex column). It generates a WHERE clause that looks like this:

WHERE age = 22 AND sex = 'male'

Now, what happens if you want to have an attribute take on multiple values? Say you want to include both males and females (which is probably redundant if you only allow two sexes). You can't do this:

array('age' => 22, 'sex' => 'male', 'sex' => 'female')   # wrong!!!

You must instead do this:

array('age' => 22, 'sex' => array('male', 'female'))     # right!!!

The reason is that array keys must be unique, so you can't have two entries in the array called 'sex'. If you do, only the last one counts. As such, DBI2 and SModel2 support this notation that lets you indicate multiple values for fields. The WHERE clause for this constraint array looks like this:

WHERE age = 22 AND (sex = 'male' OR sex = 'female')

What if you want to use an operator other than '='? Suppose that we want all people who are older than 22, in which case we need to use '>'. You can do that using any of the following notations:

array('>', 'age' => 22, '=', 'sex' => array('male', 'female'))      # a bit ugly

array('>' => array('age' => 22), 'sex' => array('male', 'female'))  # cleaner

array('age' => array('>', 22), 'sex' => array('male', 'female'))    # kind of weird...

The first notation is ugly because setting the operator by just plopping it in the middle of the array like that doesn't make it clear to which field it should apply (any fields following it in the array) and then you have to reset the operator back to '=' before specifying the values for the sex field. The third notation uses the same mechanism as the first, but puts the operator specifier inside the subarray, which is a little weird, but a bit more obvious.

You can use this same system for specifying conjuncations ('and' and 'or'). And you can nest these subarrays, make it possible to build up complex constraints arrays:

array('or' => array('age' => 22, 'sex' => 'male'), 'country' => 'us')
# WHERE (age = 22 OR sex = 'male') AND country = 'us'

array('or' => array('<' => array('age' => 22), 'sex' => 'male'))
# WHERE age < 22 OR sex = 'male'

array('and' => array(
            'sex' => 'female',
            'or' => array(
                '<', 'age' => 22,
                '>', 'age' => 24)),
      'or' => array(
              'country' => 'us',
              'country' => 'gb'))
# WHERE (sex = 'female' AND (age < 22 OR age > 24)) AND (country = 'us' OR country = 'gb')

Notice that the just like the default operator is '=', the default conjuncation is 'and'. Also it should be noted that capitalization for operators and conjunctions does not matter.

Some operators ('IS NULL' and 'IS NOT NULL') do not take any arguments in the SQL. However, due to deficiencies in the code that converts the arrays to SQL, they must take arguments nonetheless. Thus, you must do things like this:

array('is null' => array('age' => 0))

Which does not mean that age = 0, but rather that age is null. The 0 is ignored.

The operators 'IN' and 'NOT IN' work in a similar way, except that their arguments are used. If you provide a single value as the argument to 'IN', then you will get 'IN(3)', for example. You probably want to provide an array, e.g.:

array('foo' => array('IN' => array(2, 5, 7)))
// WHERE foo IN(2, 5, 7)

Limits

Order By