Doctrine - 自引用实体 - 禁用获取子项

2022-01-16 00:00:00 orm php symfony doctrine query-builder

我有一个非常简单的实体(WpmMenu)，它以自引用关系(称为相邻列表)保存相互连接的菜单项?所以在我的实体中我有:

I have a very simple entity(WpmMenu) that holds menu items connected to one another in a self-referencing relationship (adjecent list it's called)? so in my entity I have:

protected $id
protected $parent_id
protected $level
protected $name

所有的 getter/setter 的关系是:

with all the getters/setters the relationships are:

/**
* @ORMOneToMany(targetEntity="WpmMenu", mappedBy="parent")
*/
protected $children;

/**
* @ORMManyToOne(targetEntity="WpmMenu", inversedBy="children", fetch="LAZY")
* @ORMJoinColumn(name="parent_id", referencedColumnName="id", onUpdate="CASCADE", onDelete="CASCADE")
*/
protected $parent;

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

一切正常.当我渲染菜单树时，我从存储库中获取根元素，获取其子元素，然后循环遍历每个子元素，获取其子元素并递归执行此操作，直到渲染每个项目.

And everything works fine. When I render the menu tree, I get the root element from the repository, get its children, and then loop through each child, get its children and do this recursively until I have rendered each item.

会发生什么(以及我正在寻求解决方案)是这样的:目前我有 5 个 level=1 的项目，每个项目都附加了 3 个 level=2 的项目(将来我也会使用 level=3 的项目).要让我的菜单树 Doctrine 执行的所有元素:

What happens (and for what I am seeking a solution)is this: At the moment I have 5 level=1 items and each of these items have 3 level=2 items attached (and in the future I will be using level=3 items as well). To get all elements of my menu tree Doctrine executes:

  • 1 查询根元素 +
  • 1 次查询获取根元素的 5 个子元素(level=1)+
  • 5 个查询以获取每个级别 1 项目的 3 个子项(级别 = 2)+
  • 15 次查询 (5x3) 以获取每个 2 级项目的子项 (level=3)

总计:22 个查询

所以，我需要为此找到一个解决方案，理想情况下，我希望只有 1 个查询.

So, I need to find a solution for this and ideally I would like to have 1 query only.

这就是我想要做的:在我的实体存储库(WpmMenuRepository)中，我使用 queryBuilder 并获取按级别排序的所有菜单项的平面数组.获取根元素(WpmMenu)并从加载的元素数组中手动"添加其子元素.然后对孩子递归地执行此操作.这样做我可以拥有相同的树，但只需一个查询.

So this is what I am trying to do: In my entities repository(WpmMenuRepository) I use queryBuilder and get a flat array of all menu items ordered by level. Get the root element(WpmMenu) and add "manually" its children from the loaded array of elements. Then do this recursively on children. Doing this way I could have the same tree but with a single query.

这就是我所拥有的:

WpmMenuRepository:

WpmMenuRepository:

public function setupTree() {
    $qb = $this->createQueryBuilder("res");
    /** @var Array */
    $res = $qb->select("res")->orderBy('res.level', 'DESC')->addOrderBy('res.name','DESC')->getQuery()->getResult();
    /** @var WpmMenu */
    $treeRoot = array_pop($res);
    $treeRoot->setupTreeFromFlatCollection($res);
    return($treeRoot);
}

在我的 WpmMenu 实体中，我有:

and in my WpmMenu entity I have:

function setupTreeFromFlatCollection(Array $flattenedDoctrineCollection){
  //ADDING IMMEDIATE CHILDREN
  for ($i=count($flattenedDoctrineCollection)-1 ; $i>=0; $i--) {
     /** @var WpmMenu */
     $docRec = $flattenedDoctrineCollection[$i];
     if (($docRec->getLevel()-1) == $this->getLevel()) {
        if ($docRec->getParentId() == $this->getId()) {
           $docRec->setParent($this);
           $this->addChild($docRec);
           array_splice($flattenedDoctrineCollection, $i, 1);
        }
     }
  }
  //CALLING CHILDREN RECURSIVELY TO ADD REST
  foreach ($this->children as &$child) {
     if ($child->getLevel() > 0) {      
        if (count($flattenedDoctrineCollection) > 0) {
           $flattenedDoctrineCollection = $child->setupTreeFromFlatCollection($flattenedDoctrineCollection);
        } else {
           break;
        }
     }
  }      
  return($flattenedDoctrineCollection);
}

这就是发生的事情:

一切正常，但我最终每个菜单项都出现了两次.;) 现在我有 23 个查询，而不是 22 个查询.所以我实际上使情况恶化了.

