find the children of an object in the database

I have an entity account which is a classic entity with children and parents. The way I did it, the SQL relation is identified only with the parents.

What I want is to have for each object the list of its children.

In plain old PHP, I would simply loop on a mysql_fetch_array to have all my accounts, and for each one request again to the DB where parent = id so and put that in the property children (array) of my account object.

In Symfony2/doctrine, it seems I cannot do that, at least not that simply.

How am I gonna do then ?

edit: In my controller this is what I would like to do:

  $COA = $this->getDoctrine()->getRepository('NRtworksChartOfAccountsBundle:Account')->findAll();

   foreach($COA as $one_account)
   {
       echo $one_account.getChildren();

   }

But that doesn't work. When I pass this $COA to my twig I can loop on it but not in the PHP.

<?php
namespace NRtworks\ChartOfAccountsBundle\Entity;

use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;


/**
* @ORM\Entity
* @ORM\Table(name="Account")
*/

class Account
{
/**
* @ORM\Id
* @ORM\Column(type="integer")
* @ORM\GeneratedValue(strategy="AUTO")
*/

protected $id;

/**
* @ORM\Column(type="string", length=100, unique = true)
*/

protected $name;  

/**
* @ORM\Column(type="string", length=50)
*/

protected $code;

/**
* @ORM\OneToMany(targetEntity="Account", mappedBy="parent")
*/

private  $children;

/**
* @ORM\ManyToOne(targetEntity="Account", inversedBy="children")
*/

private $parent;


public function __construct()
{
    $this->children = new ArrayCollection();
}

//getter & setter    

?>

The answer

You can simply do:

$children = $account->getChildren();

Then loop over these children to get their children, etc, etc...

Or you can use DQL. Put this in a custom repository:

public function findByParent(Account $account)
{
    $dql = 'SELECT a FROM Account a WHERE a.parent = :parentId';
    $q = $this->getEntityManager()->createQuery($dql);
    $q->setParameter('parentId', $account->getId());

    return $q->getResult();
}

Then (in a controller for example) you can do:

$children = $em->getRepository('Account')->findByParent($parent);

Then loop over these children to get their children, etc, etc...

Some advice

This process isn't very efficient, especially when the tree gets large.

You should take a look at the Tree behavior of l3pp4rd's Doctrine Extensions. It uses a different setup with which you can fetch an entire tree with only 1 query.

PS: If you're using Symfony 2, you can use the StofDoctrineExtensionsBundle to integrate these Doctrine Extensions.

Answer to your edit

$one_account.getChildren() should be $one_account->getChildren(). A dot (.) in php is used to concatenate strings.