Bundle Sonata Requête avec jointure

**Dark_fun** · 06/10/2014, 12h45

Bonjour à tous,

Je découvre le bundle Sonata pour symfony.
J'ai un soucis avec une requête.

Tout d'abord voici mes entités :

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
<?php
 
/**
 * This file is part of the <name> project.
 *
 * (c) <yourname> <youremail>
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 */
 
namespace Application\Sonata\UserBundle\Entity;
 
use Sonata\UserBundle\Entity\BaseUser as BaseUser;
use Doctrine\ORM\Mapping as ORM;
 
/**
 * User
 *
 * @ORM\Table(name="fos_user_user")
 * @ORM\Entity(repositoryClass="Application\Sonata\UserBundle\Entity\UserRepository")
 */
class User extends BaseUser
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;
 
    /**
     * @var string
     *
     * @ORM\Column(name="backupDirectory", type="string", nullable=true)
     */
    private $backupDirectory;
 
    /**
     * @ORM\ManyToOne(targetEntity="BackOfficeBundle\Entity\Compagny")
     */
    private $compagny;
 
    /**
     * @ORM\OneToOne(targetEntity="BackOfficeBundle\Entity\Contract")
     */
    private $contract;
 
    /**
     * @ORM\OneToMany(targetEntity="BackOfficeBundle\Entity\CompagnyUser", mappedBy="users")
     */
    private $userCompagny;
 
    public function __construct() 
    {
        parent::__construct();
    }
 
    /**
     * Get id
     *
     * @return integer $id
     */
    public function getId()
    {
        return $this->id;
    }
    /**
     * Set backupDirectory
     *
     * @param string $backupDirectory
     * @return User
     */
    public function setBackupDirectory($backupDirectory)
    {
        $this->backupDirectory = $backupDirectory;
 
        return $this;
    }
 
    /**
     * Get backupDirectory
     *
     * @return string 
     */
    public function getBackupDirectory()
    {
        return $this->backupDirectory;
    }
 
    /**
     * Gets the value of compagny.
     *
     * @return mixed
     */
    public function getCompagny()
    {
        return $this->compagny;
    }
 
    /**
     * Sets the value of compagny.
     *
     * @param mixed $compagny the compagny 
     *
     * @return self
     */
    public function setCompagny($compagny)
    {
        $this->compagny = $compagny;
 
        return $this;
    }
 
    /**
     * Gets the value of userCompagny.
     *
     * @return mixed
     */
    public function getUserCompagny()
    {
        return $this->userCompagny;
    }
}

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
<?php
 
namespace BackOfficeBundle\Entity;
 
use Doctrine\ORM\Mapping as ORM;
 
/**
 * CompagnyUser
 *
 * @ORM\Table(name="CompagnyUser")
 * @ORM\Entity(repositoryClass="BackOfficeBundle\Entity\CompagnyUserRepository")
 */
class CompagnyUser
{
	/**
	 * @var integer
	 *
	 * @ORM\Column(name="id", type="integer")
	 * @ORM\Id
	 * @ORM\GeneratedValue(strategy="AUTO")
	 */
	private $id;
 
	/**
	 * @ORM\ManyToOne(targetEntity="Sonata\UserBundle\Entity\User", inversedBy="userCompagny")
	 */
	private $users;
 
	/**
	 * @ORM\ManyToOne(targetEntity="BackOfficeBundle\Entity\Compagny", inversedBy="compagnyUser")
	 */
	private $compagnys;
 
	/**
	 * Get id
	 *
	 * @return integer 
	 */
	public function getId()
	{
		return $this->id;
	}
 
	/**
	 * Sets the value of id.
	 *
	 * @param integer $id the id 
	 *
	 * @return self
	 */
	private function _setId($id)
	{
		$this->id = $id;
 
		return $this;
	}
 
	/**
	 * Gets the value of users.
	 *
	 * @return mixed
	 */
	public function getUsers()
	{
		return $this->users;
	}
 
	/**
	 * Sets the value of users.
	 *
	 * @param mixed $users the users 
	 *
	 * @return self
	 */
	private function setUsers($users)
	{
		$this->users = $users;
 
		return $this;
	}
 
