mysql - Symfony/Doctrine : ManyToMany Relation and SQL exception

Question

我在我的 Symfony 应用程序中定义了实体“Slideshow”和“Slide”之间的多对多关系。

实体幻灯片(摘录):

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="slide")
 */
class Slide {

/* [...] */

/**
 * @ORM\ManyToMany(targetEntity="Slideshow", inversedBy="slides", cascade={"persist"})
 * @ORM\JoinTable(name="slides_to_slideshows",
 *      joinColumns={@ORM\JoinColumn(name="slide_id", referencedColumnName="id")},
 *      inverseJoinColumns={@ORM\JoinColumn(name="slideshow_id", referencedColumnName="id")}
 *      )
 */
private $slideshows;


/**
 * Constructor
 */
public function __construct()
{
    $this->slideArticle = new \Doctrine\Common\Collections\ArrayCollection();
    $this->slideshows = new \Doctrine\Common\Collections\ArrayCollection();
}


/* [...] */

/**
 * Add slideshow
 *
 * @param Slideshow $slideshow
 *
 * @return Slide
 */
public function addSlideshow(Slideshow $slideshow)
{
    $this->slideshow[] = $slideshow;

    return $this;
}

/**
 * Get slideshows
 *
 * @return \Doctrine\Common\Collections\Collection
 */
public function getSlideshows()
{
    return $this->slideshows;
}

/**
 * Remove slideshow
 *
 * @param Slideshow $slideshow
 */
public function removeSlideshow(Slideshow $slideshow)
{
    $this->slideshows->removeElement($slideshow);
}
}

实体幻灯片(摘录):

/**
 * @ORM\Entity
 * @ORM\Table(name="slideshow")
 */
class Slideshow {

/* [...] */


    /**
     * @ORM\ManyToMany(targetEntity="Slide", mappedBy="slideshows")
     */
    private $slides;           

    /**
     * Constructor
     */
    public function __construct()
    {
        $this->slides = new \Doctrine\Common\Collections\ArrayCollection();
    }

/* [...] */

    /**
     * Get slides
     *
     * @return \Doctrine\Common\Collections\Collection
     */
    public function getSlides()
    {
        return $this->slides;
    }

    /**
     * Add slide
     *
     * @param \Screensolutions\Macms\FrontBundle\Entity\Slide $slide
     *
     * @return Slideshow
     */
    public function addSlide(\Screensolutions\Macms\FrontBundle\Entity\Slide $slide)
    {
        $this->slides[] = $slide;

        return $this;
    }

    /**
     * Remove slide
     *
     * @param \Screensolutions\Macms\FrontBundle\Entity\Slide $slide
     */
    public function removeSlide(\Screensolutions\Macms\FrontBundle\Entity\Slide $slide)
    {
        $this->slides->removeElement($slide);
    }
}

当我进行数据库查询时，我收到此错误消息:

An exception occurred while executing 'SELECT t0.id AS id_1, t0.deleted AS deleted_2, t0.title AS title_3, t0.link AS link_4, t0.rank AS rank_5, t0.artist_id AS artist_id_6 FROM slide t0 WHERE slides_to_slideshows.slideshow_id = ? ORDER BY t0.deleted ASC, t0.rank ASC, t0.title ASC' with params ["2"]:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'slides_to_slideshows.slideshow_id' in 'where clause'

表 slides_to_slideshows 存在，字段 slideshow_id 也存在，所以我猜我对关系的定义有问题。我想知道为什么查询是 slides_to_slideshows.slideshow_id 而不是 FROM slide t0, slides_to_slideshows t1 WHERE t1.slideshow_id？

有什么提示吗？

最好的问候

---

编辑:这是一个引发错误的查询:

$repository = $this->getDoctrine()->getRepository('FrontBundle:Slide');
$slidesAll = $repository->findBy(array('slideshows' => $id), array('deleted' => 'ASC', 'rank' => 'ASC', 'title' => 'ASC'));

按照 Cerad 的建议，我用一个简单的 findAll() 查询进行了测试，结果完美无缺。问题是 where 子句 'slideshows' => $id，所以我想关系的定义有问题。

缓存被清除至少一千次，数据库被 doctrine:schema:update --force 更新，doctrine:schema:validate 没有报告任何这个实体的问题。

有什么建议吗？我感谢每一个提示 - 这个问题让我发疯:(

Answer 1

你应该这样做

$repository = $this->getDoctrine()->getRepository('FrontBundle:Slide')->createQueryBuilder('s')
->join('s.slideshows', 'sw')
->andWhere('sw.id=:id')->setParameter('id', $id)
->addOrderBy('s.deleted', 'ASC')
...