Une requête compliquée

**renaud26** · 27/03/2012, 17h00

Bonjour à tous,

Je voudrais ajouter à ma boutique une fonction "ceux qui ont commandé votre produit ont aussi commandé...".

Je dispose d'une table articles_commandes :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
commande_id | produit_id
-------------------------
1                     125
1                     160
1                     220
2                     170
2                     182
3                     213
4                     160
4                      89
4                     101
5                      36

Si on suppose que le client a mis dans son panier le produit 160, je dois récupérer distinctement tous les ID "voisins" pour le même commande_id, dans cet exemple les produits de la commande 1 : 125, 220 et ceux de la commande 4 : 89, 101, 36.
Est-ce que c'est jouable en une requête sans passer par des tableaux PHP et si oui, quel est le type de syntaxe de la clause WHERE...ou la sous-requête ?
Merci de vos conseils.

**Benjamin Delespierre** · 27/03/2012, 18h02

En un mot: jointure.

Je te cache pas que la requête va être un peu complexe par contre.

**Fabllot** · 27/03/2012, 18h35

En une requête ça passe bien, et pas si "complexe" que cela...

Code :

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

1
2
3
4
5
6
 
SELECT ac1.product_id 
FROM articles_commandes ac1
JOIN articles_commandes ac2 USING(commande_id)
WHERE ac2.product_id = 160 AND commande_id <> '$my_cmd'
GROUP BY ac1.product_id

Où "$my_cmd" est l'id de la commande courante (pour éviter de comptabiliser les
éléments que tu viens d'acheter)
Quelque chose comme ça doit fonctionner...
Et cela peut même se coupler avec un petit "count" pour ne faire ressortir que les n articles les plus achetés...

**Benjamin Delespierre** · 27/03/2012, 18h39

Avec le jeu d'essai suivant:

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
mysql> create table products ( id int not null auto_increment, name varchar(45) not null, primary key (id)) engine=InnoDB;
Query OK, 0 rows affected (0.22 sec)
 
mysql> create table orders ( id int not null auto_increment, primary key (id)) engine=InnoDB;
Query OK, 0 rows affected (0.39 sec)
 
mysql> create table order_products ( order_id int not null, product_id int not null, primary key (order_id, product_id)) engine=InnoDB;
Query OK, 0 rows affected (0.09 sec)
 
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| order_products |
| orders         |
| products       |
+----------------+
3 rows in set (0.05 sec)
 
mysql> insert into products values (1,'produit 1'),(2, 'produit 2'),(3, 'produit 3'),(4, 'produit 4'),(5, 'produit 5');
Query OK, 5 rows affected (0.14 sec)
Records: 5  Duplicates: 0  Warnings: 0
 
mysql> insert into orders values (1),(2),(3);
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> insert into order_products values (1,1),(1,2),(2,2),(2,3),(3,1),(3,4);
Query OK, 6 rows affected (0.06 sec)
Records: 6  Duplicates: 0  Warnings: 0
 
mysql> select o.id as order_id, p.id as product_id, p.name as product_name from orders as o join order_products as op on (o.id=op.order_id) join products as p on (p.id=op.product_id) order by o.id,p.id;
+----------+------------+--------------+
| order_id | product_id | product_name |
+----------+------------+--------------+
|        1 |          1 | produit 1    |
|        1 |          2 | produit 2    |
|        2 |          2 | produit 2    |
|        2 |          3 | produit 3    |
|        3 |          1 | produit 1    |
|        3 |          4 | produit 4    |
+----------+------------+--------------+
6 rows in set (0.00 sec)

on a 5 produits et 3 commandes:

commande 1 produits [1,2]
commande 2 produits [2,3]
commande 3 produits [1,4]

On va chercher les les articles qui ont également été commandés lors de la commande de l'article 2.

Donc ça se traduit par: "trouver les id des produits dont les les commandes contiennent le produit 2 et qui ne correspondent pas à l'article 2 et les trier par occurence"

Soit (avec une sous requête):

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
mysql> select
    ->  o.id as order_id,
    ->  p.id as product_id,
    ->  p.name as product_name
    -> from
    ->  orders as o
    -> join
    ->  order_products as op
    ->  on (o.id=op.order_id)
    -> join
    ->  products as p
    ->  on (p.id=op.product_id)
    -> where
    ->  op.order_id IN (SELECT o.id FROM orders AS o JOIN order_products AS op ON (o.id=op.order_id) WHERE op.product_id=2)
    ->  and
    ->  op.product_id != 2
    -> group by
    ->  op.product_id
    -> order by
    ->  count(op.product_id);
+----------+------------+--------------+
| order_id | product_id | product_name |
+----------+------------+--------------+
|        1 |          1 | produit 1    |
|        2 |          3 | produit 3    |
+----------+------------+--------------+
2 rows in set (0.00 sec)

