{where}

In some cases, SPIP’s specific criteria do not allow a complex request to be described. Loops can use the {where} criterion to directly specify the WHERE of the SQL query.

  • New in : SPIP 3.0

The where criterion expects a single argument, which is the conditional part of a (MY)SQL query. SPIP uses this value by combining it with the other conditional elements from the other criteria in the loop.

Examples

The where criterion allows ORs to be performed:

<BOUCLE_or(ARTICLES){statut=redac}{where id_rubrique=10 OR id_rubrique=20}>

It allows you to call MYSQL functions:

<BOUCLE_or(ARTICLES){where YEAR(date_creation) > 1970}>

It allows you to make subqueries (here in the plug-in info_site) :
{where id_organisation IN (SELECT id_objet FROM spip_projets_liens WHERE objet='organisation' AND id_projet=#ID_PROJET)}

The where criterion makes it possible to use a condition where the 2 terms of the test are fields of the table: we want to find the articles with the same section id and sector id

<BOUCLE_where(ARTICLES){where id_rubrique = id_secteur}>

Note that it would have been possible to do it without where in all these cases:
-  with {id_rubrique IN #LIST{10,20}}
-  with {date_creation>1970-01-01}
-  with a joint in the SPIP loop
-  with a wrapping loop

Preparing the condition
When there is a comma in the where expression, often because of a MYSQL function call, include the condition in a #VAL tag.
Example :
{where #VAL{"DATE_ADD(date_debut,INTERVAL 2 WEEK)<date_fin"}}

When the conditions are even more complex, it is necessary to prepare the query in a #SET :

#SET{where, here complex calculation}
<BOUCLE_root_articles(ARTICLES) {...}{where #GET{where}}>

Conditional where criterion

The criterion also allows comparison with the value of where passed into the environment, if it exists: {where?}

Author jack Published :

Translations : English, français, Nederlands