Petit défi SQL [Résolu]

[davidbouv]

Salut,

Après des heures de prise de tête sur une requête je vous propose de m'éclairer sur cette dernière.
J'ai des petites lacune en SQL donc soyez sympas si ça vous parait simple

j'ai 3 tables :

Code :

1
2
3
objet {id, nom}
objet_identite {id, objet_id, identite_id, lecture, ecriture, modification}
identite {id, nom}

J'aimerais avoir dans mon résultat de requête un tableau contenant :
objet.id, objet.nom, objet_identite.lecture, objet_identite.ecriture, objet_identite.modification

La petite subtilité c'est que ma table objet_identite est null de base et se remplit en fonction des droits activés.

Pour exemple :
j'ai 3 objets (id = 1, objet = toto; id = 2, objet = titi; id = 3, objet = tutu)
et 3 identités (id = 1, nom = admin; id = 2, nom = compta; id = 3, nom = secret)
et j'ai activé tous les droits pour l’identité admin sur l'objet titi, j'ai donc dans la table objet_identite (id = 1, objet_id = 2, identite_id = 1, lecture = true, ecriture = true, suppression = true)

je voudrais en affichage si je suis sur l’identité admin :
LIGNE 1 :
objet.id = 1, objet.nom = toto, objet_identite.lecture = false, objet_identite.ecriture = false, objet_identite.modification = false
LIGNE 2 :
objet.id = 2, objet.nom = titi, objet_identite.lecture = true, objet_identite.ecriture = true, objet_identite.modification = true
LIGNE 3 :
objet.id = 3, objet.nom = tutu, objet_identite.lecture = false, objet_identite.ecriture = false, objet_identite.modification = false

et sur l’identité compta :
LIGNE 1 :
objet.id = 1, objet.nom = toto, objet_identite.lecture = false, objet_identite.ecriture = false, objet_identite.modification = false
LIGNE 2 :
objet.id = 2, objet.nom = titi, objet_identite.lecture = false, objet_identite.ecriture = false, objet_identite.modification = false
LIGNE 3 :
objet.id = 3, objet.nom = tutu, objet_identite.lecture = false, objet_identite.ecriture = false, objet_identite.modification = false

Alors ma requête actuellement ressemble à ça :

Code :

