Create a createQueryBuilder with UniqueConstraint on 2 keys

Question

i'm trying to retrive data from several tables to make a json, but i'm stuck for the table having UniqueConstraint on 2 keys.

Here's my QueryBuilder sofar :

$qb = $this->_em->createQueryBuilder()
    ->select('partial s.{id, activity}, partial a.{id, title}, partial p.{id, evaluationType}')
    ->from('Innova\PathBundle\Entity\Step', 's')
    ->leftJoin('s.activity', 'a')    //join on Activity entity
    ->leftJoin('a.parameters', 'p')  // join on ActivityParameters entity
    ->andWhere('s.path = 2')
;

but i want to also join on Evaluation entity, which is :

/**
 * @ORM\Table(
 *     name="claro_activity_evaluation",
 *     uniqueConstraints={
 *         @ORM\UniqueConstraint(
 *             name="user_activity_unique_evaluation",
 *             columns={"user_id", "activity_parameters_id"}
 *         )
 *     }
 * )
 */
class Evaluation
{
    /**
     * @ORM\ManyToOne(targetEntity="Claroline\CoreBundle\Entity\User")
     * @ORM\JoinColumn(onDelete="CASCADE", nullable=false)
     */
    protected $user;

    /**
     * @ORM\ManyToOne(targetEntity="Claroline\CoreBundle\Entity\Activity\ActivityParameters")
     * @ORM\JoinColumn(name="activity_parameters_id", onDelete="CASCADE", nullable=false)
     */
    protected $activityParameters;

    /**
     * @ORM\Column(name="attempts_count", type="integer", nullable=true)
     */
    protected $attemptsCount;

}

the User entity :

/**
 * @ORM\Table(name="claro_user")
 * @ORM\Entity(repositoryClass="Claroline\CoreBundle\Repository\UserRepository")
 */
class User 
{
    /**
     * @var integer
     *
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @var string
     *
     * @ORM\Column(name="first_name", length=50)
     * @Assert\NotBlank()
     */
    protected $firstName;

}

The ActivityParameters entity

/**
 * @ORM\Entity
 * @ORM\Table(name="claro_activity_parameters")
 */
class ActivityParameters
{
    /**
     * @var integer
     *
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @var \Claroline\CoreBundle\Entity\Resource\Activity
     *
     * @ORM\OneToOne(
     *     targetEntity="Claroline\CoreBundle\Entity\Resource\Activity",
     *     mappedBy="parameters"
     * )
     * @ORM\JoinColumn(name="activity_id", onDelete="CASCADE", nullable=true)
     */
    protected $activity;

    /**
     * @var string
     *
     * @ORM\Column(name="evaluation_type", nullable=true)
     */
    protected $evaluationType;

    /**
     * @return string
     */
    public function getEvaluationType()
    {
        return $this->evaluationType;
    }
}

the Activity entity

/**
 * @ORM\Table(name="claro_activity")
 */
class Activity
{
    /**
     * @var string
     * @ORM\Column(length=255, nullable=true)
     */
    protected $title;

    /**
     * @ORM\OneToOne(
     *     targetEntity="Claroline\CoreBundle\Entity\Activity\ActivityParameters",
     *     inversedBy="activity",
     *     cascade={"persist"}
     * )
     * @ORM\JoinColumn(name="parameters_id", onDelete="cascade", nullable=true)
     */
    protected $parameters;

    /**
     * @return string
     */
    public function getTitle()
    {
        return $this->title;
    }
}

I have no clue how to modify this querybuilder to retrieve also hte data from Evaluation entity. I want something like :

$qb = $this->_em->createQueryBuilder()
    ->select('partial s.{id, activity}, partial a.{id, title}, partial p.{id, evaluationType}, e')
    ->from('Innova\PathBundle\Entity\Step', 's')
    ->leftJoin('s.activity', 'a')    //join on Activity entity
    ->leftJoin('a.parameters', 'p')  // join on ActivityParameters entity
    ->andWhere('s.path = 2')
    ->leftJoin('?i dont know what?', 'e')  // join on Evaluation entity
    ->andWhere('e.user = 3')      //data for a specific user
;

Thank you for any help

score 0 · Answer 1 · edited May 23 '17 at 12:29

I "seem" to have found a nearly working solution, reading this topic : i had to make a subquery :

$qb->select('e, partial p.{id, evaluationType}, partial a.{id, title}')
    ->from('Claroline\CoreBundle\Entity\Activity\Evaluation', 'e')
    ->leftJoin('e.activityParameters', 'p')
    ->leftJoin('p.activity', 'a')
    ->where(
        $qb->expr()->in(    //needs a subquery
            'a.id',
            $subq->select('a2.id')
                ->from('Innova\PathBundle\Entity\Step', 's')
                ->leftJoin(
                    's.activity',
                    'a2',
                    \Doctrine\ORM\Query\Expr\Join::WITH,
                    $subq->expr()->eq('s.path', '?1')   //can't do a andWhere('s.path = :path'))
                )
                ->getDQL()      //needs a dql, not a querybuilder for the main query
        )
    )
    ->andWhere($qb->expr()->eq('e.user', '?2')) //can't do a andWhere('e.user = :user'))
    ->setParameter(1, $pid)
    ->setParameter(2, $uid)
;

I just would also like to retrieve the Step entity data (id, ...) in the query result. I can't add 's' to the main select : i have Error: 's' is used outside the scope of its declaration.

Create a createQueryBuilder with UniqueConstraint on 2 keys

1 Answers1