	/**
	 * Gets the value of compagnys.
	 *
	 * @return mixed
	 */
	public function getCompagnys()
	{
		return $this->compagnys;
	}
 
	/**
	 * Sets the value of compagnys.
	 *
	 * @param mixed $compagnys the compagnys 
	 *
	 * @return self
	 */
	private function setCompagnys($compagnys)
	{
		$this->compagnys = $compagnys;
 
		return $this;
	}
}

L'objectif est d'afficher une liste d'utilisateur en fonction du role d'un utilisateur.
Je m'explique, un utilisateur peut avoir comme rôle client, gestionnaire ou revendeur.
Un utilisateur revendeur appartient à une compagnie mais il peut également gérer d'autre compagny.
D'ou la table CompagnyUser.
J'aimerai afficher la liste des utilisateurs de l'entreprise du revendeur + les utilisateurs des entreprise qu'il gère.

J'ai crée la requête sql, je l'ai testé sur mysql, elle me retourne la bonne liste, je vous la met ci-dessous:

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT fos_user_user.id, 
	    fos_user_user.compagny_id, 
            fos_user_user.username,
            compagnyuser.users_id, 
            compagnyuser.compagnys_id  
FROM `fos_user_user`
JOIN compagnyuser
ON (fos_user_user.compagny_id = 1) OR (fos_user_user.compagny_id = compagnyuser.compagnys_id)
GROUP BY fos_user_user.username

Maintenant quand j'intègre cette requête dans mon code comme ci-dessous :

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
 
    public function createQuery($context = 'list')
    {
        $securityContext = $this->getSecurityContext();
 
        $query = parent::createQuery($context);
 
        $user = $securityContext->getToken()->getUser();
        $id = $user->getId();
        $idUserCompagny = $user->getCompagny()->getId();
        if($securityContext->isGranted('ROLE_SONATA_USER_ADMIN_USER_GUEST'))  
        {
            $query->where($query->getRootAlias().'.id = :user_id')
                  ->setParameter('user_id', $id)
            ;
        }
        else if($securityContext->isGranted('ROLE_SONATA_USER_ADMIN_USER_MANAGER'))  
        {
 
            $query->where($query->getRootAlias().'.compagny = :compagnyid')
                  ->setParameter('compagnyid', $idUserCompagny)
            ;
        }
        else if($securityContext->isGranted('ROLE_SONATA_USER_ADMIN_USER_DEALER'))
        {
            $query->join($query->getRootAlias().'.userCompagny', 'c', 'WITH', $query->getRootAlias().'.compagny = compagny_id OR '. $query->getRootAlias().'.compagny = c.compagnys')
                      ->setParameter('compagny_id', $idUserCompagny)
            ;
        }
        return $query;
    }

Symfony modifie ma 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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
SELECT 
  f0_.username AS username0, 
  f0_.username_canonical AS username_canonical1, 
  f0_.email AS email2, 
  f0_.email_canonical AS email_canonical3, 
  f0_.enabled AS enabled4, 
  f0_.salt AS salt5, 
  f0_.password AS password6, 
  f0_.last_login AS last_login7, 
  f0_.locked AS locked8, 
  f0_.expired AS expired9, 
  f0_.expires_at AS expires_at10, 
  f0_.confirmation_token AS confirmation_token11, 
  f0_.password_requested_at AS password_requested_at12, 
  f0_.roles AS roles13, 
  f0_.credentials_expired AS credentials_expired14, 
  f0_.credentials_expire_at AS credentials_expire_at15, 
  f0_.created_at AS created_at16, 
  f0_.updated_at AS updated_at17, 
  f0_.date_of_birth AS date_of_birth18, 
  f0_.firstname AS firstname19, 
  f0_.lastname AS lastname20, 
  f0_.website AS website21, 
  f0_.biography AS biography22, 
  f0_.gender AS gender23, 
  f0_.locale AS locale24, 
  f0_.timezone AS timezone25, 
  f0_.phone AS phone26, 
  f0_.facebook_uid AS facebook_uid27, 
  f0_.facebook_name AS facebook_name28, 
  f0_.facebook_data AS facebook_data29, 
  f0_.twitter_uid AS twitter_uid30, 
  f0_.twitter_name AS twitter_name31, 
  f0_.twitter_data AS twitter_data32, 
  f0_.gplus_uid AS gplus_uid33, 
  f0_.gplus_name AS gplus_name34, 
  f0_.gplus_data AS gplus_data35, 
  f0_.token AS token36, 
  f0_.two_step_code AS two_step_code37, 
  f0_.id AS id38, 
  f0_.backupDirectory AS backupDirectory39, 
  f0_.compagny_id AS compagny_id40, 
  f0_.contract_id AS contract_id41 