C'est bien ce qu'on voulait. Maintenant ajoutons des données:

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
mysql> insert into orders values (4);
Query OK, 1 row affected (0.08 sec)
 
mysql> insert into order_products values (4,1),(4,3),(4,4);
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> select o.id as order_id, p.id as product_id, p.name as product_name from orders as o join order_products as op on (o.id=op.order_id) join products as p on (p.id=op.product_id) order by o.id,p.id;
+----------+------------+--------------+
| order_id | product_id | product_name |
+----------+------------+--------------+
|        1 |          1 | produit 1    |
|        1 |          2 | produit 2    |
|        2 |          2 | produit 2    |
|        2 |          3 | produit 3    |
|        3 |          1 | produit 1    |
|        3 |          4 | produit 4    |
|        4 |          1 | produit 1    |
|        4 |          3 | produit 3    |
|        4 |          4 | produit 4    |
+----------+------------+--------------+
9 rows in set (0.00 sec)

La commande 4 ne contiens pas le produit 2.

Donc:

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
mysql> select
    ->  o.id as order_id,
    ->  p.id as product_id,
    ->  p.name as product_name
    -> from
    ->  orders as o
    -> join
    ->  order_products as op
    ->  on (o.id=op.order_id)
    -> join
    ->  products as p
    ->  on (p.id=op.product_id)
    -> where
    ->  op.order_id IN (SELECT o.id FROM orders AS o JOIN order_products AS op ON (o.id=op.order_id) WHERE op.product_id=2)
    ->  and
    ->  op.product_id != 2
    -> group by
    ->  op.product_id
    -> order by
    ->  count(op.product_id);
+----------+------------+--------------+
| order_id | product_id | product_name |
+----------+------------+--------------+
|        1 |          1 | produit 1    |
|        2 |          3 | produit 3    |
+----------+------------+--------------+
2 rows in set (0.00 sec)

c'est ok d'après moi, quelqu'un peut confirmer ?

**Fabllot** · 27/03/2012, 18h53

Envoyé par Benjamin Delespierre

c'est ok d'après moi, quelqu'un peut confirmer ?

