CODEWITHSUNDEEP
phpsymfonyjoindoctrine-ormwhere-clause
Jake N
Jake N

Reputation: 10583

Doctrine2 WHERE on optional JOIN

This fails

$builder = $repo->createQueryBuilder("ub")
    ->where("ub.badge = :badge AND ub.user = :user AND ub.project = :project")
    ->setMaxResults(1)
    ->setParameter("project", $project)
    ->setParameter("user", $user)
    ->setParameter("badge", $badge);

The project may or may not be present and results in this error

Binding entities to query parameters only allowed for entities that have an identifier

If the query is changed to the following it still fails, suggesting its the project rather than any other entity. This still fails even if there are no rows in the ub (UserBadge) table.

$builder = $userBadgesRepo->createQueryBuilder("ub")
    ->where("ub.project = :project")
    ->setMaxResults(1)
    ->setParameter("project", $project);

How can I change this query to work with a project in the WHERE statement?

The UserBadge YML

Acme\NameBundle\Entity\UserBadges:
    type: entity
    table: user_badges
    repositoryClass: Acme\NameBundle\Entity\UserBadgesRepository
    fields:
        id:
            type: integer
            id: true
            generator:
                strategy: AUTO

        added:
            type: datetime

    manyToOne:
        badge:
            targetEntity: Acme\NameBundle\Entity\Badge
            inversedBy: badge_users
            joinColumn:
                onDelete: CASCADE
                name: badge_id
                referencedColumnName: id
        user:
            targetEntity: Acme\NameBundle\Entity\User
            inversedBy: user_badges
            joinColumn:
                onDelete: CASCADE
                name: user_id
                referencedColumnName: id
        project:
            targetEntity: Acme\NameBundle\Entity\Project
            inversedBy: project_badges
            joinColumn:
                onDelete: CASCADE
                name: project_id
                referencedColumnName: id
                nullable: true

Project YML

Acme\NameBundle\Entity\Project:
    type: entity
    table: project
    repositoryClass: Acme\NameBundle\Entity\ProjectRepository
    fields:
        id:
            type: integer
            id: true
            generator:
                strategy: AUTO
**SNIP**

    oneToMany:
         project_badges:
             targetEntity: Acme\NameBundle\Entity\UserBadges
             mappedBy: project

Upvotes: 1

Views: 1629

Answers (1)

Marcel Burkhard
Marcel Burkhard

Reputation: 3523

This should work:

$builder = $repo->createQueryBuilder("ub")
    ->leftJoin('ub.project', 'p')
    ->join('ub.badge', 'b')
    ->join('ub.user', 'u')
    ->where("b.id = :badge AND u.id = :user AND p.id = :project")
    ->setMaxResults(1)
    ->setParameter("project", $project->getId())
    ->setParameter("user", $user->getId())
    ->setParameter("badge", $badge->getId());

Explanation (extracted from comment):

The relationships are stored by reference (id) and you can directly query by those, but not the object itself because at the time of the query sql doesn't know about the table of the other entity. You can only compare relationship objects after you manually joined them.

Please also read section 5 here: http://labs.octivi.com/mastering-symfony2-performance-doctrine

Upvotes: 2

Related Questions