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.
