SPIP

[ar] [ast] [bg] [br] [ca] [co] [cpf] [cs] [da] [de] [en] [eo] [es] [eu] [fa] [fon] [fr] [gl] [id] [it] [ja] [lb] [nl] [oc] [pl] [pt] [ro] [ru] [sk] [sv] [tr] [uk] [vi] [zh] Espace de traduction

Download

Joins between tables

August 2010

All the versions of this article:

Some examples of loops that generate multiple joins between 3 tables
[spip 2.0.9]


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


Show the template of this page Site powered by SPIP | Translation area | Private area