Have you tried validating the mappings?
$ doctrine orm:validate-schema
--
Jasper N. Brouwer
(@jaspernbrouwer)
On 13 Feb 2014, at 10:23, Jess Rowbottom <[email protected]> wrote:
> Hi all --
>
> I've got a bit of an issue with a double join in Doctrine. This is using
> Doctrine 2.4 with Zend 2.2, and MySQL 5.5.35.
>
> I have three entities:
> - Sets, which contain photos.
> - Tags, which may have many photos.
> - Photos, which may have many photos associated with them
>
> The issue I have is with this bit of DQL, to get all the tags associated with
> photos within a set:
>
> $tagQuery=$entityManager->createQuery("SELECT DISTINCT t
> FROM Hotfox\Entity\Tag t
> LEFT JOIN t.photos p
> LEFT JOIN p.set s
> WHERE s.id = :set");
>
> However, this then throws an error citing bad SQL generated by the DQL parser:
>
> An exception occurred while executing 'SELECT DISTINCT t0_.ta_id AS ta_id0,
> t0_.ta_word AS ta_word1, t0_.ta_title AS ta_title2, t0_.ta_date_added AS
> ta_date_added3, t0_.ta_groupid AS ta_groupid4 FROM tags t0_ LEFT JOIN LEFT
> JOIN sets s2_ ON p1_.ph_setid = s2_.se_id WHERE s2_.se_id = ?' with params
> ["412"]:
>
> SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in
> your SQL syntax; check the manual that corresponds to your MySQL server
> version for the right syntax to use near 'LEFT JOIN sets s2_ ON p1_.ph_setid
> = s2_.se_id WHERE s2_.se_id = '412'' at line 1
>
>
> "LEFT JOIN LEFT JOIN"? It looks like it's not adding the inverse lookup of
> the many-to-many relationship from the Photos entity.
>
> So far I've regenerated the database from scratch using doctrine-module,
> checked the entities are created in the database correctly, etc. Pulling tags
> by photo and photos by set are working fine.
>
> The relationships are defined thus (entities snipped for brevity):
>
> class Tag
> {
> (....)
> /**
> * @var integer
> *
> * @ORM\Column(name="ta_id", type="integer", nullable=false)
> * @ORM\Id
> * @ORM\GeneratedValue(strategy="IDENTITY")
> */
> private $id;
>
> /**
> * @ORM\ManyToMany(targetEntity="Hotfox\Entity\Photo", mappedBy="ph_id",
> cascade={"all"})
> **/
> private $photos;
> }
>
> class Photo
> {
> (...)
> /**
> * @var integer
> *
> * @ORM\Column(name="ph_id", type="bigint", nullable=false)
> * @ORM\Id
> * @ORM\GeneratedValue(strategy="IDENTITY")
> */
> private $id;
>
> /**
> * @ORM\ManyToOne(targetEntity="Hotfox\Entity\Set")
> * @ORM\JoinColumns({
> * @ORM\JoinColumn(name="ph_setid", referencedColumnName="se_id")
> * })
> */
> private $set;
>
> /**
> * @ORM\ManyToMany(targetEntity="Tag")
> * @ORM\JoinTable(name="tag_photo_link",
> * joinColumns={@ORM\JoinColumn(name="tm_photoid",
> referencedColumnName="ph_id")},
> * inverseJoinColumns={@ORM\JoinColumn(name="tm_keywordid",
> referencedColumnName="ta_id")}
> * )
> **/
> private $tags;
> }
>
> class Set
> {
> /**
> * @var integer
> *
> * @ORM\Column(name="se_id", type="bigint", nullable=false)
> * @ORM\Id
> * @ORM\GeneratedValue(strategy="IDENTITY")
> */
> private $id;
> }
>
> Any clues please?
>
> Cheers
>
> jx
--
You received this message because you are subscribed to the Google Groups
"doctrine-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/doctrine-user.
For more options, visit https://groups.google.com/groups/opt_out.