Euh... Je ne comprends pas bien ce que tu fais, et j'ai l'impression que tu ne réponds pas vraiment à la question.
En tout cas, je préfère ma solution... (enfin faut vérifier qu'elle marche bien mais je pense que oui)

**renaud26** · 27/03/2012, 19h53

Bonsoir à vous et désolé de ma réponse en retard : j'ai de gros soucis de connexion. Merci de vos réponses.
Ce qu'il y a c'est que l'idée est de proposer à l'acheteur des produits qui ont été achetés par d'autres - ceux qui ont acheté ce qu'il s'apprête à acheter.
Et cette notion est importante : à ce stade, il n'a pas encore payé, et je n'ai pas d'id de commande ! Il sera généré par auto-incrément de la table commandes, et la liste de ses produits sera insérée à ce moment-là dans la table articles_commandes de mon exemple.

C'est donc avec les produit_id qu'il nous faut travailler...

**Benjamin Delespierre** · 27/03/2012, 22h21

La solution que je te propose n'utilise que le product_id.

**ericd69** · 27/03/2012, 22h27

un truc genre:

Code sql :

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

1
2
3
4
5
6
7
8
select c3.produit_id as 'produit',count(c3.produit_id) as 'fréquence'
from panier c1
inner join articles_commandes c2 on c1.produit_id<>c2.produit_id
inner join articles_commandes c3 on c1.produit_id<>c3.produit_id and c2.commande_id=c3.commande_id
group by c2.produit_id
where c1.id_user=1
order by 'produit','fréquence'
limit 10

ton truc est infaisable si tu n'as pas une table panier(id,id_user,produit_id) où sont enregistrés les produits...

sinon faut faire une procédure stockée qui va:

créer une table temporaire panier(produit_id)
la remplir avec les produit_id du panier
appliquer la requête un peu modifié (sans le where), la valeur du limit dépend du nombre de produit à afficher...

**renaud26** · 27/03/2012, 22h53

A Benjamin : je vais étudier ta requête en détail et tenter de l'appliquer, même si j'avoue que formulée telle quelle, c'est un peu compliqué pour moi...

A ericd69 : Cette table existe : c'est la table articles_commandes que j'ai schématisée ici et réduite à 2 champs. Mais il y a bien un id (clé primaire), le user_id (client), le produit_id et le produit_nom. Ta requête est intéressante mais je ne vois pas trop le rôle du user_id...

Bonne soirée à tous.

**ericd69** · 27/03/2012, 23h03

à discriminé le panier de l'utilisateur sur tu as une table qui rassemble tous les paniers...

j'ai oublié un inner join dans ma requête je la rectifie

**Fabllot** · 28/03/2012, 11h03

Envoyé par ericd69

Code sql :

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

1
2
3
4
5
6
7
8
select c3.produit_id as 'produit',count(c3.produit_id) as 'fréquence'
from panier c1
inner join articles_commandes c2 on c1.produit_id<>c2.produit_id
inner join articles_commandes c3 on c1.produit_id<>c3.produit_id and c2.commande_id=c3.commande_id
group by c2.produit_id
where c1.id_user=1
order by 'produit','fréquence'
limit 10

Effectivement, je ne prenais pas en compte les produits déjà dans le panier avec ma requête.
Par contre j'aurais plutôt mis un GROUP BY sur c3.produit_id, puisque c'est les c3.produit_id que tu retournes et que tu fais ton count dessus... Ceci dit je ne sais pas s'il y a au final une différence.

Et conseil, si tu tries les résultats, fais plutôt

Code :

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

ORDER BY 'fréquence' DESC, 'produit' ASC

le résultat sera plus pertinent

**renaud26** · 28/03/2012, 14h19

Merci à vous !

J'ai dû cependant mal m'expliquer car dans cette requête :

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT c3.produit_id AS 'produit',count(c3.produit_id) AS 'fréquence'
FROM panier c1
INNER JOIN articles_commandes c2 ON c1.produit_id<>c2.produit_id
INNER JOIN articles_commandes c3 ON c1.produit_id<>c3.produit_id AND c2.commande_id=c3.commande_id
GROUP BY c2.produit_id
WHERE c1.id_user=1
ORDER BY 'produit','fréquence'
LIMIT 10

"panier " et "articles_commandes" sont 2 tables distinctes alors que la table "panier" et la table "articles_commandes" sont une seule et même table !

Il y une table commandes (je simplifie) :

id | user_id | date | total
--------------------------------
1 25 2012-03-28 255.03

Et une table articles_commandes :

commande_id | produit_id | produit_nom | qte
---------------------------------------------
1 140 toto 1
1 255 tata 2
1 465 tutu 1

Donc si un nouveau user se pointe et met le produit 255 dans son panier, je veux pouvoir lui dire juste avant son paiement : "ceux qui ont déjà commandé le produit 255 ont aussi commandé le produit 140 et le produit 465".

Avec les exemples postés ici, j'ai fait :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
//construction clause IN : il peut y avoir plusieurs produits dans le panier.
$in = "(";
foreach($tableau_panier as $id_prod){
	$in .= $id_prod.",";
}
$in = substr($in, 0 , -1);
$in .= ")";
 
$sql = "SELECT DISTINCT c1.produit_id AS 'produit'
FROM articles_commandes c1
INNER JOIN articles_commandes c2 ON c1.produit_id <> c2.produit_id
WHERE c2.produit_id IN ".$in." LIMIT 6"

Mais si ça sort bien 6 produits différents de celui du panier en cours, ils n'ont rien à voir au niveau de l'association avec celui-ci dans des commandes passées. Donc ça colle pas.

**Fabllot** · 28/03/2012, 14h27

Dans ce cas, il me semble que cela doit fonctionner :

Envoyé par Fabllot

Code :

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

1
2
3
4
5
SELECT ac1.product_id 
FROM articles_commandes ac1
JOIN articles_commandes ac2 USING(commande_id)
WHERE ac2.product_id = 160 AND commande_id <> '$my_cmd'
GROUP BY ac1.product_id

Et a priori sans la commande puisqu'elle n'existe et avec quelques ajouts :

Code :

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

1
2
3
4
5
6
7
SELECT ac1.product_id 
FROM articles_commandes ac1
JOIN articles_commandes ac2 USING(commande_id)
WHERE ac2.product_id = '$my_product_id'
GROUP BY ac1.product_id
ORDER BY COUNT(*) DESC
LIMIT 5

**renaud26** · 28/03/2012, 14h54

Merci Fabllot (et tous ceux qui ont contribué à ce post), je pe,se que c'est bon, vérifications faites. J'ai ajouté un NOT IN pour que le(s) produit(s) du panier en cours ne soit pas re proposé(s) et :

Code :

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

1
2
3
4
5
6
7
8
 
SELECT ac1.produit_id, ac1.commande_id
FROM articles_commandes ac1
JOIN articles_commandes ac2
USING ( commande_id )
WHERE ac2.produit_id
IN ( 1241 )
AND ac1.produit_id NOT IN ( 1241 ) LIMIT 6

Je vais faire d'autres tests, mais en attendant, je passe en résolu.
Encore merci à tous les participants.

Une requête compliquée

Requêtes MySQL

Discussions similaires

Partager

Partager