Joins between tables

Some examples of loops that generate multiple joins between 3 tables

  • New in : SPIP 2.0

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.

Author Mark Published : Updated : 07/07/23

Translations : català, English, français, Nederlands, українська