Where Conditions

When displaying data from a database to an end user you may require the ability to filter the data that the end user sees (for example based on account permissions or some other account access permissions). In SQL you would do this with a WHERE condition - the Editor .NET libraries also provide a Editor.Where() method to expose this functionality and allow complex conditional expressions.

It is important to note that the conditions applied by Editor.Where() are used only on data fetch. When writing data (create and edit actions) you should use the Field.Set() and Field.SetValue() methods. These are discussed in detail in the Setting field values section below.

Additionally, when writing data to the database you should be careful that the data you write will be selected by the where condition applied. If the data written does not match the applied condition it will not be shown in the table (which can result in potentially confusing behaviour if a user edits a row and it disappears because it cannot be read!).

Simple usage

In its simplest form the Editor.Where() method takes three arguments:

1. columnName - The column name to apply the condition to
2. value - The value to check the condition against - this is automatically escaped
3. operator - The conditional operator (e.g. =, <, >, etc). This parameter is optional - the default is =.

A simple example is:

editor.Where( "user_id", 12 );

i.e. select only rows in the table where user_id = 12.

Using all three options we could use the following to select all rows where age is greater than 18:

editor.Where( "age", 18, ">" );

Multiple conditions

Multiple conditions can be applied to the table simply by calling Editor.Where() multiple times (once for each condition). For example, let's combine the two examples above:

editor
    .Where( "user_id", 12 )
    .Where( "age", 18, ">" );

Only rows matching user_id = 12 AND age > 18 will be selected. Note that in the simple use case, multiple Editor.Where() statements will use an AND logical operator.

Null values

Searching for null values (or the inverse, searching for not null) can often be useful when performing a conditional operation with a database. The Editor libraries will accept a native .NET null as a null value. For example, the following is the equivalent of an SQL age IS NULL condition:

editor.Where( "age", null );

The IS NOT NULL expression can be used through the third parameter of the Editor,Where() method - specifically as !=:

editor.Where( "age", null, "!=" );

Complex usage

The simple use case can be useful, but the real power of the Editor.Where() method is uncovered when you use it with a delegate (this documentation will use lambda expressions, but any suitable delegate could be used):

editor.Where( q => {
    ...
} );

A single parameter is passed into the delegate; the Query instance that the Editor will use for the database query it will make. This means you have access to all of the methods of the Query class as such Query.Where(), Query.AndWhere() and Query.OrWhere() (refer to the .NET API documentation for full details on the Query class and its methods).

Writing the age example from above with a lambda expression gives us:

editor.Where( q => q.Where( "age", 18, ">" ) );

Where things start to get interesting is the fourth parameter of the Query.Where() method (note that it is different from Editor.Where()!) - the fourth parameter is an optional boolean parameter and will instruct the query as to whether the value should be bound (i.e. escaped) or not. By default it will be, but disabling the binding means we can pass in SQL statements, arrays and other complex expressions.

Consider for example the following to select all records from the last 14 days (this is MySQL specific SQL, but the C# code shown can be used for all database types and the SQL modified as required):

editor.Where( q =>
    q.Where( "date", "DATE_ADD( NOW(), INTERVAL -14 DAY )", ">=", false );
);

without the fourth parameter the DATE_ADD(...) would be escaped as a string, but with the false passed in it will not be, and can thus be evaluated (i.e. WHERE date >= DATE_ADD( NOW(), INTERVAL -14 DAY )).

Grouping conditions

As with the simple use case, multiple conditions can be applied to complex queries simply by calling the conditional methods multiple times. The combination of the conditions is left to right, and based on the method called (i.e. OrWhere() and AndWhere()).

Somewhat Inception like these methods can all accepts delegate methods themselves which will group conditions. Consider the following:

editor.Where( q =>
    q
        .Where( "age", "18", ">" )
        .OrWhere( r => {
            r.Where( "name", "Allan" );
            r.Where( "location", "Edinburgh" );
        } )
);

The above will produce the following SQL:

WHERE age > 18 OR (
    name = "Allan" AND location = "Edinburgh"
)

Simple OR condition

To perform a query with an OR condition when using Editor, a grouping is required, as shown below. This is required as Editor will append its own conditions to the queries, as and when it needs to. For example, when reading recently edited data, the primary key value is added as a condition to ensure only that record is read. Grouping your condition ensures that there can be no incorrect interaction between the condition added by Editor and your own.

editor.Where( q => {
    q.Where( r => {
        r.Where( "name", "Allan" );
        r.OrWwhere( "location", "Edinburgh" );
    } );
} );

// Resulting SQL: WHERE (name = "Allan" OR location = "Edinburgh")

Sub-selects

SQL WHERE statements aren't limited to simple values and function calls - you can also use sub-selects whereby the value checked against is itself the result of another query. With the optional binding parameter of Query.Where() we can use sub-selects in an Editor query.

The following example will select all rows which have an accessLevel value that is in the array of results from a query on an access table:

editor.Where( q =>
    q.Where( "accessLevel", "(SELECT id FROM access WHERE level LIKE "%admin%")", "IN", false );
)

These expressions can get as complex as you like!

Binding submitted data

Thus far we have worked with data that is not user submitted, but if you've read anything about SQL security you will know about the hazards of SQL injection attacks when handling user data. This data must be correctly escaped! If you are using complex expressions where the binding parameter of the Query condition methods is set to false you must use the Query.Bind() method to perform this action.

Consider the following example where the property city is submitted as part of an HTTP POST request:

editorWhere( q => {
    q.Where( "location", "(SELECT id FROM cities WHERE name LIKE "%:city%")", "IN", false );
    q.Bind( ":city", Request.Form["city"] );
} );

If you've done any work with parameters in ADO.NET this will be immediately familiar to you.

Setting field values

As noted above the conditional operators of the Editor class apply to data being read only - not to data being written to the database. When writing to the database you may wish to have Editor write specific values that are not in the user submitted form. For example an updated_date column, or anything else that might be part of the query condition.

This can be done using the Field.Set() and Field.SetValue() methods:

• Field.Set() is used to specify when a field should be written to - it can be one of:
  • true - Same as Field.SetType.Both (default)
  • false - Same as Field.SetType.None
  • Field.SetType.Both - Set the database value on both create and edit commands
  • Field.SetType.None - Never set the database value
  • Field.SetType.Create - Set the database value only on create
  • Field.SetType.Edit - Set the database value only on edit
• Field.SetValue() is the value to be used when writing to the database.

Consider the following two fields, a created date field and an updated field:

editor.Field(
    new Field( "created" )
        .Set( Field.SetType.Create )
        .SetValue( date("yyyy-MM-dd") )
);

editor.Field(
    new Field( "updated" )
        .Set( Field.SetType.Edit )
        .SetValue( date("yyyy-MM-dd") )
);

This method can also be used to write information that might be stored in a session such as an editor's id into the database:

editor.Field(
    new Field( "last_author" )
        .SetValue( Session["user_id"] )
);

.NET API documentation

The .NET API developer documentation for the Editor .NET classes is available for detailed and technical discussion about the methods and classes discussed above.