Symfony2 and Doctrine: Many-to-many relation using query builder

Question

I have 2 entities: User and Archive. The user entity has, among others, two properties:

/**
 * @ORM\OneToMany(targetEntity="My\ApplicationBundle\Entity\Archive", mappedBy="user")
 **/
protected $archives;

/**
 * @ORM\ManyToMany(targetEntity="My\ApplicationBundle\Entity\Archive", inversedBy="users")
 * @ORM\JoinTable(name="collection")
 **/
private $collection;

and the Archive entity:

/**
 * @ORM\ManyToOne(targetEntity="My\UserBundle\Entity\User", inversedBy="archives")
 * @ORM\JoinColumn(name="user_id", referencedColumnName="id")
 **/
protected $user;

/**
 * @ORM\ManyToMany(targetEntity="My\UserBundle\Entity\User", mappedBy="collection")
 **/
private $users;

the reason of this little mess is pretty simple: in User, $archives represent all the archives submitted by a user, $collection represent all the archives used by a user (even submitted by other users). In Archive entity, $user represent the user that submitted this archive, $users represent all the users using this archive.

So, to get the archives of one user I simply use to do:

$this->getUser()->getArchives();

and to get the collection:

$this->getUser()->getCollection();

the problem now is I have to paginate the results, so I need to use query builder. what I am doing is:

$repository = $this->getDoctrine()
        ->getRepository('MyApplicationBundle:Archive');

    $query = $repository->createQueryBuilder('a')
        ->innerJoin('a.user', 'u', 'WITH', 'u = :user')
        ->where('a.active = :active')
        ->setParameters(array(
            'user' => $this->getUser(),
            'active' => 1
        ))
        ->setFirstResult($start)
        ->setMaxResults($length)
        ->getQuery()
        ->getResult();

but here the problem is I am going directly to Archive without using the collection table (there is not an entity for that). Can anyone please explain me hot to consider the collection table to filter my results?

Many thanks Manuel

SOLVED

thanks to LPodolski, this is how I changed the DQB

$query = $repository->createQueryBuilder('a')
        //->innerJoin('a.user', 'u', 'WITH', 'u = :user')
        ->innerJoin('a.users', 'cu', 'WITH', 'cu = :user')
        ->where('a.active = :active')
        ->setParameters(array(
            'user' => $this->getUser(),
            'active' => 1
        ))
        ->setFirstResult($start)
        ->setMaxResults($length)
        ->getQuery()
;

LPodolski · Accepted Answer · 2015-03-01 08:10:26Z

2

>innerJoin('a.users', 'cu', 'WITH', 'cu = :collectionUser')

or leftJoin should do the trick

answered Mar 1, 2015 at 8:10

LPodolski

2,8884 gold badges24 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3174311 Over a year ago

could you please be more specific? do I have to use this in place of the current innerjoin or in addiction to it? what can I pass as parameter for :collectionUser? thanks

LPodolski Over a year ago

i dont know exactly what logic do you need, if you want to filer by both then add both, if want to filter instead of current then replace you can set :collectionUser via your setParameters method od add separate ->setParameter

user3174311 Over a year ago

oh now I see, I updated my question with the solution. thank you so much

Collectives™ on Stack Overflow

Symfony2 and Doctrine: Many-to-many relation using query builder

SOLVED

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

SOLVED

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related