FROM 
  fos_user_user f0_ 
  INNER JOIN CompagnyUser c1_ ON f0_.id = c1_.users_id 
  AND (
    f0_.compagny_id = 1 
    OR f0_.compagny_id = c1_.compagnys_id
  ) 
  LEFT JOIN fos_user_user_group f3_ ON f0_.id = f3_.user_id 
  LEFT JOIN fos_user_group f2_ ON f2_.id = f3_.group_id 
WHERE 
  f0_.id IN (?) 
ORDER BY 
  f0_.id ASC

Il ajoute cette condition -> ON f0_.id = c1_.users_id
J'aimerai savoir comment éviter ceci ?!
J'espère avoir été clair, demander pour toutes précisions

Merci d'avance

Julien

**arnooo999** · 06/10/2014, 13h54

Bonjour,

Si tu observes bien les requetes dans le profiler, tu devrais voir 3 requetes différentes pour cette affichage, cela est du au module de pagination
-> une requete pour récupérer le nombre d'entité
-> un requete pour récupérer les id des entités de la page (NB le nombre de ligne en SQL n'est pas forcément égal au nombre d'entité que tu souhaites récupérer, si tu fais une jointure avec une entité lié en OneToMany)
-> une requete qui utilise ces id pour récupérer les entités

D'autre part:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
/...
if($securityContext->isGranted('ROLE_SONATA_USER_ADMIN_USER_MANAGER'))  
        {
            $qb = createNativeQuery();
 
            $qb->where($query->getRootAlias().'.compagny = :compagnyid')
                  ->setParameter('compagnyid', $idUserCompagny)
            ;
        }
/...

- je ne vois pas ou tu utilises $qb dans la suite de la méthode, c'est toujours $query qui est retourné.
- Il existe vraiment une function createNativeQuery ?

**Dark_fun** · 06/10/2014, 14h05

Je me suis planté de code :

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
    public function createQuery($context = 'list')
    {
        $securityContext = $this->getSecurityContext();
 
        $query = parent::createQuery($context);
 
        $user = $securityContext->getToken()->getUser();
        $id = $user->getId();
        $idUserCompagny = $user->getCompagny()->getId();
        if($securityContext->isGranted('ROLE_SONATA_USER_ADMIN_USER_GUEST'))  
        {
            $query->where($query->getRootAlias().'.id = :user_id')
                  ->setParameter('user_id', $id)
            ;
        }
        if($securityContext->isGranted('ROLE_SONATA_USER_ADMIN_USER_MANAGER'))  
        {
 
            $query->where($query->getRootAlias().'.compagny = :compagnyid')
                  ->setParameter('compagnyid', $idUserCompagny)
            ;
        }
        else if($securityContext->isGranted('ROLE_SONATA_USER_ADMIN_USER_DEALER'))
        {
            $query->join($query->getRootAlias().'.userCompagny', 'c', 'WITH', $query->getRootAlias().'.compagny = compagny_id OR '. $query->getRootAlias().'.compagny = c.compagnys')
                  ->setParameter('compagny_id', $idUserCompagny)
            ;
        }
        return $query;
    }

Mon problème est sur le deuxième else if pour le role dealer.
Lorsque je regarde la requête exécutée, il y a un (ON c1_ ON f0_.id = c1_.users_id) qui est ajouté et qui me fausse le résultat de ma requête.

**arnooo999** · 06/10/2014, 22h18

Si tu fais un join, il est normal que Doctrine ajoute le critere pour faire cette jointure.

Que donnerais ceci:

Code :

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

1
2
3
4
5
6
7
8
 
