Discussion :

[pierrot10]

Bonjour à tous et tout bon dimanche !!

Je reviens sur des requêtes avec jointure.

J'arrive à faire ce que je veux faire mais je me demande si, c'est juste ou si on peut faire mieux.

J'ai trois tables

users
id | id_user | user_email | user_mdp
===
1 | 1 | guest@m.ch | 123
2 | 2 | titi@m.ch |321
3 | 3 |lulu|m.ch |lulu

fields
id | id_field | field_name
===
1 | 1 | nord
2 | 2 | sud
3| 3 | ouest
4 | 4 | est

L'utilisateur guest ne peux accéder au terrai nord, sud et est

J'ai créé une table de liaison n:m
users_has_fields
id | users_id_user | fields_id_field
===
1 | 1 | 1
2 | 1 | 2
3 | 1 | 4

Maintenant, j'ai fait une API qui extraire les terrains qui sont attribué à guest (je n'ai pas encore travaillé le contrôle du mot de passe, si jamais , et je vais auss vérifier que compte est actif... ). Pour le moment, je me consacre à l'extraction des terrains authorisés.

Code PHP :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
$sql_select = "SELECT u.id_user, u.user_email, u.user_mdp, u.roles_id_role, u.user_active, f.fields_id_field, f.users_id_user FROM users As u
 
			RIGHT JOIN users_has_fields AS f ON u.id_user = f.users_id_user
 
			WHERE u.user_email = '$email'
			ORDER BY f.fields_id_field ASC";
		$sql_result = $connect->query($sql_select);
 
 
		$fields = array();
		if($sql_result->num_rows > 1)
		{
 
			while($row = $sql_result->fetch_assoc())
			{
				print_r($row);
 
			}
 
	    	$code = 1;
	    	$message ="Account YES";
 
		}
		else
		{
			$code = 0;
			$message = '['.$code.'] E-mail ou mot de passe invalide';
		}

Le print_r dans le while me donne ceci

Array
(
[id_user] => 3
[user_email] => guest@m.ch
[user_mdp] => toto
[roles_id_role] => 3
[user_active] => 1
[fields_id_field] => 1
[users_id_user] => 3
)
Array
(
[id_user] => 3
[user_email] => guest@m.ch
[user_mdp] => toto
[roles_id_role] => 3
[user_active] => 1
[fields_id_field] => 2
[users_id_user] => 3
)
Array
(
[id_user] => 3
[user_email] => guest@m.ch
[user_mdp] => toto
[roles_id_role] => 3
[user_active] => 1
[fields_id_field] => 4
[users_id_user] => 3
)

Ce qui à première vue, c'est OK

Mais j'aurais préféré que le print_r me retourn

Array
(
[id_user] => 3
[user_email] => guest@m.ch
[user_mdp] => toto
[roles_id_role] => 3
[user_active] => 1
[fields_id_field] => array(1,2,4)
[users_id_user] => 3
)

Je pourrais construire un $user = array() dans mon while pour arriver à le QUOTE précédent, mais je me pose la question si on ne peut pas faire cela de manière plus simple, d'ou ma question1 .

Aussi j'ai commencé par un RIGHT JOIN. Je suis étonné que LEFT JOIN ou INNER JOIN me donne le même résultat!

Merci pour volumières

[Séb.]

users (id, id_user, user_email, user_mdp)
fields (id, id_field, field_name)
users_has_field (id, users_id_user, fields_id_field)

Je ne comprends pas l'intérêt de doubler les ID de users et fields


Tu veux les terrains non attribués à un utilisateur donné.

OUTER JOIN n'est pas le plus adapté car tu veux à la fois les données de l'utilisateur et à la fois les données du terrain non affecté, or de base une jointure externe ramène les données soit des 2 tables soit d'1 seule.

Tu peux faire :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
select all u.id_user, u.user_email, f.id_field, f.field_name
from users as u
cross join fields as f
where true 
	and u.id_user = 1 -- Guest
	and not exists (
		select all *
		from users_has_fields
		where (users_id_user, fields_id_field) = (u.id_user, f.id_field)
	)
;

On peut aussi utiliser l'opérateur relationnel EXCEPT.

PS : Pas de jeu de DDL fourni pour tests.

[pierrot10]

Hello!

Je ne comprends pas l'intérêt de doubler les ID de users et fields

Tout simplement pour avoir un identifiant unique pour un utilisateur et un terrain

Par contre ici (dans la réponse), ca en effet aucun intérêt

Array
(
[id_user] => 3
[user_email] => guest@m.ch
[user_mdp] => toto
[roles_id_role] => 3
[user_active] => 1
[fields_id_field] => 1
[users_id_user] => 3
)

Tu veux les terrains non attribués à un utilisateur donné.

Par conte, je veux les terrains attribués à un utilisateur.

Je vais étudier ta réponse

[pierrot10]

J'ai modifié de la sorte

Code PHP :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
$sql_select = 'SELECT u.id_user, u.user_email, u.user_mdp, u.roles_id_role, u.user_active, f.fields_id_field FROM users As u
 
			RIGHT JOIN users_has_fields AS f ON u.id_user = f.users_id_user
			WHERE u.user_email = "'. $email .'" AND u.user_mdp = "'.md5($pwd).'" AND u.user_active = 1
			ORDER BY f.fields_id_field ASC';
 
		$sql_result = $connect->query($sql_select);

à