1
2
3
4
5
SELECT o.id, o.nom, oi.lecture, oi.creation, oi.modification, oi.suppression
            FROM objet o
            LEFT JOIN objet_identite oi ON oi.objet_id = o.id
            WHERE oi.identite_id = $identiteId (variable selon l'identite choisi)
            OR oi.identite_id IS NULL

Ça fonctionne quand je suis en identite admin j'ai bien ce que je veux en revanche quand je suis en compta ou secret ma ligne titi n'est plus là (logique car maintenant elle existe).
J'ai essayé avec un différent de (<>) mais il compte pas les null du coup je l'ai dans l'os...

En espérant que ce soit clair j’attends vos réponse

[tatayo]

Bonjour,
Est-ce que ceci fonctionne:

Code SQL :

1
2
3
4
 
SELECT o.id, o.nom, ifnull(oi.lecture,false), ifnull(oi.creation,false), ifnull(oi.modification,false), ifnull(oi.suppression,false)
FROM objet o
LEFT OUTER JOIN objet_identite oi ON oi.objet_id = o.id AND oi.identite_id = $identiteId (variable selon l'identite choisi)

Tatayo.

[davidbouv]

Merci tatayo pour ton intérêt envers ce post mais malheureusement ta requête ne fonctionne pas non plus j'ai comme résultat mes 3 objets comme il faut avec les role compta et secret mais quand je suis en admin il me met des true sur le numéro de l'objet ou l'on choisi le role.
Donc dans ce cas là ça fonctionnerai mais je l'ai fait sur ma table de 160 objet avec l'objet numero 27 en true true true et l'identite numero 2 mais il m'affiche l'objet numero 2 true true true.

J'ai l'impression de pas être très clair quand j'explique désolé

[tatayo]

Envoyé par davidbouv

J'ai l'impression de pas être très clair quand j'explique désolé

Disons que j'ai la vague impression de ne pas avoir tout compris...
Est-ce que tu peux être un peu plus explicite, en nous donnant pour chaque cas les paramètres utilisés, le résultat attendu et le resultat récupéré ?

D'après ton jeu de test, la requête suivante

Code SQL :

1
2
3
4
 
SELECT o.id, o.nom, ifnull(oi.lecture,false), ifnull(oi.creation,false), ifnull(oi.modification,false), ifnull(oi.suppression,false)
FROM objet o
LEFT OUTER JOIN objet_identite oi ON oi.objet_id = o.id AND oi.identite_id = 1

Doit te renvoyer ceci:
LIGNE 1 :
objet.id = 1, objet.nom = toto, objet_identite.lecture = false, objet_identite.ecriture = false, objet_identite.modification = false
LIGNE 2 :
objet.id = 2, objet.nom = titi, objet_identite.lecture = true, objet_identite.ecriture = true, objet_identite.modification = true
LIGNE 3 :
objet.id = 3, objet.nom = tutu, objet_identite.lecture = false, objet_identite.ecriture = false, objet_identite.modification = false

Est-ce le cas ?

tatayo.

P.S. j'ai pris IfNull(), mais selon la base il faut utiliser NVL, Coalesce..

[davidbouv]

En fait ce n'est pas ce que je dois récupérer je vais essayer d'être plus clair :

En gros j'ai différentes identité pour chaque utilisateur donc comme dans mon exemple une 1 : ADMIN une 2 : COMPTA et une 3 : SECRET

je prends pour exemple ta requête :

Code :

1
2
3
SELECT o.id, o.nom, ifnull(oi.lecture,false), ifnull(oi.creation,false), ifnull(oi.modification,false), ifnull(oi.suppression,false)
FROM objet o
LEFT OUTER JOIN objet_identite oi ON oi.objet_id = o.id AND oi.identite_id = 1

Donc dans cette requête le oi.identite_id = 1 veut dire que l'on va faire des modifications de droit sur l'utilisateur qui a une identité ADMIN il faut donc que dans mon tableau j'aie la liste de tous les tuples dans la table objet et les droits rattachés à cet objet pour l'identité ADMIN.
La table objet_identite se construit au fur et à mesure que l'on met des droits en true sinon avant tous les droits n'existent pas.
Donc pour mon exemple il faudrait que cette requête me retourne (par rapport à l'exemple que j'ai donné au début :

LIGNE 1 :
objet.id = 1, objet.nom = toto, objet_identite.lecture = false, objet_identite.ecriture = false, objet_identite.modification = false
LIGNE 2 :
objet.id = 2, objet.nom = titi, objet_identite.lecture = true, objet_identite.ecriture = true, objet_identite.modification = true
LIGNE 3 :
objet.id = 3, objet.nom = tutu, objet_identite.lecture = false, objet_identite.ecriture = false, objet_identite.modification = false

en revanche si je passe sur l'identité COMPTA :

Code :

1
2
3
SELECT o.id, o.nom, ifnull(oi.lecture,false), ifnull(oi.creation,false), ifnull(oi.modification,false), ifnull(oi.suppression,false)
FROM objet o
LEFT OUTER JOIN objet_identite oi ON oi.objet_id = o.id AND oi.identite_id = 2

ça doit me retourner :

LIGNE 1 :
objet.id = 1, objet.nom = toto, objet_identite.lecture = false, objet_identite.ecriture = false, objet_identite.modification = false
LIGNE 2 :
objet.id = 2, objet.nom = titi, objet_identite.lecture = false, objet_identite.ecriture = false, objet_identite.modification = false
LIGNE 3 :
objet.id = 3, objet.nom = tutu, objet_identite.lecture = false, objet_identite.ecriture = false, objet_identite.modification = false

car aucun droit pour l’identité COMPTA n'a été affecté.
/** Pas facile d'expliquer dis donc ^^ */
Je vais regarder ta requête de plus près mais il me semble que si je prends l'identité COMPTA ça me retourne aussi :

LIGNE 1 :
objet.id = 1, objet.nom = toto, objet_identite.lecture = false, objet_identite.ecriture = false, objet_identite.modification = false
LIGNE 2 :
objet.id = 2, objet.nom = titi, objet_identite.lecture = true, objet_identite.ecriture = true, objet_identite.modification = true
LIGNE 3 :
objet.id = 3, objet.nom = tutu, objet_identite.lecture = false, objet_identite.ecriture = false, objet_identite.modification = false

[davidbouv]

Arg donc je viens de tester ta requête et là ça me donne bien ce que je veux !!
Alors que hier non, bizarre... J'ai du mal recopier...

P.S. j'ai pris IfNull(), mais selon la base il faut utiliser NVL, Coalesce..

Je connais pas du tout je vais me renseigner.

En tous cas un grand MERCIIII !!

[CinePhil]

P.S. j'ai pris IfNull(), mais selon la base il faut utiliser NVL, Coalesce..

Puisqu'on est dans le forum "Langage SQL" qui est indépendant des SGBD utilisés et donc plutôt consacré au SQL normatif, l'opérateur normatif qui devrait être présent dans tous les SGBD est COALESCE. Celui-ci permet de retourner le premier de ses arguments qui n'est pas NULL.

Code :

COALESCE(NULL, 0) -- résultat = 0

[davidbouv]

Ok merci pour l'info c'est impeccable en SQL en revanche j'utilise doctrine du coup je dois transformer cette requête en DQL et là problème je ne peux pas rajouter de "ON" dans ma jointure et je ne peut pas rajouter de variable dans mes annotations de l'objet objet_identite

Code :

1
2
3
4
/**
    * @orm\ManyToOne(targetEntity="Objet", inversedBy="objet_identite")
    * @orm\JoinColumn(name="objet_id", referencedColumnName="id")
    */

j'ai pas fini d'en baver avec cette requête !!

[CinePhil]

Envoyé par davidbouv

c'est impeccable en SQL en revanche j'utilise doctrine du coup je dois transformer cette requête en DQL et là problème

Ah ces saloperies d'ORM !

J'avais essayé de jouer un peu avec Hibernate et j'avais vite compris qu'il était plus simple d'écrire les requêtes soi-même en SQL et de les balancer tel quel au serveur dans le programme.

Ne connaissant pas Doctrine, je ne peux pas t'aider mais je suppose que c'est le même genre d'usine à gaz !

[davidbouv]

Rahh j'en peux plus 1 jour et demi que je me prends la tête sur cette requête et je n'ai toujours pas trouvé enfin si mais maintenant c'est le DQL qui commence à me sortir par les yeux !!

[CinePhil]

N'y a t-il pas une instruction sous Doctrine qui permet de lancer une requête écrite en SQL natif ?

J'ai pu le faire :
- avec Hibernate en Java ;
- avec Zend Framework en PHP ;
- avec PDO en PHP.

Si Doctrine utilise PDO, ça doit être faisable de faire une requête préparée en SQL avec des paramètres puis de faire exécuter la requête en lui filant les valeurs à affecter aux paramètres.
Ne t'embête pas avec la syntaxe saucissonnée du pseudo-SQL de l'ORM.

[davidbouv]

Oui c'est ce que j'ai fait mais j'ai eu des petits soucis de syntaxe d’ailleurs je pense que c'est quelque chose de mal conçu au niveau de doctrine mais le sujet est dans Symfony2>Doctrine2.
En tous cas ça y est tous fonctionne parfaitement !