Bonjour,
 
Je vous expose mon projet et mon problème:
 
Projet:
J'invite des gens à manger, certains n'aiment vraiment pas certains aliments et il faut donc que je prenne ça en compte. De plus, il faut que j'évite de faire 2 fois la même chose aux invités (même si c'est bon ).
Je me suis donc fait un petit site web me permettant d'entrer:
- Mes recettes avec des mots-clés qui serviront pour les goûts des personnes
- Les invités avec quelques infos, + des mots-clés décrivant ce qu'ils n'aiment pas (ananas, etc...)
 
Pour gérer ça j'ai plusieurs tables:
- Recettes
CREATE TABLE `recettes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `titre` varchar(64) NOT NULL,
  `id_type` int(11) NOT NULL,
  `nbPersonnes` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `titre` (`titre`,`id_type`),
  FULLTEXT KEY `titre_2` (`titre`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1
 
- Type (les types de recette: entrée, plats, desserts, etc...)
CREATE TABLE `types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(32) NOT NULL,
  `ordre` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ordre` (`ordre`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1
 
- personnes_gouts (qui fait la liaison entre les ID des personnes et les gouts. Si "aime=0" alors c'est quelque chose que la personne n'aime pas)
CREATE TABLE `personnes_gouts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_personne` int(11) NOT NULL,
  `aime` tinyint(4) NOT NULL,
  `gout` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_personne` (`id_personne`,`aime`,`gout`),
  KEY `gout` (`gout`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1
 
- personnes_recettes (qui fait la relation entre les ID des personnes et les ID des recettes qu'elles sont déjà eu)
CREATE TABLE `personnes_recettes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_personne` int(11) NOT NULL,
  `id_recette` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_personne` (`id_personne`,`id_recette`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1
 
- mots_cles (qui fait la relation entre des ID des recettes et donc les mots-clés qui vont avec)
CREATE TABLE `mots_cles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_recette` int(11) NOT NULL,
  `mot_cle` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_recette` (`id_recette`,`mot_cle`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1  
 
 
 
PROBLEME:
Voici ce que je souhaite obtenir:
- Je récupère grâce à un formulaire les ID des personnes qui vont être invitées, et je précise un ID de type (par exemple l'ID 1 qui est le type "entrée" )
- Je voudrais une requête qui me sort un menu que je n'ai pas encore fait aux invités et donc aucun mot-clés desdits recettes ne soient dans les goûts à éviter pour ces mêmes personnes
 
Cette requête sera donc à créer dynamiquement suivant le nombre de personnes cochées, ce que je ferai en PHP, pas de souci là dessus.
 
Par contre voilà, la requête "de base" me pose souci avec après moult essais je n'y parviens pas... Alors qu'il s'agit du principal intérêt du site en question.
 
J'espère avoir été clair dans mon explication... A vot' bon cœur m'sieur dames!

Je n'ai pas compris où tu étais bloqué , tu peux montrer la requête qui te pose problème ?

Par exemple j'ai pour le moment fait cette requête:
 
SELECT DISTINCT r.id, r.titre, r.nbPersonnes FROM recettes r, mots_cles m WHERE r.id_type=22 AND m.id_recette=r.id AND m.mot_cle NOT IN (SELECT DISTINCT gout FROM personnes_gouts WHERE (id_personne=17 || id_personne=18) AND aime=0)
 
Pour un ID de type=22 et pour 2 personnes d'ID 17 et 18. Ca ne fonctionne évidemment pas.
Je continue à chercher de toute façon mais j'ai "quelques" lacunes en SQL (que je tente de corriger!).

Un conseil : poste ton schéma sous forme visuelle (un MPD suffira), ça sera plus motivant pour essayer de t'aider .
Et les NOT IN et sous requêtes imbriquées c'est mal !

Au passage, tu peux avantageusement remplacer (id_personne=17 || id_personne=18) par id_personne In (17, 18)

Ok pour les NOT IN, par contre, les sous-requêtes, ça dépend : placée dans une Where avec un IN, c'est clair, c'est pas bon pour les perfs. Par contre, une sous-requête dans un FROM, ça peut réduire la taille des les jointures à faire

Là c'était dans le where .

une requête ne sera jamais satisfaisante. il faut le faire via une procédure stockée

Et tu mets quoi dans ta procédure stockée à part une requête

Pourquoi donc Et suffisante à quoi ?

Avec CTE (donc pas de MySQL) :

WITH invites AS
(
    SELECT *
    FROM personne p
    WHERE p.id IN (17, 18)
)
SELECT *
FROM recettes r
WHERE r.id NOT IN (
    SELECT c.id_recette
    FROM invites i
    INNER JOIN personnes_gouts g ON g.id_personne = i.id AND g.aime = 'N'
    INNER JOIN mots_cles_recettes c ON c.mot_cle = g.gout
    union ALL
    SELECT p.id_recette
    FROM invites i
    INNER JOIN personnes_recettes p ON p.id_personne = i.id    
)
AND r.nbPersonnes = (SELECT count(*) FROM invites)
AND r.id_type = 1;

Et donc sans CTE, donc MySQL :

SELECT *
FROM recettes r
WHERE r.id NOT IN (
    SELECT c.id_recette
    FROM personnes p
    INNER JOIN personnes_gouts g ON g.id_personne = p.id AND g.aime = 'N'
    INNER JOIN mots_cles_recettes c ON c.mot_cle = g.gout
        WHERE p.id IN (17, 18)
    union ALL
    SELECT p.id_recette
    FROM personnes_recettes p
        WHERE p.id_personne IN (17, 18)
)
AND r.nbPersonnes = (SELECT count(*) FROM personnes p WHERE p.id IN (17, 18))
AND r.id_type = 1;

Je me suis permis de :
- créer une table "mots_cles_recettes" qui fait le lien entre "mots_cles" et "recette"
- changer la signification de "mots_cles" qui se contente d'être les ingrédients potentiellement non désirés (curry, concombre, ananas, etc.)
- plus quelques autres adaptation

Code complet de test sous SQL Server pour mieux comprendre :

-- Jeu de données
WITH recettes AS
(
    SELECT 1 id, 'Poulet au curry' titre, 1 id_type, 2 nbPersonnes
    union ALL
    SELECT 2, 'Lasagnes', 1, 2
    union ALL
    SELECT 3, 'Steak frites', 1, 2
    union ALL
    SELECT 4, 'Tarte au concombre', 2, 2
    union ALL
    SELECT 5, 'Salade de fruits', 2, 8
    union ALL
    SELECT 6, 'Flan au chocolat', 2, 2
),
types AS
(
    SELECT 1 id, 'Plat principal' type, 2 ordre
    union ALL
    SELECT 2, 'Dessert', 3
),
personnes_gouts AS
(
    SELECT 1 id, 17 id_personne, 'O' aime, 1 gout
    union ALL
    SELECT 2, 18, 'N', 1
    union ALL
    SELECT 3, 17, 'O', 2
    union ALL
    SELECT 4, 18, 'O', 2
    union ALL
    SELECT 5, 17, 'O', 3
    union ALL
    SELECT 6, 18, 'O', 3
    union ALL
    SELECT 7, 17, 'N', 4
    union ALL
    SELECT 8, 18, 'N', 4
    union ALL
    SELECT 9, 17, 'O', 5
    union ALL
    SELECT 10, 18, 'O', 5
),
personnes_recettes AS
(
    SELECT 1 id, 17 id_personne, 3 id_recette
),
mots_cles AS
(
    SELECT 1 id, 'curry' mot_cle
    union ALL
    SELECT 2, 'poulet'
    union ALL
    SELECT 3, 'pâtes'
    union ALL
    SELECT 4, 'concombre'
    union ALL
    SELECT 5, 'ananas'
    union ALL
    SELECT 6, 'steak'
    union ALL
    SELECT 7, 'frittes'
),
mots_cles_recettes AS
(
    SELECT 1 id, 1 id_recette, 1 mot_cle
    union ALL
    SELECT 1, 1, 2
    union ALL
    SELECT 2, 2, 3
    union ALL
    SELECT 3, 4, 4
    union ALL
    SELECT 4, 6, 5
    union ALL
    SELECT 5, 3, 6
    union ALL
    SELECT 6, 3, 7
),
personne AS
(
    SELECT 17 id, 'toto' nom
    union ALL
    SELECT 18, 'titi'
),
-- jeu de données
-- la requête commence ici
invites AS
(
    SELECT *
    FROM personne p
    WHERE p.id IN (17, 18)
)
SELECT *
FROM recettes r
WHERE r.id NOT IN (
    SELECT c.id_recette
    FROM invites i
    INNER JOIN personnes_gouts g ON g.id_personne = i.id AND g.aime = 'N'
    INNER JOIN mots_cles_recettes c ON c.mot_cle = g.gout
    union ALL
    SELECT p.id_recette
    FROM invites i
    INNER JOIN personnes_recettes p ON p.id_personne = i.id    
)
AND r.nbPersonnes = (SELECT count(*) FROM invites)
AND r.id_type = 1;

PS :
- Pas de "dinstinct", ça sert à rien (sinon c'est que la requête est fausse) et c'est lent.
- IN sur une PK n'a rien de lent, ça utilise sans problème la clé primaire.
- Les jointures s'écrivent avec le mot clé "JOIN" depuis maintenant 22 ans.

Salut!
 
Je tente le debut, avec IN / NOT IN:

Et sans IN/NOT IN:

Et sinon je vois pas trop en quoi ca necessite une procedure stockee.

MagicBuzz, ça veut dire quoi CTE

Bon alors déjà merci à tous pour votre aides et proposition, je vais voir pour tester et continuer ça demain, pas possible de ce soir.
Je ne pensais pas avoir autant de réponses donc ça fait super plaisir déjà, et ça le sera encore plus quand j'aurai vu que ça fonctionne.
 
Je vous tiens au jus!

CTE > Common Table Expression
 
C'est un élément de la norme SQL1999.
 
Cela permet de faire des requêtes intermédiaires, qui peuvent être liées entre elles.
 
L'utilisation la plus courante :
- Comme dans mon cas, la création d'un jeu de test, plutôt que de passer par des objets physiques dans la base
- Récursivité
- Refactorisation de sous-select utilisés à plusieurs endroits d'une même requête (comme ça il ne sont évalués qu'une seul fois)
 
MySQL ne supporte évidement pas les CTE.

Je ne connaissais pas. Merci pour l'info

tu n'es jamais assuré d'avoir un résultat à chaque requête.
la meilleure chose c'est de programmer ça en procédure ou dans le code en exécutant chaque requête une par une

Si la requête ramène rien, c'est qu'il n'y a aucun plat à proposer (soit il n'y a que des plats qui ne plaisent pas aux invités, soit les invités ont déjà mangés tous les plats qu'ils aimaient).
 
Dans ce cas, le programme doit prévoir une autre requête pour trouver une recette selon d'autres critères.
 
C'est comme ça qu'il faut faire.
 
Pas faire une procédure qui va faire 25 requêtes (inutile, lourd et difficile à debugger) ni au programme de lancer 25 requêtes avec des curseurs imbriqués ouverts dans tous les sens, ce qui est le meilleur moyen de mettre à genoux le SGBD et l'application cliente.
 
Au contraire, quand on peut répondre à une problématique, aussi complexe soit-être avec une unique requête SQL, aussi complexe soit-elle, il faut impérativement utiliser cette solution.
 
Les procédures stockées et autres algos complexes dans le code client doivent être réservés pour des traitements séquentiels non ensemblistes, pour lesquels le SQL n'est pas correctement armé. Pour le reste, SQL, SQL, SQL, rien d'autre.

je doute que ça soit ce qu'il recherche.
difficile de faire une seule requête qui puisse renvoyer ce qu'il faut
 
et pas d'accord sur ce que tu dis. faut aller au plus efficace. même si tu fais 25 requêtes. ça peut être bien plus efficace qu'une énorme usine à gaz dans une requête.

Autant, je suis d'accord avec Magicbuzz sur le fait de ne pas utiliser une procédure stockée autant je rejoins ddr555 sur le fait que dans certains cas, plusieurs requêtes SQL simples peuvent être plus rapides à exécuter qu'une grosse requête SQL complexe.
 
Mais ça dépend pas mal du SGBD, de comment il a été configuré/tuné, de la structure de la BD concernée, des index... Bref, c'est du cas par cas.

lasnoufle> J'ai essayé tes requêtes qui fonctionnent bien concernant les goûts des personnes, mais qui ne regardent pas les recettes déjà faites.
J'avoue avoir du mal avec les jointures.....
 
MagicBuzz> Je vais tester ta solution mais le fait de devoir modifier un peut la structure m'embête un peu car j'ai déjà fait plusieurs choses.
 
Quant au fait d'avoir au moins un résultat pour chaque requête ça ne m'était pas venu à l'idée, pour le moment ma priorité est déjà de faire le "filtre" de base.

Perso je suis beaucoup plus d'accord avec Magicbuzz. Ce qui est demande ici est largement faisable avec une requete et meme une pas vraiment compliquee (il n'y a qu'a voir les solutions qu'il propose, il faut peut etre les affiner en fonction des retours de Furaxx mais elles sont pas vraiment compliquees).
 
Quant a requete vs code, c'est exactement ce que Magicbuzz dit: ca depend du type de traitement.
 
Maintenant mon experience perso c'est qu'il n'y a pas grand-monde qui sait faire des requetes bien ecrites (comprendre performantes, et j'inclus la declaration d'indexs adequats la-dedans), et pour ceux-la, faire le traitement en code pardonne beaucoup plus.
Mais reste que pour de l'ensembliste, quand tu sais faire (je m'inclus pas forcement dedans), SQL > all

Je viens de "compléter" ta proposition de requête lasnoufle, ça m'a l'air pas mal comme résultat:
 

Je viens de faire plusieurs tests et les résultats obtenus semblent bons.
 
Après, pour gérer le fait où cette requête ne retourne rien, il faudrait peut-être proposer par exemple les 2-3 recettes les plus "vieilles" par exemple, sans se préoccuper de celles déjà réaliser donc.
Ca, rien de compliqué à faire du coup!

le plus gros soucis c'est quand y'a pas de résultat. les jointures externes sont faites pour ça, mais dans ce cas y'a pas d'assurance d'avoir un résultat quelquepart

Bon, les NOT IN ca m’ennuie
Donc j'ai essayé de trouver une query (juste pour le fun) qui n'en utilise pas et au passage qui gère les vielles recettes si aucune ne convient et au final ça donne quelque chose de pas trop compliqué:

SELECT TOP 3 a.ID, a.titre, MAX(d.DateMangée) DernièreFoisMangée
FROM recettes a
    LEFT JOIN mots_cles b ON b.id_recette = a.ID
    LEFT JOIN personnes_gouts c ON c.aime = 0 AND c.id_personne IN (1, 5) AND c.gout = b.mot_cle
    LEFT JOIN personnes_recettes d ON d.id_personne IN (1, 5) AND d.id_recette = a.ID
WHERE a.id_type = 2
GROUP BY a.ID, a.titre
HAVING MAX(c.ID) IS NULL
ORDER BY Max(d.DateMangée) ASC

 
Evidemment pour gérer les vielles recettes j'ai ajouté un champ a personnes_recettes (DateMangée) pour savoir quand ça a été mangé.
C'est facultatif mais ça me semble être une bonne donnée à avoir.
 
Ya moyen d'encore ajouter des choses facilement, par exemple si il n'y a aucun plats sans ingrédient que les personnes n'aiment pas il est possible de remonter les plats avec le moins de "mauvais" ingrédient en changeant juste le Having MAX(c.ID) is null.

J'ai pas vraiment d'expérience avec MySQL, donc je veux bien croire votre phobie des IN/NOT IN (après tout, avec MySQL, on peut s'attendre à tout).

En revanche, IN/NOT IN ne pose aucun problème sur des "vrais" SGBD que sont SQL Server ou Oracle. Et au contraire, ils sont souvent bien plus rapide que les jointures externes, pour la simple et bonne raisons qu'ils permettent de filtrer des sous-ensemble avant de se lancer dans la lecture fastidieuse des autres tables.

Oliiii> Merci, je vais tester ça aussi alors!
Je pensais bien ajouter un champs pour gérer le "date mangé" oui.
Je verrai ce que ça donne au niveau temps d'exécution.

mouais, ça dépend de la taille des tables pour les IN sous Oracle.

Je dirais que ça dépend des tables concernées et du contenu du IN (si on lui met un grand nb de valeurs dedans)...

Dans l'absolu ca ne change rien, en général l'optimisateur va pondre le même plan avec ou sans IN.
 
C'est juste que d’expérience la majorité des query que je vois avec IN/NOT IN sont mauvaise alors que celle écrites avec des JOIN sont meilleur (pour SQL Server) probablement du au fait que c'est plus difficile d’écrire un bonne query avec des JOIN donc il faut plus d’expérience et donc on tombe moins dans le panneau.
 
Évidement il y a des exceptions partout