Everything works out fine, BUT I end up with each menu items present twice. ;) Instead of 22 queries now I have 23. So I actually worsened the case.

我认为，真正发生的情况是，即使我添加了手动"添加的子项，WpmMenu 实体也不会被视为与数据库同步，并且一旦我对其子项执行 foreach 循环，加载就是在 ORM 加载和添加已经手动"添加的相同子级时触发.

What really happens, I think, is that even if I add the children added "manually", the WpmMenu entity is NOT considered in-sync with the database and as soon as I do the foreach loop on its children the loading is triggered in ORM loading and adding the same children that were added already "manually".

问:有没有办法阻止/禁用此行为并告诉这些实体它们与数据库同步，因此不需要额外的查询?

Q: Is there a way to block/disable this behaviour and tell these entities they they ARE in sync with the db so no additional querying is needed?

推荐答案

我松了一口气(以及大量关于 Doctrine Hydration 和 UnitOfWork 的学习)，我找到了这个问题的答案.和很多事情一样，一旦你找到答案，你就会意识到你可以用几行代码来实现这一点.我仍在测试它是否有未知的副作用，但它似乎工作正常.我在确定问题所在时遇到了很多困难 - 一旦我这样做了，就更容易找到答案.

With immense relief (and a lots of learning about Doctrine Hydration and UnitOfWork) I found the answer to this question. And as with lots of things once you find the answer you realize that you can achieve this with a few lines of code. I am still testing this for unknown side-effects but it seems to be working correctly. I had quite a lot of difficulties to identify what the problem was - once I did it was much easier to search for an answer.

所以问题是这样的:由于这是一个自引用实体，其中整个树作为元素的平面数组加载，然后通过 setupTreeFromFlatCollection 方法将它们手动输入"到每个元素的 $children 数组 -当在树中的任何实体(包括根元素)上调用 getChildren() 方法时，Doctrine(不知道这种手动"方法)将元素视为未初始化"，因此执行 SQL 以获取所有来自数据库的相关子代.

So the problem is this: Since this is a self-referencing entity where the entire tree is loaded as a flat array of elements and then they are "fed manually" to the $children array of each element by the setupTreeFromFlatCollection method - when the getChildren() method is called on any of the entities in the tree (including the root element), Doctrine (NOT knowing about this 'manual' approach) sees the element as "NOT INITIALIZED" and so executes an SQL to fetch all its related children from the database.

所以我剖析了 ObjectHydrator 类 (DoctrineORMInternalHydrationObjectHydrator) 并遵循(某种程度)脱水过程，我得到了一个 $reflFieldValue->setInitialized(true); @line:369 是 DoctrineORMPersistentCollection 类上的一个方法，设置类上的 $initialized 属性为真/假.所以我试过了，它可以工作！！！

So I dissected the ObjectHydrator class (DoctrineORMInternalHydrationObjectHydrator) and I followed (sort of) the dehydration process and I got to a $reflFieldValue->setInitialized(true); @line:369 which is a method on the DoctrineORMPersistentCollection class setting the $initialized property on the class true/false. So I tried and IT WORKS!!!

对 queryBuilder 的 getResult() 方法返回的每个实体执行 ->setInitialized(true)(使用 HYDRATE_OBJECT === ObjectHydrator)，然后在实体上调用 ->getChildren() 现在不会触发任何进一步的 SQL ！！！

Doing a ->setInitialized(true) on each of the entities returned by the getResult() method of the queryBuilder (using the HYDRATE_OBJECT === ObjectHydrator) and then calling ->getChildren() on the entities now do NOT trigger any further SQLs!!!

把它集成到WpmMenuRepository的代码中，就变成了:

Integrating it in the code of WpmMenuRepository, it becomes:

public function setupTree() {
  $qb = $this->createQueryBuilder("res");
  /** @var $res Array */
  $res = $qb->select("res")->orderBy('res.level', 'DESC')->addOrderBy('res.name','DESC')->getQuery()->getResult();
  /** @var $prop ReflectionProperty */
  $prop = $this->getClassMetadata()->reflFields["children"];
  foreach($res as &$entity) {
    $prop->getValue($entity)->setInitialized(true);//getValue will return a DoctrineORMPersistentCollection
  }
  /** @var $treeRoot WpmMenu */
  $treeRoot = array_pop($res);
  $treeRoot->setupTreeFromFlatCollection($res);
  return($treeRoot);
}

仅此而已！

相关文章