Symfony Doctrine joins a one-to-many relationship

advertisements

I want to join two tables together to get retrieve all the entities in one search. I have a one to many relationship: Jedi and Member. One member group can have many Jedi's. I want to make a form to search by one entity and displays all of the information as a whole.

Example: Search for a Jedi by its assigned color -> retrieving name, age, gender, color, and rank.

Jedi.php:

class Jedi
{
/**
 * @var integer
 *
 * @ORM\Column(name="id", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private $id;

/**
 * @var string
 *
 * @ORM\Column(name="name", type="string", length=255)
 */
private $name;

/**
 * @var integer
 *
 * @ORM\Column(name="age", type="integer")
 */
private $age;

/**
 * @var string
 *
 * @ORM\Column(name="gender", type="string", length=255)
 */
private $gender;

/**
 *
 * @ORM\ManyToOne(targetEntity="Member", inversedBy="jedi")
 * @ORM\JoinColumn(name="member", referencedColumnName="id")
 */
private $member;

Member.php:

class Member
{
/**
 * @var integer
 *
 * @ORM\Column(name="id", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private $id;

/**
 * @var string
 *
 * @ORM\Column(name="rank", type="string", length=255)
 */
private $rank;

/**
 * @var string
 *
 * @ORM\Column(name="color", type="string", length=255)
 */
private $color;

/**
 * @var integer
 * @ORM\OneToMany(targetEntity="Jedi", mappedBy="member")
 *
 */
protected $jedi;

In my JediRepository.php:

class JediRepository extends EntityRepository
{

public function findJediByColor($color) {

    $query = $this->getEntityManager()
        ->createQuery(
            'SELECT color, rank
             FROM YodaBundle:Member member
             JOIN member.jedi
             WHERE member.color = :color'
        )->setParameter('color', $color);

    try {
        return $query->getSingleResult();
    } catch (\Doctrine\ORM\NoResultException $e) {
        return null;
    }

 }
}

Now I am pretty sure I probably have the query join statement all wrong and I'm quite confused on how to to go about using it. Any help would be appreciated!


You have a mistake in your code. Your query retrieve many rows but you get it as single row (getSingleResult()). As you can see in documentation (http://docs.doctrine-project.org/en/latest/reference/dql-doctrine-query-language.html#query-result-formats) getSingleResult() will throw an exception every time when many rows will satisfy your query. Also if you need all the columns of your entity you don't need to specify each in the query. You can just specify entity you want to retrieve:

$query = $this->getEntityManager()
    ->createQuery(
        'SELECT member, jedi
         FROM YodaBundle:Member member
         JOIN member.jedi
         WHERE member.color = :color'
    )->setParameter('color', $color);

try {
    return $query->getResult();
} catch (\Doctrine\ORM\NoResultException $e) {
    return null;
}

Notice that in the Select statement I use member and jedi. In this case Doctrine will use eager joining (all data in one query). If you only need select members and for some of them retrieve jedis you can omit jedi in Select part of query.