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