CODEWITHSUNDEEP
symfonydoctrine-orm
mickburkejnr
mickburkejnr

Reputation: 3690

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.

Upvotes: 0

Views: 427

Answers (4)

dmnptr
dmnptr

Reputation: 4304

Look at examples here - http://docs.doctrine-project.org/en/latest/reference/dql-doctrine-query-language.html

Everywhere where id is used as a parameter, it is used as integer. And in database, id is always an integer. Doctrine needs your $member as an integer, whereas you are passing a numeric string. I assume that the portion of the code where Doctrine verifies parameters has some type of bug. I suggest that you report this bug at #doctrine channel on freenode.

As a solution for your code, just do typecasting for your $member variable either with intval($member) or (int)$member right in your query.

Upvotes: 0

Paul Andrieux
Paul Andrieux

Reputation: 1847

I recommend use dsl, the query language provided by Doctrine. Your second query looks like:

$dm->createQuery()
->select('mli.name, mli.created, mli.eventDate, mli.description, mli.eventType, mc.surname, mc.firstName')
->from('InstructorBundle:MapLists', 'mli')
->leftJoin('mli.list', 'mlm')
->leftJoin('mlm.contact', 'mc')
->where('mli.branch = :centre')
->andWhere('mli.eventType IS NOT NULL')
->andWhere('mc.id = :member')
->orderBy('mli.eventDate', 'DESC')
->setParameters(array(
                'centre' => $centreid,
                'member' => $member
));

Upvotes: 0

mickburkejnr
mickburkejnr

Reputation: 3690

This is a really weird issue, as the $member variable works with one query but wouldn't with the other two. However, adding this code seemed to have fixed the issue:

$memberint = intval($member);

This seemed to fix it. The errors dissappeared. However, it doesn't explain really why $member will work with one query and not the other two.

Upvotes: 0

dmnptr
dmnptr

Reputation: 4304

Is your $member variable an object? You need to use integer id as parameter in your query, so replace $member with $member->getId()

Upvotes: 1

Related Questions