英文:
How to optimize a query from N+1 without breaking the data in doctrine2?
问题
以下是翻译好的部分：
有以下表格：国家、签证、大使馆。
（一个国家可以有多个签证和大使馆）。
我进行了一个查询，获取了几个国家的记录：
$currencies = $this->entityManager
->createQueryBuilder()
->select('c')
->from(Country::class, 'c')
->setMaxResults($condition->first)
->setFirstResult($condition->offset)
->getQuery()
->getResult();
好的，一切都正常。
此外，当我们在循环中使用 $currencies 并获取签证和大使馆时，我们遇到了N + 1查询的问题。在我的情况下，一次有200多个查询。
让我们尝试使用连接来解决这个问题：
$currencies = $this->entityManager
->createQueryBuilder()
->select('c')
->from(Country::class, 'c')
->leftJoin('c.visas', 'cv')
->addSelect('cv')
->leftJoin('c.embassies', 'ce')
->addSelect('ce')
->setMaxResults($condition->first)
->setFirstResult($condition->offset)
->getQuery()
->getResult();
因此，这解决了N + 1的问题，将200多个查询转化为4个查询，但随之出现了以下问题：
例如，在我们的表格（或根据查询条件找到的表格）中有1个国家记录，但是这个国家，例如，在数据库中有3个签证和5个大使馆记录。
因此，在使用连接的情况下，当找到1个国家时，签证和大使馆的关系也将返回1条记录（并且不管可能有更多记录）。
这种行为是由于左连接引起的，一方面是有200多个查询，另一方面是如果国家记录少于可能的关系，则返回的数据不完整。
我该如何解决这个问题？
英文:
There are the following tables: countries, visas, embassies.
(one country can have many visas and embassies).
I make a query that gets several countries records:
$currencies = $this->entityManager
->createQueryBuilder()
->select('c')
->from(Country::class, 'c')
->setMaxResults($condition->first)
->setFirstResult($condition->offset)
->getQuery()
->getResult();
ok, everything works.
further, when we work with $currencies in a loop and get visas and embassises, we get a problem with N + 1 queries. In my case it is 200+ queries at a time.
Let's try to solve the problem with join:
$currencies = $this->entityManager
->createQueryBuilder()
->select('c')
->from(Country::class, 'c')
->leftJoin('c.visas', 'cv')
->addSelect('cv')
->leftJoin('c.embassies', 'ce')
->addSelect('ce')
->setMaxResults($condition->first)
->setFirstResult($condition->offset)
->getQuery()
->getResult();
so this solves the problem with N+1, turning 200+ queries into 4 queries, but the following problem appears:
for example, in our table (or found by query condition) there is 1 country record, but this country, for example, has 3 visas and 5 embassies records in the database.
So, in the case of using join, when 1 country is found, then the relations on visas and embassises will also return 1 record (and it doesn’t matter that there may be more of them).
This behavior is due to leftJoin, and on the one hand - these are 200+ queries and on the other hand - incomplete data return if there are fewer countries records than possible relations.
How can I solve this problem?
答案1
得分: 1
你不能使用 join 解决这个问题，你应该使用 WHERE IN 运算符进行其他查询来获取额外的数据。首先，获取你的国家，获得它们的 id 数组，例如 $ids，然后进行如下查询：
$queryBuilder->where('embassies.country_id IN (:ids)')
->setParameter('ids', $ids)
然后对签证也做同样的事情。这样，你可以用 3 次查询获取所有数据，而不是 200+ 次查询。
英文:
You can't solve it with join, you should make other queries for additional data using WHERE IN operator. First you get your countries, get array of their id, for example $ids, and then make query like
$queryBuilder->where('embassies.country_id IN (:ids)')
->setParameter('ids', $ids)
And do the same thing for visas. So instead of 200+ queries you can get all the data with 3.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论