Discussion :

[mrboliboli]

Bonjour,
Je suis pas expert en SQL et je rencontre une grosse difficulté sur un problème qui me paraissait simple.
Pour faire le plus simple possible (en réalité il y a des tables intermédiaires mais cela n'est pas pertinent pour l'exemple),
dans une base de donnée (postgres) j'ai :
- d'un coté des utilisateurs qui possèdent une liste d'aptitudes
- d'un autre coté j'ai des taches qui nécessitent certaines aptitudes et inaptitudes (une liste d'aptitude et une liste d'inaptitudes).

Pour un utilisateur donné je souhaite récupérer toutes les taches compatibles avec ses aptitudes/inaptitudes (certaines taches requièrent que la personne soit inapte).

je suis arrivé à un truc comme ca:

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
select prerequis.id as prerequis_id,
			(select count(*) from prerequis_aptitudes where prerequis_aptitudes.id_prerequis=prerequis.id) as nb_apt, 
			(select count(*) from prerequis_inaptitudes where prerequis_inaptitudes.id_prerequis=prerequis.id) as nb_inapt, 
			(select count (*) from (
					select id_aptitude from prerequis_aptitudes where prerequis_aptitudes.id_prerequis=prerequis.id
					intersect 
					select aptitude.id from usdata
					join usdata_aptitudes on usdata.id = usdata_aptitudes.id_usdata
					join aptitude on usdata_aptitudes.id_aptitude = aptitude.id
					where usdata.user_id=759) as popo) as user_apt,
			(select count (*) from (
				select id_aptitude from prerequis_inaptitudes where prerequis_inaptitudes.id_prerequis=prerequis.id
				intersect 
				select aptitude.id from aptitude
				Where aptitude.id not in(select id_aptitude from usdata
				join usdata_aptitudes on usdata.id = usdata_aptitudes.id_usdata
				where usdata.user_id=759)) as poo
			) as user_inapt
from prerequis

ce qui me donne :


Avec ça je cree une vue(depuis une procedure stockée pour passer le userid en paramètre) puis un select avec un where pour comparer les count de valeurs nb_* / user_*.

Bref c'est une vrai usine à gaz et je pense que quelqu'un de plus péchu que moi aura surement une solution plus simple et plus élégante.
Si en plus je peux le transcrire en JPA cela serait merveilleux.

Merci beaucoup.

[escartefigue]

Bonjour,

Quelle différence y a-t-il entre une inaptitude et le fait de ne pas avoir une certaine aptitude ?
Il me semble que être inapte à la cuisine, c'est ne pas avoir la compétence cuisine.
Si l'on modélise deux notions distinctes, on risque les incohérences : rien n'interdira d'associer la compétence cuisine et l'incompétence cuisine à une même personne...

Du coup, de ce que je comprends, le modèle de données devrait être le suivant (MCD à gauche, MLD à droite) :

Pièce jointe 588414

à partir de là, pour répondre au besoin, il suffit de faire une jointure entre la table AQ des compétences acquises et la tables RQ des compétences requises qui ont en commun l'identifiant de l'aptitude AP_ident

[mrboliboli]

Bonjour,

C'est le modèle que j'avais initialement avant d'ajouter cette contrainte d'inaptitude.
La difference entre une inaptitude et le fait de ne pas avoir une certaine aptitude, au niveau de l'utilisateur aucune.
Au niveau de la tache par contre toutes les aptitudes ne sont pas évaluées.
Pour reprendre l'exemple, mettons que nous ayons 3 aptitudes: balai, cuisine, ps4.
Pour la tache "préparer le repas" j'ai besoin de trouver une personne qui ait la compétence cuisine, pas la compétence balai, la compétence ps4 n'est pas prise en compte pour cette tache.
Au final, c'est le caractère facultatif de certaines competences qui ne seront donc pas évaluées qui me pose problème.
c'est la raison qui m'a amené a créer les 2 listes pour évaluer les aptitudes/inaptitudes nécessaires à cette tache.

Merci

[escartefigue]

Avec le modèle proposé plus haut il n'y a aucun problème : faites la jointure entre AQ et RQ sur l'identifiant de d'aptitude et le tour est joué

[escartefigue]

Voici le script de création des tables selon le modèle proposé plus haut :

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
CREATE TABLE UT_utilisateur(
   UT_ident SERIAL,
   UT_nom VARCHAR(50) NOT NULL,
   PRIMARY KEY(UT_ident)
);
 
