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 Node.JS 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

The Editor.where() method exposes the Knex.JS query object, so you can use any query condition that Knex supports. For full documentation of all those methods, please refer to the Knex.js documentation - the documentation here will show how it can be used with Editor, but will not detail the API methods offered by Knex.

Knex allows the condition to be provided either as a list of parameters, an object or a function - all of these options are supported by the Editor.where() method. For example the following code equates to be user_id = 12:

editor.where( 'user_id', 12 );

Equally, using an object to specify the condition, the above statement can be written as:

editor.where( {
    user_id: 12
} )

And finally, as a function, the following is again the equivalent of the above:

editor.where( function () {
    this.where( 'user_id', 12 );
} );

Knex also allows for more complex expressions through an optional third parameter for the where() method:

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. Use a function with the Knex orWhere and similar methods if you need to use other operators.

Advanced usage

As noted Knex.JS provides a wealth of conditional operators and it is possible to perform a sub-select conditions, conditions based on function return values and so on. Please refer to the Knex.js documentation for further documentation on how to use these methods. The key thing to keep in mind is that you need to use it as a function to be able to access the Knex methods - e.g.:

editor.where( function () {
    this
        .where( 'age', '>', '18' )
        .orWhere( function () {
            this.where( 'name', 'Allan' );
            this.where( 'location', 'Edinburgh' );
        } )
);

Will produce the following SQL:

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

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( new Date().toISOString() )
);

editor.field(
    new Field( 'updated' )
        .Set( Field.SetType.Edit )
        .SetValue( new Date().toISOString() )
);

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( req.session.user_id )
);

Node.JS API documentation

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