在symfony中选择带有doctrine的连接

Question

This is generating the wrong ON clause:

public function findByAdOwner($ownerId)
{
    $qb = $this->getEntityManager()->createQueryBuilder('n');

    return $qb->select('n')
              ->from('DelivveWebBundle:UserAd', 'n')
              ->join('DelivveWebBundle:Ad', 'ad', "WITH", "n.ad = ad.id")
              ->where('ad.owner = :ownerId')
              ->setParameter('ownerId', $ownerId)
              ->getQuery()
              ->getResult()
    ;
}

[2/2] DBALException: An exception occurred while executing 'SELECT u0_.status AS status0, u0_.user_id AS user_id1, u0_.ad_id AS ad_id2 FROM user_ad u0_ INNER JOIN my_ad m1_ LEFT JOIN Deliver d2_ ON m1_.id = d2_.id LEFT JOIN Sender s3_ ON m1_.id = s3_.id AND (u0_.ad_id = m1_.id) WHERE m1_.user_id = ?' with params [1]:

Where am I going wrong?

Answer 1

Ad.php

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

    /**
     * @var ArrayCollection
     *
     * @ORM\OneToMany(targetEntity="UserAd", mappedBy="ad")
     */
    protected $interestedUsers;
}

UserAd.php

/*     *@ORM\Entity(repositoryClass="Delivve\WebBundle\Entity\UserAdRepository")
     */
        class UserAd
        {
            /**
             * @ORM\Id()
             * @ORM\ManyToOne(targetEntity="User", inversedBy="interestInAds")
             * @ORM\JoinColumn(name="user_id", referencedColumnName="id")
             **/
            protected $user;

            /**
             * @ORM\Id()
             * @ORM\ManyToOne(targetEntity="Ad", inversedBy="interestedUsers")
             * @ORM\JoinColumn(name="ad_id", referencedColumnName="id")
             **/
            protected $ad;
        }

UserAdRepository.php

class UserAdRepository extends EntityRepository
{
    public function findByAdOwner($ownerId)
    {
        $qb = $this->getEntityManager()->createQueryBuilder('n');

        return $qb->select('n')
            ->from('DelivveWebBundle:UserAd', 'n')
            ->join('n.ad', 'ad')
            ->where('ad.owner = :ownerId')
            ->setParameter('ownerId', $ownerId)
            ->getQuery()
            ->getResult()
        ;
   }
}

could have the result expected with the amendment that follows the join is done with the own table element with already hesitate a note in UserAd entity it already makes a direct call table

AdController.php

...
$owner = $this->getUser();

$repository = $this->getDoctrine()->getRepository('DelivveWebBundle:UserAd');

$notifications = $repository->findByAdOwner($owner->getId());
...