CODEWITHSUNDEEP
phpsymfonydoctrine-ormone-to-many
ibi0tux
ibi0tux

Reputation: 2629

Symfony2 / Doctrine : findBy with OneToMany cardinality

I would like to if it possible (and how) to use a OneToMany relationship cardinality as findBy filter.

For instance here, let's say I have two entities User and Post with a OneToMany relationship between them so that an user has a collection of posts.

I am looking for a way to get all users that have at least one post, that is to say :
|user.posts| >= 1 or more programmatically count(user->getPosts()) >= 1

I know this can be achieved with a QueryBuilder or DQL but I am quite sure there is a trick that allows to make it work with findBy.

Here is the idea of what I am willing to do :

class UserRepository extends EntityRepository
{
    public function myQuery()
    {
        return $this->findBy(
            array(... 'posts' ...), // What should I put here ?
            array('email' => 'ASC')
        );
    }
}

Upvotes: 1

Views: 3048

Answers (1)

Terenoth
Terenoth

Reputation: 2598

People in the comments are completely right, so I'm summing up:

  • findBy won't work on a OneToMany, particularly since you want to find by the number of related entities.

  • This means you have to use a QueryBuilder.

In your UserRepository, use the following:

$qb = $this->createQueryBuilder('users')
    ->leftJoin('user.posts', 'posts')
    ->addGroupBy('users')
    ->having('COUNT(posts) >= :limit')
    ->setParameter('limit', 1);
  • Since in your use case, the limit of Posts you want to select is 1, as said @Yoshi, you can use INNER JOIN instead of GROUP BY + HAVING, which will automatically unselect Users with no Post.

Instead of what is above, use this:

$qb = $this->createQueryBuilder('users')
    ->innerJoin('user.posts', 'posts');

Upvotes: 6

Related Questions