Code PHP :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
$sql_select =' SELECT u.id_user, u.user_email, u.user_mdp, u.roles_id_role, f.id_field, f.field_name
			FROM users AS u
			CROSS JOIN fields AS f
			WHERE true 
				AND u.user_email = "'. $email .'" AND u.user_mdp = "'.md5($pwd).'" AND u.user_active = 1
				and EXISTS (
					SELECT uf.fields_id_field, uf.users_id_user
					FROM users_has_fields AS uf
					WHERE (uf.users_id_user, uf.fields_id_field) = (u.id_user, f.id_field)
			)';
		$sql_result = $connect->query($sql_select);

Mais je ne vois pas trop la différence à par que je peux utilisé des attributs des terrains (fields), ce qui est très intéressant.

Mais mon print_r($row) affiche toujours comme ceci:
Note: les mots de passe ne seront évidement pas retourner

Array
(
[id_user] => 3
[user_email] => guest@m.ch
[user_mdp] =>mm
[roles_id_role] => 3
[id_field] => 1
[field_name] => Printaniere
)
Array
(
[id_user] => 3
[user_email] => guest@m.ch
[user_mdp] =>aaaa
[roles_id_role] => 3
[id_field] => 4
[field_name] => Perrieres
)
Array
(
[id_user] => 3
[user_email] => guest@m.ch
[user_mdp] => aaaaa
[roles_id_role] => 3
[id_field] => 5
[field_name] => Duvernay
)
Array
(
[id_user] => 3
[user_email] => guest@m.ch
[user_mdp] => aaaa
[roles_id_role] => 3
[id_field] => 6
[field_name] => Jardin
)

En soit c'est tro importabt d'avoir un truc du genre comme réponse de la requête MYSQL (peut-être que je cherche la petite bête

Array
(
[id_user] => 3
[user_email] => guest@m.ch
[roles_id_role] => 3
[id_field] => array(1,4,5,6)
[field_name] => Jardin
)

Merci

[Séb.]

Je ne comprends pas l'intérêt de doubler les ID de users et fields

Tout simplement pour avoir un identifiant unique pour un utilisateur et un terrain

id | id_user | user_email | user_mdp
===
1 | 1 | guest@m.ch | 123
2 | 2 | titi@m.ch |321
3 | 3 |lulu|m.ch |lulu

À quoi servent les colonnes id et id_user ?

Par conte, je veux les terrains attribués à un utilisateur.

Ah oui, mauvaise lecture de ma part :

Array
(
  [id_user] => 3
  [user_email] => guest@m.ch
  [user_mdp] => toto
  [roles_id_role] => 3
  [user_active] => 1
  [fields_id_field] => array(1,2,4)
  [users_id_user] => 3
)

Dans ton résultat fields_id_field contient les id_field affectés. Ce n'est pas traditionnel, mais tu peux agréger les valeurs dans un tableau JSON :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
select all u.id_user, u.user_email, json_arrayagg(uhf.fields_id_field) as field_list
from users as u
inner join users_has_fields as uhf on u.id_user = uhf.users_id_user
where u.id_user = 1 -- Guest
group by 1, 2

Cela donnera une colonne JSON field_list que tu pourras transformer en tableau PHP avec json_decode().

[escartefigue]

users (id, id_user, user_email, user_mdp)
fields (id, id_field, field_name)
users_has_field (id, users_id_user, fields_id_field)
Je ne comprends pas l'intérêt de doubler les ID de users et fields

Tout simplement pour avoir un identifiant unique pour un utilisateur et un terrain

Cette base de données est mal modélisée :

• nommer les identifiants simplement "id" est une mauvaise pratique, car on ne sait pas identifiant DE QUOI, plutôt que "id", choisissez par exemple "id_user" ;
• utiliser des mots réservés SQL comme "user" pour nommer un objet est également une mauvaise pratique, çar ça oblige à encadrer ce nom avec des délimiteurs, ce qui n'est vraiment pas pratique à l'usage.
  Si vous êtes dans un contexte francophone, simplifiez-vous la vie et nommez les objets en français, dans tous les cas, évitez les noms réservés ;
• il est préférable que l'identifiant primaire d'une table porte le même nom dans les tables où il est propagé comme clef étrangère
  
  c'est ce que font par défaut tous les logiciels de modélisation, c'est tellement plus simple
  par exemple "id_user" de la table "user" devrait également s'appeler id_user dans la table "user_has_field" ainsi, les études d'impact sont facilitées ;
• la table "user_has_field" est ce qu'on appelle une table associative, car elle est issue d'une association entre "user" et "field"
  
  ce faisant, elle ne doit pas avoir d'identifiant propre, son identifiant PK est composé des PK des tables issues des types d'entités de l'association du MCD
  ici, l'association fait participer les types d'entité "user" et "field", la PK doit donc être le duo "id_user + id_field"
  si vous procédez autrement, alors les contraintes d'intégrité ne sont plus assurées entre tables parentes (user et field) et enfant (user_has_field) sauf à bidouiller le DDL au risque que les évolutions dégradent les déclaratives.
  Et une base dont l'intégrité n'est pas vérifiée est bonne pour la poubelle.

Tous ces écueils sont dus à une modélisation faite sans logiciel adhoc.

Il existe d'excellents logiciels gratuits, parmi lesquels Looping que vous pouvez télécharger gratuitement ICI

[tatayo]

alors les contraintes d'intégrité ne peuvent plus être assurées entre tables parentes (user et field) et enfant (user_has_field) et une base dont l'intégrité n'est pas vérifiée est bonne pour la poubelle.

Je connais un paquet d'ERP, WMS et autres qui sont bons pour la poubelle...

Et j'ai dû batailler ferme pour que les contraintes d'intégrité soient systématiquement mise en place dans la base dont je suis "responsable".

Tatayo.