CREATE TABLE AP_aptitude(
   AP_ident SERIAL,
   AP_code CHAR(4) NOT NULL,
   AP_libelle VARCHAR(128) NOT NULL,
   PRIMARY KEY(AP_ident),
   UNIQUE(AP_code)
);
 
CREATE TABLE TA_tache(
   TA_ident SERIAL,
   TA_description VARCHAR(50) NOT NULL,
   PRIMARY KEY(TA_ident)
);
 
CREATE TABLE AQ_acquerir(
   UT_ident INTEGER,
   AP_ident INTEGER,
   AQ_date DATE NOT NULL,
   PRIMARY KEY(UT_ident, AP_ident),
   FOREIGN KEY(UT_ident) REFERENCES UT_utilisateur(UT_ident),
   FOREIGN KEY(AP_ident) REFERENCES AP_aptitude(AP_ident)
);
 
CREATE TABLE RQ_requerir(
   AP_ident INTEGER,
   TA_ident INTEGER,
   PRIMARY KEY(AP_ident, TA_ident),
   FOREIGN KEY(AP_ident) REFERENCES AP_aptitude(AP_ident),
   FOREIGN KEY(TA_ident) REFERENCES TA_tache(TA_ident)
);

Le script de création d'un petit jeu d'essais :

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
insert into UT_utilisateur(UT_nom)
       values ('Baucuse'), ('Fee du logis'), ('Bricolo')
;
insert into AP_aptitude (AP_code, AP_libelle)
       values ('CUIS', 'Cuisine')
            , ('MENA', 'Ménage')
            , ('BRIC', 'Bricolage')
;
insert into TA_tache(TA_description)
       values ('préparer un clafouti aux bananes')
            , ('réparer la boite aux lettres')
            , ('faire une pâte feuilletée')
            , ('repasser les chemises')
            , ('réparer la gazinière')
;            
insert into AQ_acquerir(UT_ident, AP_ident, AQ_date)
       values (1, 1, '1945-10-12')
            , (1, 2, '1945-10-12')
            , (1, 3, '1970-09-23')
            , (2, 2, '1983-05-22')
            , (3, 3, '1990-06-11')
;
insert into RQ_requerir(AP_ident, TA_ident)
       values (1, 1)
            , (3, 2)
            , (1, 3)
            , (2, 4)
;

liste des personnes capables pour chaque tache :

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
select TA.TA_description
     , AP.AP_libelle
     , UT.UT_nom
from TA_tache TA
inner join RQ_requerir RQ
   on RQ.TA_ident=TA.TA_ident
inner join AP_aptitude AP
   on AP.AP_ident=RQ.AP_ident
left join AQ_acquerir AQ
     inner join UT_utilisateur UT
        on UT.UT_ident = AQ.UT_ident
   on AQ.AP_ident = AP.AP_ident
where not exists
     (select 1
      from RQ_requerir R2
      where R2.TA_ident = RQ.TA_ident
        and R2.AP_ident<> RQ.AP_ident
        and not exists
           (select 1
            from AQ_acquerir Q2
            where Q2.AP_ident = R2.AP_ident
              and Q2.UT_ident = UT.UT_ident
           )
     )
;

Résultat :

Pièce jointe 588449

EDIT : j'ai légèrement modifié le jeu d'essai et la requête car je n'avais pas tenu compte du fait que certaines taches requièrent plusieurs compétences

[SQLpro]

La solution de la requête est une division relationnelle. À me lire :
https://sqlpro.developpez.com/cours/divrelationnelle/

A +

[escartefigue]

La solution de la requête est une division relationnelle. À me lire :
https://sqlpro.developpez.com/cours/divrelationnelle/

A +

C'est en effet la division relationnelle qui est utilisée dans ma requête corrélée tout en bas

[escartefigue]

Bonjour,

Quelle différence y a-t-il entre une inaptitude et le fait de ne pas avoir une certaine aptitude ?
Il me semble que être inapte à la cuisine, c'est ne pas avoir la compétence cuisine.
Si l'on modélise deux notions distinctes, on risque les incohérences : rien n'interdira d'associer la compétence cuisine et l'incompétence cuisine à une même personne...

Je m'auto corrige : une contrainte d'exclusion permettrait de le faire, mais c'est compliquer inutilement la solution

@mrboliboli : avez vous testé, sujet résolu ?

[mrboliboli]

Bonjour,
Je n'ai pas eu de temps à consacrer à cela dernièrement, désolé.

Je suis en train de regarder, mais j'avoue que ca pique un peu...
Il ne me reste plus qu'a étudier cela.

Merci beaucoup.