Joins (koppelingen) tussen tabellen

Voorbeelden van lussen die in één keer meerdere tabellen combineren
vanaf [spip 2.0.9]

Apparu en : SPIP 2.0

We willen in een enkele lus de informatie opvragen van artikelen uit één of meerdere rubrieken waaraan een bepaald trefwoord is gekoppeld.

<BOUCLE_a(ARTICLES spip_mots_rubriques spip_mots) {titre_mot=trefwoord}>

zal resulteren in de volgende SQL query:

  SELECT articles.id_rubrique,
         articles.id_article,
         ...
         articles.lang
    FROM spip_articles AS <span class="base64" title="PGNvZGUgY2xhc3M9J3NwaXBfY29kZSBzcGlwX2NvZGVfaW5saW5lJyBkaXI9J2x0cic+YXJ0aWNsZXM8L2NvZGU+"></span>
         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 = 'trefwoord'
GROUP BY articles.id_article

We willen in een enkele lus een willekeurig document uit een hoofdrubriek (13) kiezen. Deze hoofdrubriek bevat geen artikelen, maar uitsluitend rubrieken met bijlages (een fotoverzameling).

<BOUCLE_d(spip_documents_liens rubriques)
          {objet = rubrique}
          {rubriques.id_secteur = 13}
          {par hasard}
          {0, 1}>
  #LOGO_DOCUMENT
</BOUCLE_d>

- De tabel spip_documents_liens bevat de koppeling tussen een document en een object (artikel, rubriek, trefwoord, site...).

In deze tabel vind je regels als:

id_document	id_objet	objet	vu
14	36	article	non
363	66	rubrique	non
...	...	...	...

We zoeken dus in deze tabel de documenten die zijn gekoppeld aan een rubriek {objet = rubrique}, maar we willen ook dat deze rubriek onderdeel is van de hoofdrubriek (id_secteur) 13. We maken dus in onze query een join tussen de tabel spip_documents_liens en spip_rubriques. Deze join wordt gemaakt tussen:
de id_objet van spip_documents_liens, en
de id_rubrique van spip_rubriques

Daarom vertellen we SPIP dat je deze specifieke join willen door de twee tabellen in de lus te vermelden <BOUCLE_d(spip_documents_liens rubriques)...

Om ons tenslotte te beperken tot de hoofdrubriek 13 vermelden we de voorwaarde {rubriques.id_secteur = 13} door expliciet de volledige naam van het veld aan te geven (inclusief de naam van de tabel) om te voorkomen dat de query gaat zoeken naar een veld spip_documents_liens.id_secteur dat niet bestaat.

- Via deze lus hebben we dus toegang tot alle velden van spip_documents_liens en die van spip_rubriques:

#ID_DOCUMENT
#ID_OBJET
#OBJET
#VU

#ID_RUBRIQUE
#ID_PARENT
#TITRE
#DESCRIPTIF
#TEXTE
#ID_SECTEUR
...

- De SQL-query die door onze lus wordt gemaakt 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

- Tenslotte retourneert #LOGO_DOCUMENT ons de HTML-bron:

<img src='local/cache-vignettes/L135xH150/Image_10-d84e2.png'
width='135' height='150' style='height:150px;width:135px;' alt=''
class='spip_logos' />

en wanneer wij ook de bestandsnaam van het document zouden willen opvragen (het veld spip_documents.fichier) zouden we nog een extra join moeten doen met de tabel spip_documents:

<BOUCLE_d(spip_documents_liens documents rubriques)
          {objet = rubrique}
          {rubriques.id_secteur = 13}
          {par hasard}
          {0, 1}>
#LOGO_DOCUMENT / #FICHIER
</BOUCLE_d>

met als gevolg deze query:

  SELECT rand() AS alea,
         spip_documents_liens.id_document,
         L2.fichier
    FROM spip_documents_liens AS <span class="base64" title="PGNvZGUgY2xhc3M9J3NwaXBfY29kZSBzcGlwX2NvZGVfaW5saW5lJyBkaXI9J2x0cic+c3BpcF9kb2N1bWVudHNfbGllbnM8L2NvZGU+"></span>
         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

Opgelet nr 1:
- We hebben hier toegang tot bijna alle velden van de 3 tabellen
«bijna» want er zijn enkele homoniemen:
#TITRE, #DESCRIPTIF, #MAJ, #STATUT en #DATE bestaan in spip_documents en in spip_rubriques. Weergegeven worden die van spip_documents (de eerste tabel van de query)!

Opgelet nr 2:
- De schrijfwijze van de tabelnamen

waar:

  <BOUCLE_d(DOCUMENTS_LIENS
  <BOUCLE_d(documents_liens
  <BOUCLE_d(SPIP_DOCUMENTS_LIENS
  <BOUCLE_d(spip_documents_liens

gelijkwaardig zijn;

en:

  <BOUCLE_d(documents_liens documents rubriques
  <BOUCLE_d(documents_liens documents spip_rubriques
  <BOUCLE_d(documents_liens documents RUBRIQUES

gelijkwaardig zijn;

en ook:

  <BOUCLE_d(documents_liens documents
  <BOUCLE_d(documents_liens spip_documents
  <BOUCLE_d(spip_documents_liens spip_documents
  <BOUCLE_d(spip_documents_liens documents

gelijkwaardig zijn;

is dat niet het geval met:

  <BOUCLE_d(DOCUMENTS_LIENS DOCUMENTS
  <BOUCLE_d(documents_liens DOCUMENTS
  <BOUCLE_d(SPIP_DOCUMENTS_LIENS DOCUMENTS
  <BOUCLE_d(spip_documents_liens DOCUMENTS

waar het gebruik van hoofdletters bij documents soms een probleem in de join tussen spip_documents_liens en spip_documents oplevert.

Auteur Hanjo Gepubliceerd op: 24/03/16 Aangepast: 28/04/23

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