Doctrine not allowing ResultSetMappingBuilder to work

Question

I'm using Symfony 2 with doctrine. I've set up a custom repository with a custom find function. As it joins to a subquery I'm pretty sure from what I've read that I'll need to use the ResultSetMappingBuilder to get the results back from the query. But I keep getting the following error:

The column 'id' conflicts with another column in the mapper. 

My code is:

    $sql ="SELECT c.id as cid, c.country, c.rank, c.continent, q.*
        FROM country c
        INNER JOIN (
            SELECT d.* , MAX( d.rating ) AS maxrating
            FROM ducks d
            GROUP BY d.country_id
        )q ON ( q.country_id = c.id )";

    $rsm = new \Doctrine\ORM\Query\ResultSetMappingBuilder($this->getEntityManager());
    $rsm->addRootEntityFromClassMetadata('Wfuk\DuckBundle\Entity\Country', 'c', array('id' => 'cid'));
    $rsm->addJoinedEntityFromClassMetadata('Wfuk\DuckBundle\Entity\Ducks', 'ducks', 'c', 'country_id');

    $query = $this->getEntityManager()->createNativeQuery($sql, $rsm);
    return $query->getResult();

The SQL query runs on it's own without any problems and returns the results I'd expect.

UPDATE, I've fixed the id issue by remapping the id field on the addRootEntityFromClassMetadata, but I've still got a remaining problem with the revised joinedEntity:

    $rsm->addJoinedEntityFromClassMetadata('Wfuk\DuckBundle\Entity\Ducks', 'ducks', 'c', 'country_id');

I get the following error:

Notice: Undefined index: country_id in E:\Documents\wfuk\Work\duck travels\wamp\vendor\doctrine\lib\Doctrine\ORM\Internal\Hydration\ObjectHydrator.php line 85 

I'm pretty sure this is down to the last value in the call, country_id, but I'm not 100% sure what this is. The explanation here: http://readthedocs.org/docs/doctrine-orm/en/latest/reference/native-sql.html isn't too clear and I don't fully understand the description from the class docblock.

The error it gives is:

The column 'id' conflicts with another column in the mapper. 

Yet I have renamed the id field for one of the results so the result set only has one column called 'id'.

The stacktrace on the error shows it's being thrown here:

at ResultSetMappingBuilder ->addAllClassFields ('Wfuk\DuckBundle\Entity\Ducks', 'q', array())
in E:\Documents\wfuk\Work\duck travels\wamp\vendor\doctrine\lib\Doctrine\ORM\Query\ResultSetMappingBuilder.php at line 71

I suspect it's something obvious but there's not much in the way of documentation on this function that I could find anywhere...

SOLVED:

I can't add this as an answer for another 5 hours, so to avoid anyone wasting time answering this the answer is:

For anyone who has the same problem the problem was with the 4th parameter sent on this line:

$rsm->addJoinedEntityFromClassMetadata('Wfuk\DuckBundle\Entity\Ducks', 'ducks', 'c', 'ducks');

Which should have held the field within the Country class that I was using to store the arrayCollection of ducks within it.

Answer 1

When creating ResultSetMappingBuilder specify COLUMN_RENAMING_INCREMENTrename mode for Doctrine to automatically resolve conflicts

Then in the actual query you need to use select generator:

    $rsm = new ResultSetMappingBuilder(
        $entityManager, 
        ResultSetMappingBuilder::COLUMN_RENAMING_INCREMENT
    );

    $rsm->addRootEntityFromClassMetadata("Product", "p");
    $rsm->addJoinedEntityFromClassMetadata("ProductImage", "pi", "p", "images");

    $query = $entityManager->createNativeQuery("
        SELECT " . $rsm->generateSelectClause() . " 
        FROM product p 
        JOIN product_image pi
    ", $rsm);