find the children of an object in the database

Question

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    

?>

Have you tried to loop over the `Account`s? Calling `getChildren()` on each `Account` should give you the children (if the association mapping is correct ; the toolbar will display an error if not). What is the context, in a repository, in a controller or in a Twig template? — A.L, Jan 29 '14 at 15:20
thanks that's good to know. have a look at my edit, my problem is that I don't know how to loop on my accounts — Eagle1, Jan 29 '14 at 18:51
`$one_account.getChildren()` should be `$one_account->getChildren()`. — Jasper N. Brouwer, Jan 29 '14 at 19:51

Jasper N. Brouwer · Accepted Answer · 2014-01-30T08:25:05.510

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.

hey thanks for that. I've put an edit to my question. I realized there is something else I don't know how to do :) ps: I had already installed StofDoctrineExtensionBundle and I'm trying it also. But truth is I need to try basic stuff on my own too in order to understand the basics. — Eagle1, Jan 29 '14 at 18:53
@jasper-n-brouwer hi sorry to call on you again but nobody seems to have that answer. I finally turned to StofDoctrineExtension, which is very handy to get the children of the tree. However the problem is that I can't get the parents now :( http://stackoverflow.com/questions/21538095/stofdoctrineextension-tree-entity-get-the-id-of-the-parent-in-twig — Eagle1, Feb 05 '14 at 08:36

find the children of an object in the database

1 Answers1

Linked