else if($securityContext->isGranted('ROLE_SONATA_USER_ADMIN_USER_DEALER'))
        {
            $query->join($query->getRootAlias().'.userCompagny', 'c')
                  ->andWhere('c.compagnys = :compagny_id OR '. $query->getRootAlias().'.compagny MEMBER OF c.compagnys')
                  ->setParameter('compagny_id', $idUserCompagny)
            ;
        }

ou bien

Code :

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

1
2
3
4
5
6
7
8
 
else if($securityContext->isGranted('ROLE_SONATA_USER_ADMIN_USER_DEALER'))
        {
            $query->join($query->getRootAlias().'.userCompagny', 'c', 'ON', $query->getRootAlias().'.compagny = compagny_id OR '. $query->getRootAlias().'.compagny = c.compagnys')
                      ->setParameter('compagny_id', $idUserCompagny)
                      ->groupBy($query->getRootAlias())
            ;
        }

**Dark_fun** · 07/10/2014, 10h14

La joiture avec le ON, j'ai déjà essayé il me sort une erreur :

[Syntax Error] line 0, col 112: Error: Expected end of string, got 'ON'

Pour la première query, j'ai également une erreur :

[Semantical Error] line 0, col 199 near 'compagnys': Error: Invalid PathExpression. Must be a CollectionValuedAssociationField

**Nico_F** · 07/10/2014, 10h20

Essaye avec 'WITH' à la place de 'ON'.
Et met des double points devant tes variables

Code :

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

$query->getRootAlias().'.compagny = :compagny_id

et company ne prend pas de g ... soit dit en passant.

**Dark_fun** · 07/10/2014, 11h13

Code :

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

1
2
3
$query->join($query->getRootAlias().'.userCompagny', 'c', 'WITH', $query->getRootAlias().'.compagny = :compagny_id OR '. $query->getRootAlias().'.compagny = c.compagnys')
      ->setParameter('compagny_id', $idUserCompagny)
      ->groupBy($query->getRootAlias());

Avec un WITH je n'ai pas d'erreur, mais il ne surcharge pas correctement la requête. Du coup je ne me retrouve pas avec la bonne liste.

Code sql :

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

1
2
3
4
5
6
7
SELECT * FROM 
  fos_user_user f0_ 
  INNER JOIN CompagnyUser c1_ ON f0_.id = c1_.users_id 
  AND (
    f0_.compagny_id = ? 
    OR f0_.compagny_id = c1_.compagnys_id
  )

J'ai testé un peu toute les façons de faire ma jointure, sans succès.
Impossible de surcharger correctement la requête de symfony.
Il y a toujours ce ON f0_.id = c1_.users_id.

Auriez vous un exemple de comment remplacer ce ON généré, par mes conditions pour avoir cette requête :

Code sql :

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

1
2
3
4
5
6
7
SELECT *
FROM 
  fos_user_user f0_ 
  INNER JOIN CompagnyUser c1_ ON (
    f0_.compagny_id = ?
    OR f0_.compagny_id = c1_.compagnys_id
  )

Merci pour vos réponses !

Oui, effectivement ... Merci nico

**Nico_F** · 07/10/2014, 11h40

La requête attendue me gène un peu, dans la mesure ou il y a un cas ou ton ON fais que tu n'as pas de jointure. Dans ce cas il y a le LEFT Join.

Je verrais d'avantage ta requête SQL ressembler à ça :

Code sql :

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

1
2
3
4
5
SELECT * FROM fos_user_user f0_ 
LEFT JOIN CompagnyUser c1_ 
ON f0_.compagny_id = c1_.compagnys_id
WHERE c1.compagnys_id IS NULL
OR f0_.compagny_id = 1234

Du coup avec le QueryBuilder :

Code :

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

1
2
3
4
5
6
 // J'avoue qu'entre l'orthographe et les pluriels là ou ça n'a pas de sens je ne suis pas tout à fait sûr de ce que je fais
$query->leftjoin("$alias.userCompagny", 'c', 'WITH', "alias.compagny = c.compagnys")
    ->setParameter('compagny_id', $idUserCompagny)
    ->where("c.compagnys IS NULL OR $alias.compagny = :compagny_id") 
    ->groupBy($alias)
;

Bundle Sonata Requête avec jointure

Symfony PHP

Discussions similaires

Partager

Partager