Unable to retrieve records using a variable in Symfony2, but works if I
don't use a variable?
I have three SQL statements in Symfony2, and they all use a variable that
contains the record ID (which is passed through using the URL). The first
SQL statement works correctly, however, the other two don't. They often
result in errors like this:
An exception occurred while executing 'SELECT m0_.name AS name0,
m0_.created AS created1, m0_.event_date AS event_date2, m0_.description AS
description3, m0_.event_type AS event_type4, m1_.surname AS surname5,
m1_.first_name AS first_name6 FROM map_lists m0_ LEFT JOIN
map_list_members m2_ ON (m2_.list_id = m0_.id) LEFT JOIN map_contacts m1_
ON (m1_.id = m2_.contact_id) WHERE m0_.branch_id = ? AND m0_.event_type IS
NOT NULL AND m1_.id = ? ORDER BY m0_.event_date DESC' with params
{"1":"0","2":{}}:
Catchable Fatal Error: Object of class Doctrine\ORM\Query could not be
converted to string in
F:\wamp\www\centredb\vendor\doctrine\dbal\lib\Doctrine\DBAL\Connection.php
line 1211
The two SQL statements in question are:
// Retrieve Test For That Member
$membertests = $dm->createQuery('
SELECT mt.id, mt.taken, mt.result, mtd.test, mtd.description
FROM InstructorBundle:MapTests mt
LEFT JOIN InstructorBundle:MapTestDescriptions mtd WHERE mtd.id =
mt.testDescription
WHERE mt.contact = :member'
)->setParameter('member', '40264');
$memtest = $membertests->getResult();
// Retrieve Events For That Member
$memberevents = $dm->createQuery('
SELECT mli.name, mli.created, mli.eventDate, mli.description,
mli.eventType, mc.surname, mc.firstName
FROM InstructorBundle:MapLists mli
LEFT JOIN InstructorBundle:MapListMembers mlm WHERE mlm.list = mli.id
LEFT JOIN InstructorBundle:MapContacts mc WHERE mc.id = mlm.contact
WHERE mli.branch = :centre
AND mli.eventType IS NOT NULL
AND mc.id = :member
ORDER BY mli.eventDate DESC'
)->setParameters(array(
'centre' => $centreid,
'member' => $member
));
$memevent = $memberevents->getResult();
Now, if I remove $member from the Parameters and replace it with the
record ID that I'm using during development these SQL statements work.
Obviously this isn't ideal, so to find out why these SQL statements fail
when using the same variable that the 3rd uses is vital.
The 3rd SQL statement, for reference, is:
// Retrieve Member Details
$member = $dm->createQuery('
SELECT mc.id, mc.surname, mc.firstName
FROM InstructorBundle:MapSubscriptions msu
LEFT JOIN InstructorBundle:MapContacts mc WHERE msu.contact = mc.id
LEFT JOIN InstructorBundle:MapFamilies mf WHERE mc.family = mf.id
WHERE mc.id = :member'
)->setParameter('member', $member);
I've looked at the Entity's of the two tables and the two fields that
$member is used to recover the data from. They look like this:
MapTests mt
/**
* @var \MapContacts
*
* @ORM\ManyToOne(targetEntity="MapContacts")
* @ORM\JoinColumns({
* @ORM\JoinColumn(name="contact_id", referencedColumnName="id")
* })
*/
private $contact;
/**
* Set contact
*
* @param \Acme\InstructorBundle\Entity\MapContacts $contact
* @return MapTests
*/
public function setContact(\Acme\InstructorBundle\Entity\MapContacts
$contact = null)
{
$this->contact = $contact;
return $this;
}
/**
* Get contact
*
* @return \Acme\InstructorBundle\Entity\MapContacts
*/
public function getContact()
{
return $this->contact;
}
MapContacts mc
/**
* @var integer
*
* @ORM\Column(name="id", type="integer", nullable=false)
* @ORM\Id
* @ORM\GeneratedValue(strategy="IDENTITY")
*/
private $id;
/**
* Get id
*
* @return integer
*/
public function getId()
{
return $this->id;
}
I can't figure it out, it seems fine to me. But obviously something is
stopping this from working.
No comments:
Post a Comment