Using Compound Conditions for Complex Database Queries
In any of the WebvantaScript iterators, such as w:kb:item:each, you can specify compound conditions that limit the items selected based on a combination of critera.
For multiple conditions that must all be met, you can use the "&&" symbol. That means that all conditions must be true to return a result: "name = Jane && pet = Cat" will search for records having both a name of 'Jane' and a pet of 'Cat'.
For multiple conditions where not all conditions may be met, you can use '<OR>'. This means that only one condition must be true to return a result: "season = Summer <OR> season = Spring" will search for records having either a season of 'Summer' or a season of 'Spring'.
If a condition key (the field name) is supplied, but no test value is present, then the condition will not be included in the final query. So, "name = Jane && pet = " would result in a search that only looks for name equalling "Jane", rather than requiring pet to be blank, as you might expect. While this may not be used much in direct WebvantaScript, it is very useful for URL parameters or forms, as discussed in the article Implementing Advanced Search.
Field Types
Nearly all fields can be used in a compound condition:
| Field | Type | Notes |
| updated_at, published_at, created_at | Date | Built-in item fields, use field name |
| name,author,url | String | Built-in item fields, use field name |
| sequence,rating,id | Number | Built-in item fields, use field name |
| categories, tags | Taxonomy | Built-in item fields, use field name |
| custom text fields | String | Custom-item fields, use field name |
| custom taxonomy fields | Taxonomy | Use fieldname as specified in the custom item type that references it |
| custom related item | Related Item | Use current-item-type-name.related-item-type-name.field-name |
Operators
Various operators are available for your test conditions, depending on the item’s type:
| Type | Operators |
| String | <, >, =, !=, <=, >=, =~, ~, === |
| Date or Number | <, >, =, !=, <=, >= |
| Taxonomy | = |
The meaning of most of these operators should be obvious. Here are some details on the unusual ones:
| Operator | Usage |
| =~ | regular expression |
| ~ | containment (equivalent to the regular expression .*VALUE.*) |
| === | match if blank. Normally, if no value is supplied, the term is not included in the search criteria. If you want to explicitly see if a term is the empty string, use this operator |
Regular Expression Syntax and Limitations
Our regular expression support follows most of the MySQL REGEXP 5.0 Rules.
The main items are as follows:
- "." matches any single character.
- A character class "[_]" matches any character within the brackets. For example, "[abc]" matches "a", "b", or "c". To name a range of characters, use a dash. "[a-z]" matches any letter, whereas "[0-9]" matches any digit.
- "*" matches zero or more instances of the thing preceding it. For example, "x*" matches any number of "x" characters, "[0-9]*" matches any number of digits, and ".*" matches any number of anything.
- A REGEXP pattern match succeeds if the pattern matches anywhere in the value being tested.
- To anchor a pattern so that it must match the beginning or end of the value being tested, use "^" at the beginning or "$" at the end of the pattern.
