The "!" operator

Conditional criteria of simple negation, when operating on fields that are external to the table (fields accessed by a join to another table), do not always do what one might think at first.

As an example, the criteria {titre_mot!=rose} selects, for an ARTICLES loop, all the articles which are not linked to the keyword "rose". However, the type of SQL join created selects only articles linked to at least one keyword, and where at least one of those keywords is not "rose".

But in most cases, we would simply be trying to display all articles that do not have the keyword "rose", regardless of whether they had any other keywords or not. That is the result produced by using code with a {!criterion}. The code below generates a double SQL query:

<BOUCLE_articles(ARTICLES){!titre_mot = 'X'}> ...

First, articles with keyword X are selected, then they are removed from the main SQL record selection by use of a NOT IN (selection criteria) on the actual SQL database query.

This syntax is equally valid when you want to force a join field, which could be written as follows:

<BOUCLE_articles(ARTICLES){!mots.titre = 'X'}> ...

Example

Select the sections which have no article whose title starts with an "L" or an "l". Note, however, that this query uses a regular expression (^[Ll]) which will require more calculation time from the database manager.

<BOUCLE_rub(RUBRIQUES){!articles.titre == '^[Ll]'}> ...

Author Mark Baber Published : Updated : 12/03/23

Translations : English, français, Nederlands