Join 3 Tables

Lucas_Castro · Juin 26, 2019, 8:16

Dear SPIP Comunity,

While using the loops for a template, I read that there was the option to join two tables.
Now I wanted to join two tables with an intermediate table in between, I also found an example documented here (). But I couldn’t make it work for me.

I use the tables spip_forum, spip_mots_liens and spip_mots.

This is the Loop:

<BOUCLE_questions(spip_forum spip_mots_liens spip_mots){spip_mots.titre = « action1 »}{tous}>

#TITRE

AUTEUR

#TEXTE

This is the generated SQL-Statement:

JLuc · Juin 26, 2019, 9:24

Le 26/06/2019 à 22:16, Lucas Castro a écrit :

Dear SPIP Comunity,

While using the loops for a template, I read that there was the option to join two tables.
Now I wanted to join two tables with an intermediate table in between, I also found an example documented here (https://programmer.spip.net/Forcing-joins).
But I couldn't make it work for me.

I use the tables spip_forum, spip_mots_liens and spip_mots.

This is the Loop:
                 &lt;BOUCLE\_questions$spip\_forum spip\_mots\_liens spip\_mots$\{spip\_mots\.titre = &quot;action1&quot;\}\{tous\}&gt;
                     &lt;div class=&quot;question&quot;&gt;
                         &lt;h4&gt;\#TITRE&lt;/h4&gt;
                         &lt;h6&gt;AUTEUR&lt;/h6&gt;
                         \#TEXTE
                     &lt;/div&gt;
                 &lt;/BOUCLE\_questions&gt;

I wont answer precisely but here are remarks that could be usefull.

Usualy the first table is 1) uppercase, 2) plural and 3) without prefix : 'FORUMS'
The following ones are lowercase.

For example in spip core ical_prive.html :
<BOUCLE_message_n_a(AUTEURS auteurs_messages){lang_select=non}{id_message}>

In GIS plugin gis_kml.html :
<BOUCLE_gis(GIS gis_liens){id_gis ?}{id_rubrique ?}{id_article ?}...>

In the media plugin of spip-dist :
<BOUCLE_illustrations(DOCUMENTS documents_liens types_documents){inclus=image}{mode=image}{id_objet}{objet}{par rang_lien, num titre, date,id_document}{pagination 50}{statut?}>

Moreoever, SPIP compiler tries its best to create efficient code and so as to achieve this, it doesnt join useless tables. So you have to ensure that at least one field for each table is used in the BOUCLE as a #FIELD or criteria.

Then it requires some searches and trials...

JL

This is the generated SQL-Statement:

SELECT forum.titre, forum.auteur, forum.texte
FROM spip_forum AS `forum`
WHERE (forum.statut = 'publie')
AND (L1.titre = 'action1')
GROUP BY spip_forum.id_forum

This is what I expected it to be:

SELECT <https://dev.mysql.com/doc/refman/5.7/en/select.html> forum.titre, forum.auteur, forum.texte FROM spip_forum <https://lucas.naturechallenge.swiss/adminer-4.6.0-en.php?username=o53j7_nc_dev_usr&db=o53j7_nc_dev&table=spip_forum> AS `forum` INNER JOIN <https://dev.mysql.com/doc/refman/5.7/en/join.html> spip_mots_liens <https://lucas.naturechallenge.swiss/adminer-4.6.0-en.php?username=o53j7_nc_dev_usr&db=o53j7_nc_dev&table=spip_mots_liens> AS L2 ON forum.id_forum = L2.id_objet INNER JOIN <https://dev.mysql.com/doc/refman/5.7/en/join.html> spip_mots <https://lucas.naturechallenge.swiss/adminer-4.6.0-en.php?username=o53j7_nc_dev_usr&db=o53j7_nc_dev&table=spip_mots> AS L1 ON L1.id_mot = L2.id_mot WHERE (forum.statut = 'publie') AND <https://dev.mysql.com/doc/refman/5.7/en/logical-operators.html#operator_and> (L1.titre = 'action1') GROUP BY forum.id_forum;

My guess is, that this has something to do with the id's which don't have the same name in both tables spip_forum and spip_mots_liens, but spip_mots_liens can not have the id called differently because it adresses multiple tables.

So now my question is, can I join three tables together of which one is the intermediate table with the loop-syntax, if yes how?

Thanks in advance for the help and best regards,

Lucas Castro

JLuc · Juin 27, 2019, 3:51

FYI Lucas answered :
I tried with the Syntax you wrote with the uppercase and so on
but this didn't work.
But the Idea with looking into source already existing was the key.
In forum.php they declare the Join between spip_mots and spip_forum:

// il ne faut pas essayer de chercher le forum du mot cle, mais bien
le mot cle associe au forum
$interfaces['exceptions_des_jointures']['spip_forum']['titre_mot'] =
array('spip_mots', 'titre');

So I can just do the join without the intermediate table like:
(FORUM spip_mots){titre_mot = "action1"}{tous}

Thanks a lot for the help!

Lucas

On 6/26/19 11:24 PM, JLuc wrote:
> Le 26/06/2019 à 22:16, Lucas Castro a écrit :
>> Dear SPIP Comunity,
>>
>> While using the loops for a template, I read that there was the
>> option to join two tables.
>> Now I wanted to join two tables with an intermediate table in
>> between, I also found an example documented here
>> (https://programmer.spip.net/Forcing-joins).
>> But I couldn't make it work for me.
>>
>> I use the tables spip_forum, spip_mots_liens and spip_mots.
>>
>> This is the Loop:
>>
>> <BOUCLE_questions(spip_forum spip_mots_liens
>> spip_mots){spip_mots.titre = "action1"}{tous}>
>> <div class="question">
>> <h4>#TITRE</h4>
>> <h6>AUTEUR</h6>
>> #TEXTE
>> </div>
>> </BOUCLE_questions>
>
> I wont answer precisely but here are remarks that could be usefull.
>
> Usualy the first table is 1) uppercase, 2) plural and 3) without
> prefix : 'FORUMS'
> The following ones are lowercase.
>
> For example in spip core ical_prive.html :
> <BOUCLE_message_n_a(AUTEURS
> auteurs_messages){lang_select=non}{id_message}>
>
> In GIS plugin gis_kml.html :
> <BOUCLE_gis(GIS gis_liens){id_gis ?}{id_rubrique ?}{id_article ?}...>
>
> In the media plugin of spip-dist :
> <BOUCLE_illustrations(DOCUMENTS documents_liens
> types_documents){inclus=image}{mode=image}{id_objet}{objet}{par
> rang_lien, num titre, date,id_document}{pagination 50}{statut?}>
>
> Moreoever, SPIP compiler tries its best to create efficient code and
> so as to achieve this, it doesnt join useless tables. So you have to
> ensure that at least one field for each table is used in the BOUCLE as
> a #FIELD or criteria.
>
> Then it requires some searches and trials...
>
> JL
>
>>
>> This is the generated SQL-Statement:
>>
>> SELECT forum.titre, forum.auteur, forum.texte
>> FROM spip_forum AS `forum`
>> WHERE (forum.statut = 'publie')
>> AND (L1.titre = 'action1')
>> GROUP BY spip_forum.id_forum
>>
>> This is what I expected it to be:
>>
>> SELECT <https://dev.mysql.com/doc/refman/5.7/en/select.html>
>> forum.titre, forum.auteur, forum.texte FROM spip_forum
>> <https://lucas.naturechallenge.swiss/adminer-4.6.0-en.php?username=o53j7_nc_dev_usr&db=o53j7_nc_dev&table=spip_forum>
>> AS `forum` INNER JOIN
>> <https://dev.mysql.com/doc/refman/5.7/en/join.html> spip_mots_liens
>> <https://lucas.naturechallenge.swiss/adminer-4.6.0-en.php?username=o53j7_nc_dev_usr&db=o53j7_nc_dev&table=spip_mots_liens>
>> AS L2 ON forum.id_forum = L2.id_objet INNER JOIN
>> <https://dev.mysql.com/doc/refman/5.7/en/join.html> spip_mots
>> <https://lucas.naturechallenge.swiss/adminer-4.6.0-en.php?username=o53j7_nc_dev_usr&db=o53j7_nc_dev&table=spip_mots>
>> AS L1 ON L1.id_mot = L2.id_mot WHERE (forum.statut = 'publie') AND
>> <https://dev.mysql.com/doc/refman/5.7/en/logical-operators.html#operator_and>
>> (L1.titre = 'action1') GROUP BY forum.id_forum;
>>
>> My guess is, that this has something to do with the id's which don't
>> have the same name in both tables spip_forum and spip_mots_liens, but
>> spip_mots_liens can not have the id called differently because it
>> adresses multiple tables.
>>
>> So now my question is, can I join three tables together of which one
>> is the intermediate table with the loop-syntax, if yes how?
>>
>> Thanks in advance for the help and best regards,
>>
>> Lucas Castro
>>
>
> _______________________________________________
> spip-en@rezo.net - https://listes.rezo.net/mailman/listinfo/spip-en