Discussion :

[Invité]

Bonjour,

Pour commencer je vais exposer la structure de mes tables. Dans mon problème, 4 tables sont concernées.

profil(id INT, nom VARCHAR, prenom VARCHAR)
travail(id INT, #profil_id)
travail_competence(#travail_id INT, #competence_id INT)
competence(id INT, nom VARCHAR)

Explication rapide :

Un profil possède 0 ou PLUSIEURS travail.
Un travail possède 1 et 1 SEUL profil.
Un travail possède 0 ou PLUSIEURS competence.
Une competence possède 0 ou PLUSIEURS travail.
NOTE : Un profil peut donc avoir (indirectement) plusieurs fois la même competence.

Schema :

(J'ai simplifié les tables, la modélisation est bonne même si ici le nombre de table semble inadapté)

Ce que je cherche à faire :

Je veux retrouver une liste de Profil avec en condition d'entrée une liste de Competence. Le but étant de trouver les Profil possédant cette liste de Competence (toute la liste : "AND").

Le problème :

Mon problème vient bien évidemment du faite qu'un Profil ne possède pas directement les Competence, le lien est fait par la table Travail et travail_competence.

Notes :

Mon projet est réalisé en PHP sous Symfony2 et ma requête va être construite avec Doctrine2 en DQL.

Voilà pour les explications.

Début de solution :

Je pense être un peu plus avancé qu'au début de la rédaction de ce message.
Pas sûr que ça marche mais je pense être sur la bonne voie :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
select profil_id, count(*) as nb
from(
        select profil.id as profil_id, profil.nom as profil_nom, competence.id as competence_id
            from profil, travail, competence, travail_competence
            where profil.id = travail.profil_id
            and travail.id = travail_competence.travail_id
            and competence.id = travail_competence.competence_id
    ) as profil_competence
where competence_id = 56 
or competence_id = 59
group by profil_id
having nb = 2 /* variable, en fonction du nombre de condition OR */
;

Sachant que les condition OR et le HAVING seraient générés en fonction de la liste de compétence d'entrée

Je pense que je ne suis pas loin de la solution, peut-être qu'un SELECT DISTINCT se montrera nécessaire vu qu'un profil peut avoir plusieurs fois la même compétence.

Je fais quelques tests.

[vmolines]

Bonjour,

Bon début ! Je pense que vous serez heureux de trouver la solution par vous meme en vous aidant de ce cours : http://sqlpro.developpez.com/cours/divrelationnelle/

Bonne lecture

[ericd69]

salut,

ça ne ferait pas simplement le job:

Code sql :

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

1
2
3
4
5
select p.nom
from travail t
inner join travail_competence tc on tc.travail_id=t.id
inner join competences c on c.id=tc.competence_id and c.nom in('nom1','nom2',...);
left join profil p on p.id=t.profil_id

[vmolines]

A priori non :

Le but étant de trouver les Profil possédant cette liste de Competence (toute la liste : "AND").

Votre requête trouverait les profils avec au moins une des compétences requises.

[ericd69]

ah oui j'avais zappé ça...

c'est vrai que ce que je donne fournit la liste des profil pour 1 compétence ou plus...

du coup, je pense en effet que faut faire l'équivalent de la division relationnelle...

on peut pas faire plus simple, je crois

[Invité]

Oui effectivement, il faut que le profil possède toute les compétence.

Et ma requête semble répondre à mes besoins, sauf que j'ai effectivement besoin du SELECT DISTINCT vu que je peux avoir plusieurs fois la même compétence pour un profil.

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
 
select profil_id
from 
    (
        select distinct profil.id as profil_id, competence.id as competence_id
            from profil, travail, competence, travail_competence
            where profil.id = travail.profil_id
            and travail.id = travail_competence.travail_id
            and competence.id = travail_competence.competence_id
    ) as profil_competence
where competence_id = 67
or competence_id = 68
group by profil_id
having count(*) = 2
;

Elle me renvoie bien ce que je veux.
Je ferai encore quelques tests demain. J'en profiterai aussi pour lire le cours.

Et plus qu'à implémenter avec doctrine, une autre histoire ça.

Merci à vous deux, je passerai en résolu si je ne trouve aucune erreur.

[CinePhil]

Puisque, d'après ta première requête, tu ne récupères que les identifiants des profils, pourquoi t'embêter, dans la sous-requête, à récupérer le nom et l'identifiant de la compétence ?

De plus, les jointures s'écrivent depuis 20 ans avec l'opérateur JOIN. Il serait temps de s'y mettre !

Reprenons...

Le but étant de trouver les Profil possédant cette liste de Competence (toute la liste : "AND").

Tu es sur la bonne piste avec les OR et le HAVING.

Fred_34 avait la bonne requête mais avec les jointures obsolètes et une jointure inutile. Puisque competence_id figure dans la table associative travail_competence, inutile de faire la jointure avec la table competence.

Ceci devrait fonctionner non ?

Code :

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

1
2
3
4
5
6
7
SELECT p.id AS profil_id
FROM profil p
INNER JOIN travail t ON t.profil_id = p.id
	INNER JOIN travail_competence tc ON tc.travail_id = t.id
WHERE tc.competence_id IN (56, 59)
GROUP BY p.id
HAVING COUNT(DISTINCT tc.competence_id) = 2 -- Nb d'éléments dans le IN

Si tu veux le nom du profil avec, ajoute le simplement dans le SELECT et dans le GROUP BY.

Au passage, remarque que l'utilisation d'alias et l'indentation rend plus lisible la requête.

Dernière chose : j'ose espérer que cette saloperie d'ORM Doctrine permet de soumettre au SGBD une requête en SQL pur ! Inutile de s'emmerder à traduire en dialecte doctrinien maintenant que tu as la bonne requête en SQL !

normalement, ton having doit être, comme le group by, sur une colonne du select, sous peine d'une jolie erreur.

Pas pour le HAVING qui porte sur les opérations de groupage et qui peuvent y figurer directement, sans qu'elles figurent dans le SELECT, comme je l'ai fait dans ma requête.

[Invité]

Merci pour toutes vos réponses, j'ai du mettre ce problème de côté pendant un temps, voilà pourquoi je n'ai pas répondu plus tôt.

Puisque, d'après ta première requête, tu ne récupères que les identifiants des profils, pourquoi t'embêter, dans la sous-requête, à récupérer le nom et l'identifiant de la compétence ?

De plus, les jointures s'écrivent depuis 20 ans avec l'opérateur JOIN. Il serait temps de s'y mettre !

C'était pour que cela soit plus lisible dans les tests j'affichais aussi les autres champs, j'ai oublié de les supprimer par la suite.

Pour les jointures, effectivement il faut que je m'y mette , je les avais utilisées avec doctrine mais pas dans mes tests en SQL pure.

Comme souvent j'ai surement faux quelque part, mais n'est-il pas possible de faire une seule requête ? : [...SQL]

Ceci devrait fonctionner non ? [...SQL]

Cela répond effectivement à mes besoins en bien plus optimisé/propre que ma requête, merci beaucoup.

Dernière chose : j'ose espérer que cette saloperie d'ORM Doctrine permet de soumettre au SGBD une requête en SQL pur ! Inutile de s'emmerder à traduire en dialecte doctrinien maintenant que tu as la bonne requête en SQL !

Ouch ! Bah je suis tout de même passé par ce dialecte, et il génère la bonne requête :p

Encore merci. Je passe en résolu.