Discussion :

[mastermool]

Bonjour,


Excusez moi si je me trompe d'endroit pour poster, je suis nouveau, d'habitude je me débrouille toujours mais là je me casse les dents sur ce soucis.
Je travaille sur Wordpress,mysql et avec un plugin de gestion des membres qui se nomme WP-member
J'ai modifié le plugin pour pouvoir faire en sorte que l'utilisateur rentre son code postal.


Le soucis est qu'avec Wordpress toutes les informations concernant les users se stockent dans la table usermeta.

Voici la structure de la table:
umeta_id (pk): n'est pas l'id du user mais l'id de l'enregistrement c'est à dire cela peut être le nom du user , son code postal etc. Plusieurs umeta_id peuvent être affilié à l'id du user.
user_id (fk) : est l'id du user, elle est pk dans une autre table mais l'autre table ne peut pas stocker les données que je veux car la table est non modifiable sous wordpress.
meta_key: est le titre de la donnée, par exemple nom, code postal. On peut donc trouver plusieurs fois la même meta_key pour des users différents.
meta_value: réponse à la meta_key, c'est là que ce trouve les données enregistrées.

exemple d'un user en simplifié:

umeta_id | user_id | meta_key | meta_value
-----------------------------------------------------------------
1 | 1 | nom | Toto
2 | 1 | codepostal | 66000
... | 1 | ... | ...
15 | 1 | ville | Perpignan

Le probleme avec cette structure est que je pense que plus les users vont s'accumuler plus les requetes pour récupérer les codes postaux seront longues.
Car pour un user il peut y avoir 15 enregistrements différents. Si il y a 10000 users il va y avoir donc 150000 row à parcourir pour récupérer les codes postaux que l'on veut . J'aimerai avoir votre avis car je pense que la structure de cette table pose problème.



