I'm crossposting this from Doctrine-users list since the error happens under
Symfony2 and not under Doctrine2
I am using both Doctrine2 and Symfony2 but Symfony2 fails me at a task I am
able to accomplish in Doctrine2 (the 'standalone' version).
/*---------------------- In Doctrine2: -----------------------/
I have 2 classes, Outcome, Ticket as such:
namespace Entity;
/** @Entity @Table(name="tickets") */
class Ticket {
/** @Id @Column(type="integer") @GeneratedValue(strategy="AUTO") */
private $id;
/** @Column(type="string", length=100) */
private $title;
/** @OneToMany(targetEntity="Outcome", mappedBy="ticket") */
private $outcomes;
/* with setters and getters for title and outcomes */
public function __toString() {
return $this->getTitle().' ('.$this->getId().')';
}
public function __construct () {
$this->outcomes=new \Doctrine\Common\Collections\ArrayCollection();
}
}
and
namespace Entity;
/** @Entity @Table(name="outcomes") */
class Outcome {
/** @Id @Column(type="integer") @GeneratedValue(strategy="AUTO") */
private $id;
/** @Column(type="string", length=100) */
private $text;
/** @ManyToOne(targetEntity="Ticket", inversedBy="outcomes")
@JoinColumn(nullable=false) */
private $ticket;
public function __toString() {
return $this->getText().' ' .' ('.$this->getId().')';
}
}
in my index.php I use
$query = $em->createQuery ("SELECT t,o FROM Entity\Ticket t LEFT JOIN
t.outcomes o WHERE t.id=1");
$query->useResultCache(true);
$tickets_2 = $query->execute();
$ticket_2 = $tickets_2[0];
$amount=count($ticket_2->getOutcomes());
echo $ticket_2." --ticket\n";
echo $amount." --outcomes\n";
and I get the correct output
/*---------------------- in Symfony2: -----------------------/
I use the following definitions for the entities:
namespace test\DemoBundle\Entity;
/** @orm:Entity @orm:Table(name="tickets") */
class Ticket {
/** @orm:Id @orm:Column(type="integer")
@orm:GeneratedValue(strategy="AUTO") */
private $id;
/** @orm:Column(type="string", length=100) */
private $title;
/** @orm:OneToMany(targetEntity="Outcome", mappedBy="response") */
private $outcomes;
...
}
namespace test\DemoBundle\Entity;
/** @orm:Entity @orm:Table(name="outcomes") */
class Outcome {
/** @orm:Id @orm:Column(type="integer")
@orm:GeneratedValue(strategy="AUTO") */
private $id;
/** @orm:Column(type="string", length=100) */
private $text;
/** @orm:ManyToOne(targetEntity="Ticket", inversedBy="outcomes")
@orm:JoinColumn(nullable=false) */
private $ticket;
....
}
now, I've set up a controller with a test function:
namespace test\DemoBundle\Controller;
use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Symfony\Component\HttpFoundation\Response;
use test\DemoBundle\Entity\Ticket as Ticket;
use test\DemoBundle\Entity\Outcome as Outcome;
class TicketController extends Controller {
...
public function testAction() {
$em = $this->get('doctrine.orm.entity_manager');
$query = $em->createQuery ("SELECT t, o
FROM test\DemoBundle\Entity\Ticket t
LEFT JOIN t.outcomes o
WHERE t.id=1");
$query->useResultCache(true);
$tickets_2 = $query->execute();
$ticket_2 = $tickets_2[0];
$amount=count($ticket_2->getOutcomes());
$prepared_result=$ticket_2." --ticket ".$amount." --outcomes ";
$response = new Response($prepared_result);
return $response;
}
}
at this point I get an error while trying to execute the last query like so:
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 'WHERE t0_.id = 1' at line 1
from the webdebugger I get the following syntax from PDO:
SELECT t0_.id AS id0,
t0_.title AS title1,
o1_.id AS id2,
o1_.text AS text3,
o1_.ticket_id AS ticket_id4
FROM tickets t0_
LEFT JOIN WHERE t0_.id = 1
so I guess I am missing something after the LEFT JOIN so that's why I am
gettint the error in the first place?
What am I doing wrong?/What should I do?
p.s. the schema generated by both is the same and is as follows:
CREATE TABLE IF NOT EXISTS `tickets` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
CREATE TABLE IF NOT EXISTS `outcomes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ticket_id` int(11) NOT NULL,
`text` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
KEY `IDX_6E54D0FA700047D2` (`ticket_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
ALTER TABLE `outcomes`
ADD CONSTRAINT `outcomes_ibfk_1` FOREIGN KEY (`ticket_id`) REFERENCES
`tickets` (`id`);
--
If you want to report a vulnerability issue on symfony, please send it to
security at symfony-project.com
You received this message because you are subscribed to the Google
Groups "symfony users" group.
To post to this group, send email to symfony-users@googlegroups.com
To unsubscribe from this group, send email to
symfony-users+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/symfony-users?hl=en
