We would like, in a single loop, to retrieve the data from some articles from one (or several) sections to which a predetermined keyword has been assigned.
<BOUCLE_a(ARTICLES spip_mots_rubriques spip_mots) {titre_mot=search_keyword}>
will generate the following SQL query:
SELECT articles.id_rubrique,
articles.id_article,
...
articles.lang
FROM spip_articles AS `articles`
INNER JOIN spip_mots_rubriques AS L1
ON L1.id_rubrique = articles.id_rubrique
INNER JOIN spip_mots AS L2
ON L2.id_mot = L1.id_mot
WHERE articles.statut = 'publie'
AND L2.titre = 'search_keyword'
GROUP BY articles.id_article
We would like, in a single loop, to randomly select a document from a sector. Note that this sector does not contain any articles of its own, just sections with their associated documents (in fact, it’s a photo library).
<BOUCLE_d(spip_documents_liens rubriques)
{objet = rubrique}
{rubriques.id_secteur = 13}
{par hasard}
{0, 1}>
#LOGO_DOCUMENT
</BOUCLE_d>
- The spip_documents_liens table is a table which collates all of the links (joins) between a document and an object (article, section, keywords, site, etc.)
Some typical entries in this table would look like:
| id_document | id_objet | objet | vu |
|---|---|---|---|
| 14 | 36 | article | non |
| 363 | 66 | rubrique | non |
| ... | ... | ... | ... |
So we are therefore going to search in this table for documents linked to a section {objet = rubrique}
But we also woulk like that this section be a descendant from sector with ID = 13.
We therefore need to establish in our query that there is a join made between the spip_documents_liens and spip_rubriques tables.
This join is established between:
the id_objet in spip_documents_liens, and
the id_rubrique in spip_rubriques
To make the join, we tell SPIP that we want this specific join by specifying 2 tables inside of the loop syntax <BOUCLE_d(spip_documents_liens rubriques)...>
Finally, to indicate our restriction concerning sector 13, we specify the criterion {rubriques.id_secteur = 13}, by explicitly detailing the full name of the field (including the table name) so that the query does not attempt to find a field spip_documents_liens.id_secteur that does not actually exist.
- Using this loop, them. we have access to all of the fields in spip_documents_liens and all of those in spip_rubriques:
#ID_DOCUMENT
#ID_OBJET
#OBJET
#VU
#ID_RUBRIQUE
#ID_PARENT
#TITRE
#DESCRIPTIF
#TEXTE
#ID_SECTEUR
...
- So far our generated SQL query for our loop is:
SELECT rand() AS alea,
spip_documents_liens.id_document
FROM spip_documents_liens AS `spip_documents_liens`
INNER JOIN spip_rubriques AS L1
ON L1.id_rubrique = spip_documents_liens.id_objet
AND spip_documents_liens.objet = 'rubrique'
WHERE spip_documents_liens.objet = 'rubrique'
AND L1.id_secteur = 13
GROUP BY spip_documents_liens.id_document,
spip_documents_liens.id_objet,
spip_documents_liens.objet
ORDER BY alea
LIMIT 0,1
- Finally, the #LOGO_DOCUMENT tag will return us the desired HTML source to the document:
<img src='local/cache-vignettes/L135xH150/Image_10-d84e2.png'
width='135' height='150' style='height:150px;width:135px;' alt=''
class='spip_logos' />
Now if we would also like the document’s file name, since we can not directly access it with this request since the spip_documents.fichier field has not been collected (no join to the spip_documents table), then we need to declare a supplementary join to the spip_documents table:
<BOUCLE_d(spip_documents_liens documents rubriques)
{objet = rubrique}
{rubriques.id_secteur = 13}
{par hasard}
{0, 1}>
#LOGO_DOCUMENT / #FICHIER
</BOUCLE_d>
The SQL query will then be:
SELECT rand() AS alea,
spip_documents_liens.id_document,
L2.fichier
FROM spip_documents_liens AS `spip_documents_liens`
INNER JOIN spip_documents AS L2
ON L2.id_document = spip_documents_liens.id_document
INNER JOIN spip_rubriques AS L1
ON L1.id_rubrique = spip_documents_liens.id_objet
AND spip_documents_liens.objet='rubrique'
WHERE spip_documents_liens.objet = 'rubrique'
AND L1.id_secteur = 13
GROUP BY spip_documents_liens.id_document,
spip_documents_liens.id_objet,
spip_documents_liens.objet
ORDER BY alea
LIMIT 0,1
Important note n° 1:
- in this scenario, we almost have access to all of the fields in the 3 tables
"almost" since there are some fields with exactly the same names:
the #TITRE, #DESCRIPTIF, #MAJ, #STATUT and #DATE fields displayed have corresponding fields with the same names in both spip_documents and spip_rubriques, and those displayed will be from spip_documents, (the first table in the query) !
Important note n° 2:
- the syntaxes for writing table names
Even though:
<BOUCLE_d(DOCUMENTS_LIENS
<BOUCLE_d(documents_liens
<BOUCLE_d(SPIP_DOCUMENTS_LIENS
<BOUCLE_d(spip_documents_liens
are all equivalent;
as is the also the case with:
<BOUCLE_d(documents_liens documents rubriques
<BOUCLE_d(documents_liens documents spip_rubriques
<BOUCLE_d(documents_liens documents RUBRIQUES
being equivalent to each other;
and even this set:
<BOUCLE_d(documents_liens documents
<BOUCLE_d(documents_liens spip_documents
<BOUCLE_d(spip_documents_liens spip_documents
<BOUCLE_d(spip_documents_liens documents
are also equivalent amongst themselves;
we can not say the same about this set:
<BOUCLE_d(DOCUMENTS_LIENS DOCUMENTS)>
<BOUCLE_d(documents_liens DOCUMENTS)>
<BOUCLE_d(SPIP_DOCUMENTS_LIENS DOCUMENTS)>
<BOUCLE_d(spip_documents_liens DOCUMENTS)>
where writing documents in capitals will cause a loss of the automatic join between spip_documents_liens and spip_documents
Declared and undeclared tables
The above documentation relates to tables in the SPIP core, and is valid for all tables declared using the Table Declaration API.
When a table is not declared, its full name must be used, with the prefix, in lower case.
See also programmer.spip.net: Automatic joins.