Je voudrai donc créer une nouvelle table utilisateurs avec une structure qui ressemblerait à cela (je n'ai pas besoin de la ville) pour faciliter les requêtes et surement leur temps de réponse.

user_id | nom | codepostal
-----------------------------------
1 | toto | 66000


Pour la création de la table pas de soucis , en ce qui concerne de l'implémenter via trigger à partir de la table usermeta je n'arrive qu'à récupérer le user_id et le nom grace à l'appel de new.user_id mais je n'arrive pas à récupérer le code postal . Trigger ci-dessous:

Code :

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

1
2
3
4
5
insert into utilisateurs (user_id,nom,codepostal) values (
(select distinct user_id  from usermeta where user_id=new.user_id ),
(select meta_value from te_usermeta  where meta_key='nom' and user_id=new.user_id),
(select meta_value from te_usermeta  where meta_key='codepostal' and user_id=new.user_id)
)

Je me dis qu'un update sera pire .
Quelqu'un peut il me guider?
Je vous remercie d'avance

[Artemus24]

Salut mastermool (= maelstrom + o).

Code :

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `tabone`
--------------
 
--------------
CREATE TABLE `tabone`
( `id`          integer unsigned  NOT NULL auto_increment primary key,
  `user_id`     integer unsigned  NOT NULL,
  `meta_key`    varchar(255)      NOT NULL,
  `meta_value`  varchar(255)      NOT NULL,
  index `idx` (`user_id`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `tabone` (`user_id`,`meta_key`,`meta_value`) VALUES
(1, 'nom',       'Toto'),
(1, 'codepostal', '66000'),
(1, 'ville',      'Perpignan'),
(2, 'nom',       'Tata'),
(2, 'codepostal', '21000'),
(2, 'ville',      'Dijon'),
(3, 'nom',       'Titi'),
(3, 'codepostal', '39000'),
(3, 'ville',      'Lons-le-saunier'),
(4, 'nom',       'Tutu'),
(4, 'codepostal', '75000'),
(4, 'ville',      'Paris'),
(5, 'nom',       'Tete'),
(5, 'codepostal', '13000'),
(5, 'ville',      'Marseille')
--------------
 
--------------
select * from `tabone`
--------------
 
+----+---------+------------+-----------------+
| id | user_id | meta_key   | meta_value      |
+----+---------+------------+-----------------+
|  1 |       1 | nom        | Toto            |
|  2 |       1 | codepostal | 66000           |
|  3 |       1 | ville      | Perpignan       |
|  4 |       2 | nom        | Tata            |
|  5 |       2 | codepostal | 21000           |
|  6 |       2 | ville      | Dijon           |
|  7 |       3 | nom        | Titi            |
|  8 |       3 | codepostal | 39000           |
|  9 |       3 | ville      | Lons-le-saunier |
| 10 |       4 | nom        | Tutu            |
| 11 |       4 | codepostal | 75000           |
| 12 |       4 | ville      | Paris           |
| 13 |       5 | nom        | Tete            |
| 14 |       5 | codepostal | 13000           |
| 15 |       5 | ville      | Marseille       |
+----+---------+------------+-----------------+
--------------
DROP TABLE IF EXISTS `tabtwo`
--------------
 
--------------
CREATE TABLE `tabtwo`
( `user_id`     integer unsigned  NOT NULL primary key,
  `nom`         varchar(255)          NULL DEFAULT NULL,
  `codepostal`  integer               NULL DEFAULT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP PROCEDURE IF EXISTS `init`
--------------
 
--------------
CREATE PROCEDURE `init` ()
DETERMINISTIC
NO SQL
BEGIN
  DECLARE _userid      INTEGER       DEFAULT NULL;
  DECLARE _nom         VARCHAR(255)  DEFAULT NULL;
  DECLARE _codepostal  INTEGER       DEFAULT NULL;
 
  DECLARE _fin         INTEGER      DEFAULT 1;
 
  DECLARE _tab  CURSOR FOR SELECT user_id, max(case meta_key when 'nom'        then meta_value end) as nom,
                                           max(case meta_key when 'codepostal' then meta_value end) as codepostal
                FROM `tabone` group by user_id order by nom;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET _fin = 0;
 
  OPEN  _tab;
  FETCH _tab INTO _userid, _nom, _codepostal;
 
  WHILE (_fin)
  DO
    INSERT INTO `tabtwo` (`user_id`,`nom`,`codepostal`) VALUES (_userid, _nom, _codepostal);
 
    FETCH _tab INTO _userid, _nom, _codepostal;
  END WHILE;
 
  CLOSE _tab;
END
--------------
 
--------------
call `init` ()
--------------
 
--------------
select * from `tabtwo`
--------------
 
+---------+------+------------+
| user_id | nom  | codepostal |
+---------+------+------------+
|       1 | Toto |      66000 |
|       2 | Tata |      21000 |
|       3 | Titi |      39000 |
|       4 | Tutu |      75000 |
|       5 | Tete |      13000 |
+---------+------+------------+
--------------
DROP TRIGGER IF EXISTS `ajouter`
--------------
 
--------------
CREATE TRIGGER `ajouter`
BEFORE insert ON `tabone`
FOR EACH ROW
BEGIN
  DECLARE _nom         VARCHAR(255)  DEFAULT NULL;
  DECLARE _codepostal  INTEGER       DEFAULT NULL;
 
  SET _nom        = (select nom        from `tabtwo` where user_id = new.user_id);
  SET _codepostal = (select codepostal from `tabtwo` where user_id = new.user_id);
 
  IF new.meta_key = 'nom'        then replace into `tabtwo` (`user_id`,`nom`,`codepostal`) values (new.user_id, new.meta_value,_codepostal); END IF;
  IF new.meta_key = 'codepostal' then replace into `tabtwo` (`user_id`,`nom`,`codepostal`) values (new.user_id,_nom,new.meta_value);         END IF;
END
--------------
 
--------------
INSERT INTO `tabone` (`user_id`,`meta_key`,`meta_value`) VALUES
  (10, 'nom',        'alfred'),
  (12, 'codepostal', '32000'),
  (10, 'codepostal', '17000'),
  (12, 'nom',        'jean')
--------------
 
--------------
select * from `tabtwo`
--------------
 
+---------+--------+------------+
| user_id | nom    | codepostal |
+---------+--------+------------+
|       1 | Toto   |      66000 |
|       2 | Tata   |      21000 |
|       3 | Titi   |      39000 |
|       4 | Tutu   |      75000 |
|       5 | Tete   |      13000 |
|      10 | alfred |      17000 |
|      12 | jean   |      32000 |
+---------+--------+------------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

@+

[mastermool]

Salut et déjà merci beaucoup, pour ainsi dire ce que tu m'as donné marche à la perfection, comme tu peux te douter c'est cette partie là :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
CREATE TRIGGER `ajouter`
BEFORE insert ON `tabone`
FOR EACH ROW
BEGIN
  DECLARE _nom         VARCHAR(255)  DEFAULT NULL;
  DECLARE _codepostal  INTEGER       DEFAULT NULL;
 
  SET _nom        = (select nom        from `tabtwo` where user_id = new.user_id);
  SET _codepostal = (select codepostal from `tabtwo` where user_id = new.user_id);
 
  IF new.meta_key = 'nom'        then replace into `tabtwo` (`user_id`,`nom`,`codepostal`) values (new.user_id, new.meta_value,_codepostal); END IF;
  IF new.meta_key = 'codepostal' then replace into `tabtwo` (`user_id`,`nom`,`codepostal`) values (new.user_id,_nom,new.meta_value);         END IF;
END

que j'essayai de trouver mais je n'étais donc pas allé sur la bonne voie vu mes compétences sur le sujet.
J'ai créé donc 2 triggers before insert et before update (au cas un utilisateur mette à jour son code postal)


Par contre, comme j'aime toujours comprendre les codes, peux tu me répondre à ces questions afin que je saisisse mieux pour l'avenir:

- Pourquoi un before insert et pas un after insert?
- Comment ces "variables" utilisant les requêtes:

Code :

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

1
2
3
 
  SET _nom        = (select nom        from `tabtwo` where user_id = new.user_id);
  SET _codepostal = (select codepostal from `tabtwo` where user_id = new.user_id);

peut elle fonctionner alors que dans tabtwo, logiquement user_id n'est pas encore implémenté avant sa création ?
- Sur un autre forum on m'a dit que le système de wordpress était EAV-model, et que celui est tellement optimisé au niveau des indexs qu'il était inutile de créer une nouvelle table pour mieux structurer les données, car requeter la dessus serait aussi long, qu'en penses tu?


En tout cas je te remercie beaucoup, tu m'as ouvert les yeux sur pas mal de possibilités.

[Artemus24]

Salut mastermool.

Pourquoi un before insert et pas un after insert?

J'utilise par défaut le "before insert". La seule différence est dans la valeur de l'auto incrément.
En effet, dans le "before insert", cette valeur n'est pas connue puisqu'elle sera affectée à la ligne que si elle est insérée dans la table.
Dans le cas qui nous intéresse, il n'y a pas de différence puisque vous n'utilisez pas l'auto incrément.

Comment ces "variables" utilisant les requêtes: ... peut elle fonctionner alors que dans tabtwo, logiquement user_id n'est pas encore implémenté avant sa création ?

L'astuce repose sur le fait que la future ligne peut ne pas encore exister dans la table "tabtwo".
Je rappelle que dans la table "tabone", vous avez pour un même "user_id" plusieurs lignes, dont une pour le "nom" et une autre ligne pour le "code postal".
Le trigger va se déclencher plusieurs fois, dont au moins deux fois, l'une pour le "nom" et une autre fois pour le code postal", sauf que je ne sais pas dans quel ordre cela va se faire.

Admettons que la première fois, l'insertion va se faire sur le "nom".
Dans la variable "_nom", il y aura NULL car la ligne n'existe pas encore dans la table "tabtwo".
Même raisonnement pour la variable "_codepostal".

A la seconde insertion sur le "code postal", la ligne existe déjà mais avec le "nom" de renseigné.
Le "replace" va effectuer une destruction de la ligne puis une création.
Si je ne récupère pas le "nom" avant la destruction, je perds cette information.
Je rècupère dans la variable "_nom", le nom qui est déjà présent dans la ligne.
Maintenant, je peux créer la ligne dans la table "tabtwo" avec le "nom" et le code "postal".

Même raisonnement si la première insertion se fait sur le "code postal".

Sur un autre forum on m'a dit que le système de wordpress était EAV-model, et que celui-ci est tellement optimisé au niveau des indexs qu'il était inutile de créer une nouvelle table pour mieux structurer les données, car requeter la dessus serait aussi long, qu'en penses tu?

Je pense que la base de données utilisées par wordpress n'est pas optimisée en terme de modélisation.
Pourquoi ? Parce qu'elle doit répondre à tous les cas possibles selon les besoins des utilisateurs.
Or les besoins de chacun ne sont pas les mêmes.

En terme de performance, je n'en sais rien, mais a priori, je pense que c'est aussi le cas.
Il est fort probable que vous n'utilisez pas toutes vos tables et de ce fait, la modélisation n'est pas bonne.
Le mieux serait de refondre en totalité la base de données afin de coller au plus près de vos besoins.

@+

[mastermool]

Salut,


Je te remercie pour ces explications au top , j'ai tout bien compris , j'en sors grandi.
Dernière question, tu as mis combien de temps pour me pondre cela , tu dois faire cela souvent mais c'est juste par curiosité , si cela ne te dérange pas.
Car tu as compris le sujet du premier coup, je n’étais pas sur que mon explication était suffisamment claire pour me faire aider .


Merci encore

[Artemus24]

Salut mastermool.

Le temps de comprendre le sujet et d'écrire le script SQL. Soit environ 1/2 heures car je ne suis pas très rapide.

@+

[mastermool]

Oui donc je suis vraiment nul , mais je n'avais jamais fait de trigger chacun son truc
Mais là je vois plus de possibilité, pourtant j'avais quand même un peu chercher sur les forums mais je n'avais pas vu de script comme celui que tu m'as fourni.
Merci encore je vais essayer de clore le sujet.