Requête select sur plusieurs tuples

**BeRoots** · 29/10/2016, 06h56

Bonjour à tous

J'ai un ensemble deux tables comme suit :

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 TABLE IF NOT EXISTS `product_productextras` (
    `id_pei` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `id_product` int(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`id_pei`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE IF NOT EXISTS `product_productextras_lang` (
    `id_pei` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `id_lang` int(10) unsigned NOT NULL,
    `notice` text DEFAULT NULL,
    `about` text DEFAULT NULL,
    PRIMARY KEY (`id_pei`, `id_lang`),
    CONSTRAINT `fk_productextras_id_pei` FOREIGN KEY (`id_pei`) REFERENCES `'._DB_PREFIX_.'product_productextras`(`id_pei`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Je voudrai faire une requète SELECT qui me retourne les id_product (de la table product_productextras) correspondant à des id_pei contenant bien trois tuples dans la table product_productextras_lang (un par id_lang soit dans mon cas un tuple avec id_lang à 1. et deux autre pour les id_lang 2 & 3). J'aimerai aussi faire ressortir les id_product associés des id_pei qui aurai des valeurs vide soit pour leur champs notice et about.

Exemple avec cette table :

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
+---------+---------+------+---------+
|    product_productextras_lang      |
+---------+---------+-------+--------+
| id_pei  | id_lang | notice | about |
+---------+---------+-------+--------+
|    1    |    1    |   x   |   x    |
|    1    |    2    |   x   |   x    |
|    1    |    3    |   x   |   x    |
|    2    |    1    |   x   |   x    |
|    2    |    2    |   x   |   x    |
|    2    |    3    |   x   |   x    |
|    3    |    1    |   x   |   x    |
|    3    |    2    |   x   |   x    |
|    3    |    4    |   x   |   x    |
|    4    |    1    |   x   |   x    |
|    4    |    2    |   x   |   x    |
|    5    |    1    |       |        |
|    5    |    2    |       |        |
|    5    |    3    |       |        |
|    6    |    1    |   x   |   x    |
|    6    |    2    |       |        |
|    6    |    3    |   x   |   x    |
+---------+---------+-------+--------+

Est ce possible ?

Merci d'avance pour vos idées

**Artemus24** · 30/10/2016, 13h04

Salut BeRoots.

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
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `utf8`
    DEFAULT COLLATE       `utf8_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `product_productextras`
--------------
 
--------------
CREATE TABLE `product_productextras`
( `id_pei`     integer UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_product` integer UNSIGNED NOT NULL,
  PRIMARY KEY (`id_pei`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`utf8` COLLATE=`utf8_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `product_productextras` (`id_pei`,`id_product`)  values
  (1, 25), (2, 12), (3, 44), (4, 75), (5, 32), (6, 24)
--------------
 
--------------
select * from product_productextras
--------------
 
+--------+------------+
| id_pei | id_product |
+--------+------------+
|      1 |         25 |
|      2 |         12 |
|      3 |         44 |
|      4 |         75 |
|      5 |         32 |
|      6 |         24 |
+--------+------------+
--------------
DROP TABLE IF EXISTS `product_productextras_lang`
--------------
 
--------------
CREATE TABLE `product_productextras_lang`
( `id_pei`   integer unsigned NOT NULL AUTO_INCREMENT,
  `id_lang`  integer unsigned NOT NULL,
  `notice`   text                      DEFAULT NULL,
  `about`    text                      DEFAULT NULL,
  PRIMARY KEY (`id_pei`, `id_lang`),
  CONSTRAINT `fk_productextras_id_pei` FOREIGN KEY (`id_pei`) REFERENCES `product_productextras` (`id_pei`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`utf8` COLLATE=`utf8_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `product_productextras_lang` (`id_pei`,`id_lang`,`notice`,`about`) values
  (1, 1, 'x', 'x'),
  (1, 2, 'x', 'x'),
  (1, 3, 'x', 'x'),
  (2, 1, 'x', 'x'),
  (2, 2, 'x', 'x'),
  (2, 3, 'x', 'x'),
  (3, 1, 'x', 'x'),
  (3, 2, 'x', 'x'),
  (3, 4, 'x', 'x'),
  (4, 1, 'x', 'x'),
  (4, 2, 'x', 'x'),
  (5, 1, '',  ''),
  (5, 2, '',  ''),
  (5, 3, '',  ''),
  (6, 1, 'x', 'x'),
  (6, 2, '',  ''),
  (6, 3, 'x', 'x')
--------------
 
--------------
select * from product_productextras_lang
--------------
 
+--------+---------+--------+-------+
| id_pei | id_lang | notice | about |
+--------+---------+--------+-------+
|      1 |       1 | x      | x     |
|      1 |       2 | x      | x     |
|      1 |       3 | x      | x     |
|      2 |       1 | x      | x     |
|      2 |       2 | x      | x     |
|      2 |       3 | x      | x     |
|      3 |       1 | x      | x     |
|      3 |       2 | x      | x     |
|      3 |       4 | x      | x     |
|      4 |       1 | x      | x     |
|      4 |       2 | x      | x     |
|      5 |       1 |        |       |
|      5 |       2 |        |       |
|      5 |       3 |        |       |
|      6 |       1 | x      | x     |
|      6 |       2 |        |       |
|      6 |       3 | x      | x     |
+--------+---------+--------+-------+
--------------
select t1.id_pei,
       t1.id_product
 
from        product_productextras       as t1
 
inner join  product_productextras_lang  as t2
        on  t2.id_pei  = t1.id_pei
       and  t2.notice <> ''
       and  t2.about  <> ''
 
group by  t1.id_pei, t1.id_product
  having  count(distinct t2.id_lang) = 3
order by  t1.id_pei, t1.id_product
--------------
 
+--------+------------+
| id_pei | id_product |
+--------+------------+
|      1 |         25 |
|      2 |         12 |
|      3 |         44 |
+--------+------------+
--------------
explain
select t1.id_pei,
       t1.id_product
 
from        product_productextras       as t1
 
inner join  product_productextras_lang  as t2
        on  t2.id_pei  = t1.id_pei
       and  t2.notice <> ''
       and  t2.about  <> ''
 
group by  t1.id_pei, t1.id_product
  having  count(distinct t2.id_lang) = 3
order by  t1.id_pei, t1.id_product
--------------
 
+----+-------------+-------+------------+------+---------------+---------+---------+----------------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref            | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+---------+---------+----------------+------+----------+----------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | PRIMARY       | NULL    | NULL    | NULL           |    6 |   100.00 | Using filesort |
|  1 | SIMPLE      | t2    | NULL       | ref  | PRIMARY       | PRIMARY | 4       | base.t1.id_pei |    1 |    81.00 | Using where    |
+----+-------------+-------+------------+------+---------------+---------+---------+----------------+------+----------+----------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

@+

**BeRoots** · 30/10/2016, 19h52

Merci énormément pour ton aide Artemus24.

Mais ce n'est pas tout à fait cela...
En fait dans ce cas tu relève l'id_pei n° 3 mais les id_lang ne sont pas 1,2, et 3 (ici 1,2, et 4

)

en fait il faudrai bien que le compte des langues soit à 3 mais aussi que cela respect les id_lang existant d'une autre table qui s'appel lang

J'avais volontairement simplifié mon problème mais l'objectif final est de faire une colorisation de liste d'id_product (tout ceux de ma table product). Je relève donc tout les produits de product. Je doit ressortir tout les id_product n'ayant aucune entrée correspondante dans mon jeu de table fournies (CàD aucun tuple ni dans t1 ni dans t2) afin de mettre en rouge ces produits dans ma liste. Je veut aussi mettre en orange tout ceux qui aurai un manque de tuples pour chacune des langues (CàD si la table lang contient des id_lang à 1, 2 , et 3, alors je veut sélectionner les jeux de tuple de t2 qui ne respect pas ceci). Si un id_lang inexistant dans la table lang existe dans t2, je n'en tiens pas compte. Par contre si j'ai les bon id_lang pour le produit dans t2, je doit m'assurer que chacun des tuples de cette même t2 aient une longueur supérieur à 0 (non vide et non null quoi). Sinon je les met en orange aussi...

J'ai réussi en faisant cela plutôt coté PHP avec multitude de requêtes MySQL mais je me demande si il y a moyen de faire mieux avec MySQL en faite... Voilà ce que j'ai fait en PHP :

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
        /*
         * Color products with no pei in red, product with missing languages
         * in orange, and incomplete product (notice or about) in orange too 
         */
        $sql = "SELECT `id_product` FROM `" ._DB_PREFIX_. "Product` WHERE "
            . "`id_product` NOT IN (SELECT `id_product` FROM `" ._DB_PREFIX_. "product_productextras`);";
        $result = Db::getInstance()->executeS($sql, $array = true, $use_cache = false);
 
        foreach ($result as $key => $value) {
            if($value['id_product']) {
                //ICI JE COLORE EN ROUGE
                $html = preg_replace(
                    "(<option value=\"" . $value['id_product'] . "\")",
                    "(<option value=\"" . $value['id_product'] . "\" style=\"color:red\")",
                    $html,
                    -1
                );
            }
        }
 
        $array_languages = array();
        // ICI ON PEUT AUSSI AVOIR LES id_lang VIA MYSQL SUR LA TABLE " ._DB_PREFIX_. "lang 
        foreach(Language::getLanguages(false) as $key => $value) {
            $array_languages[] = $value['id_lang'];
        }
        $array_languages_count = count($array_languages);
 
        $sql = "SELECT `id_pei` FROM `" ._DB_PREFIX_. "product_productextras`;";
        $result = Db::getInstance()->executeS($sql, $array = true, $use_cache = false);
 
        foreach ($result as $key => $value) {
            $sql = "SELECT DISTINCT(`id_lang`) FROM `" ._DB_PREFIX_. "product_productextras_lang`"
                . " WHERE `id_pei`=" . $value['id_pei'] . ";";
            $result2 = Db::getInstance()->executeS($sql, $array = true, $use_cache = false);
 
            $pei_languages = array();
            foreach ($result2 as $k => $val) {
                if(in_array($val['id_lang'], $array_languages)) {
                    $pei_languages[] = $val['id_lang'];
                }
            }
 
            //if missing language
            if (count($pei_languages) < $array_languages_count) {
                //create missing languages row
                $missing_languages = array_diff($array_languages, $pei_languages);
                foreach ($missing_languages as $k => $id_lang) {
                    $sql = "INSERT INTO `" ._DB_PREFIX_. "product_productextras_lang` (`id_pei`, `id_lang`)"
                        . "VALUES (" . $value['id_pei'] . ", " .$id_lang. ");";
                    $result2 = Db::getInstance()->execute($sql);
                }
 
                // color the product in select input
                $sql = "SELECT `id_product` FROM `" ._DB_PREFIX_. "product_productextras`"
                    . "WHERE `id_pei`=" . $value['id_pei'] . ";";
                $result2 = Db::getInstance()->executeS($sql, $array = true, $use_cache = false);
 
                if(isset($result2[0]) && $result2[0]['id_product']) {
                    //ICI JE COLORE EN ORANGE
                    $html = preg_replace(
                        "(<option value=\"".$result2[0]['id_product']."\")",
                        "(<option value=\"".$result2[0]['id_product']."\" style=\"color:orange\")",
                        $html,
                        -1
                    );
                }
            }
            else {
                $color = false;
                foreach ($pei_languages as $k => $id_lang) {
                    $sql = "SELECT `id_product`, `notice`, `about` FROM `" ._DB_PREFIX_. "product_productextras`"
                        . "JOIN `" ._DB_PREFIX_. "product_productextras_lang` ON `" ._DB_PREFIX_. "product_productextras`.`id_pei` = `" ._DB_PREFIX_. "product_productextras_lang`.`id_pei` "
                        . "WHERE `" ._DB_PREFIX_. "product_productextras_lang`.`id_pei`=" .$value['id_pei']. " "
                        . "AND `id_lang`=" . $id_lang . ";";
                    $result2 = Db::getInstance()->executeS($sql, $array = true, $use_cache = false);
 
                    if($color === false && (count($result2[0]['notice']) == 0 || count($result2[0]['about']) == 0)) {
                        $color = true;
                    }
                }
 
                if ($color === true) {
                  //ICI JE COLORE EN ORANGE
                  $html = preg_replace(
                        "(<option value=\"".$result2[0]['id_product']."\")",
                        "(<option value=\"".$result2[0]['id_product']."\" style=\"color:orange\")",
                        $html,
                        -1
                    );
                }
            }
        }

Si quelqu'un à un avis à donner sur le sujet

Merci d'avance...

**Artemus24** · 31/10/2016, 18h16

Salut BeRoots.

Envoyé par BeRoots

En fait dans ce cas tu relèves l'id_pei n° 3 mais les id_lang ne sont pas 1,2, et 3 (ici 1,2, et 4

)
En fait il faudrait bien que le compte des langues soit à 3 mais aussi que cela respect les id_lang existant d'une autre table qui s'appel lang

J'avais compris trois langues différentes et non en particulier les langues 1, 2 et 3.
Il suffit d'ajouter une restriction sur la langue, en sélectionnant (1, 2 et 3) et en tenant compte que l'on doit récupérer seulement trois lignes.
J'espère que vous n'avez pas deux fois la même langue pour un id_pei donné.

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
--------------
select t1.id_pei,
       t1.id_product
 
from        product_productextras       as t1
 
inner join  product_productextras_lang  as t2
        on  t2.id_pei  = t1.id_pei
       and  t2.notice <> ''
       and  t2.about  <> ''
 
where t2.id_lang in (1, 2, 3)
 
group by  t1.id_pei, t1.id_product
  having  count(distinct t2.id_lang) = 3
order by  t1.id_pei, t1.id_product
--------------
 
+--------+------------+
| id_pei | id_product |
+--------+------------+
|      1 |         25 |
|      2 |         12 |
+--------+------------+

@+

**BeRoots** · 01/11/2016, 22h24

Bonsoir Artemus24

Non, c'est parfait. Je vais voir si cela est une meilleur optimisation avec cette requête.
Un grand merci

Requête select sur plusieurs tuples [MySQL-5.6]

Requêtes MySQL

Discussions similaires

Partager

Partager