Doctrine 在水合期间添加了额外的查询,导致“正常"的 n+1 问题.一对一和自引用关系

本文介绍了Doctrine 在水合期间添加了额外的查询,导致“正常"的 n+1 问题.一对一和自引用关系的处理方法,对大家解决问题具有一定的参考价值

问题描述

新闻通过一对多的自引用方式相互关联(一个新闻为父,可以有多个子).更重要的是,每个 NewsEventGallery 都有正常的(非自引用)一对一关系.当我运行简单的 DQL 时:

News are related to each other using one-to-many self-referencing approach (one news is parent and can have many children). What's more each News has got normal (non self-referenced) one to one relation with Event and Gallery . When I run simple DQL:

SELECT n FROM AppEntityNews n WHERE n.parent = :id

然后通过 getResults 方法和默认的 HYDRATION_OBJECT 值集水合结果,额外的查询在 getResults 方法中的某处进行.

and then hydrate results by getResults method with default HYDRATION_OBJECT value set, extra queries are made somewhere inside getResults method.

SELECT t0.* FROM event t0 WHERE t0.news_id = 2 AND ((t0.deleted_at IS NULL));
SELECT t0.* FROM gallery t0 WHERE t0.news_id = 2 AND ((t0.deleted_at IS NULL));
SELECT t0.* FROM event t0 WHERE t0.news_id = 1 AND ((t0.deleted_at IS NULL));
SELECT t0.* FROM gallery t0 WHERE t0.news_id = 1 AND ((t0.deleted_at IS NULL));

其中 news_id = 1news_id = 2 是第一次查询选择的新闻的子代.

Where news_id = 1 and news_id = 2 are children of news selected by first query.

News 也没有自引用的一对多关系(我在这里忽略了它们),但是水合不会对它们进行额外的查询.仅当 where 语句中涉及 parent 关系时,才会出现此问题.

News has got also not self-referenced one to many relations (I ignored them here), but hydration make no extra queries about them. The problem occurs only when there is parent relation involved in where statement.

如何重现

// news Entity
/**
 * @ORMEntity(repositoryClass="AppRepositoryNewsRepository")
 * @ORMTable(uniqueConstraints={@UniqueConstraint(name="news_slug_deleted", columns={"slug","deleted_at"})})
 */
class News {
    use SoftDeleteableEntity;

    /**
     * @ORMId()
     * @ORMGeneratedValue()
     * @ORMColumn(type="integer")
     */
    private $id;
    /**
     * @ORMColumn(type="string", length=255)
     */
    private $title;

    /**
     * @GedmoSlug(fields={"title"})
     * @ORMColumn(length=128)
     */
    private $slug;
/**
     * @ORMOneToOne(targetEntity="AppEntityGallery", mappedBy="news", cascade={"persist", "remove"}, orphanRemoval=true)
     *
     * @var Gallery
     */
    private $gallery;

    /**
     * @ORMOneToOne(targetEntity="AppEntityEvent", mappedBy="news", cascade={"persist", "remove"}, orphanRemoval=true)
     *
     * @var Event
     */
    private $event;
    /**
     * One News has Many News.
     * @ORMOneToMany(targetEntity="News", mappedBy="parent")
     */
    private $children;

    /**
     * Many News have One News.
     * @ORMManyToOne(targetEntity="News", inversedBy="children")
     * @ORMJoinColumn(name="parent_id", referencedColumnName="id", nullable=true)
     */
    private $parent;
}

/**
 * @ORMEntity(repositoryClass="AppRepositoryEventRepository")
 * @GedmoSoftDeleteable()
 */
class Event
{
    use SoftDeleteableEntity;

    /**
     * @ORMId()
     * @ORMGeneratedValue()
     * @ORMColumn(type="integer")
     */
    private $id;
    /**
     * @ORMOneToOne(targetEntity="AppEntityNews", inversedBy="event", cascade={"persist"})
     * @ORMJoinColumn(nullable=false)
     */
    private $news;

Gallery 实体类似于 Event 所以我在这里忽略它.

Gallery entity is simmilar to Event so I ignored it here.

// News controller

public function index(NewsRepository $newsRepository, $slug)
{
        $news = $newsRepository->findOneBy(['slug' => $slug]);
        $newsRepository->getConnectedNews($news->getId());
}

// news repository

class NewsRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, News::class);
    }
    public function getConnectedNews($newsId) {
        $query = $this->createQueryBuilder('n')->andWhere('n.parent = :id')->setParameter('id', $newsId);
        return $query->getQuery()->getResult(AbstractQuery::HYDRATE_OBJECT);
    }
}

有 20 个孩子的 news 水化最终得到:20 * 2 + 1 (n*r+1) 个查询,其中:

Hydration of news which has got 20 children ends up with: 20 * 2 + 1 (n*r+1) queries, where:

  • (n) 20 是孩子的数量
  • (r) 2 是一对一关系的个数
  • 1 是基本查询

我想防止对 Doctrine 进行的一对一关系的额外查询.是 Doctrine 错误还是不受欢迎的行为,还是我在某个地方犯了错误?

I want to prevent that extra queries to one to one relations made by Doctrine. Is it Doctrine bug or unwanted behaviour or I made a mistake somewhere?

总而言之,我只想获取所有没有一对一关系的自引用子项,因为我没有要求检索它们,所以它应该只使用一个查询来获取所有子项news 无需对每个检索到的新闻"对象进行额外查询,并且每个对象都是一对一的关系.

All in all I want to just get all self-referenced children without one-to-one relations as I didn't ask to retrieve them so it should use just one query to get all children news without making additional queries for each retrieved 'news' object and each it's one to one relation.

推荐答案

您在该实体中有一对反向 OneToOne 关系.

You have a couple inverse OneToOne relationships in that entity.

逆 OneToOne 关系不能被 Doctrine 延迟加载,并且很容易成为性能问题.

Inverse OneToOne relationships cannot be lazy-loaded by Doctrine, and can easily become a performance problem.

如果您真的需要将这些关系映射到 inverse 端(而不仅仅是在拥有端),请确保明确地进行适当的连接,或标记这些关联为 FETCH=EAGER 以便 Doctrine 为您进行连接.

If you really need to have those relationships mapped on the inverse side (and not only on the owning side) make sure to make the appropriate joins explicitly, or mark those associations as FETCH=EAGER so that Doctrine makes the joins for you.

例如一个可以避免可怕的n+1"的查询.问题是:

E.g. a query that would avoid the dreaded "n+1" problem would be:

SELECT n, g, e
    FROM AppEntityNews n
    LEFT JOIN n.gallery g
    LEFT JOIN n.event e
WHERE n.parent = :id

您可以在 这里 阅读更多关于 Doctrine 中的 N+1 问题的信息a href="https://tideways.com/profiler/blog/5-doctrine-orm-performance-traps-you-should-avoid" rel="nofollow noreferrer">这里.

这篇关于Doctrine 在水合期间添加了额外的查询,导致“正常"的 n+1 问题.一对一和自引用关系的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,WP2

admin_action_{$_REQUEST[‘action’]}

do_action( "admin_action_{$_REQUEST[‘action’]}" )动作钩子::在发送“Action”请求变量时激发。Action Hook: Fires when an ‘action’ request variable is sent.目录锚点:#说明#源码说明(Description)钩子名称的动态部分$_REQUEST['action']引用从GET或POST请求派生的操作。源码(Source)更新版本源码位置使用被使用2.6.0 wp-admin/admin.php:...

日期:2020-09-02 17:44:16 浏览:1127

admin_footer-{$GLOBALS[‘hook_suffix’]}

do_action( "admin_footer-{$GLOBALS[‘hook_suffix’]}", string $hook_suffix )操作挂钩:在默认页脚脚本之后打印脚本或数据。Action Hook: Print scripts or data after the default footer scripts.目录锚点:#说明#参数#源码说明(Description)钩子名的动态部分,$GLOBALS['hook_suffix']引用当前页的全局钩子后缀。参数(Parameters)参数类...

日期:2020-09-02 17:44:20 浏览:1032

customize_save_{$this->id_data[‘base’]}

do_action( "customize_save_{$this->id_data[‘base’]}", WP_Customize_Setting $this )动作钩子::在调用WP_Customize_Setting::save()方法时激发。Action Hook: Fires when the WP_Customize_Setting::save() method is called.目录锚点:#说明#参数#源码说明(Description)钩子名称的动态部分,$this->id_data...

日期:2020-08-15 15:47:24 浏览:775

customize_value_{$this->id_data[‘base’]}

apply_filters( "customize_value_{$this->id_data[‘base’]}", mixed $default )过滤器::过滤未作为主题模式或选项处理的自定义设置值。Filter Hook: Filter a Customize setting value not handled as a theme_mod or option.目录锚点:#说明#参数#源码说明(Description)钩子名称的动态部分,$this->id_date['base'],指的是设置...

日期:2020-08-15 15:47:24 浏览:866

get_comment_author_url

过滤钩子:过滤评论作者的URL。Filter Hook: Filters the comment author’s URL.目录锚点:#源码源码(Source)更新版本源码位置使用被使用 wp-includes/comment-template.php:32610...

日期:2020-08-10 23:06:14 浏览:903

network_admin_edit_{$_GET[‘action’]}

do_action( "network_admin_edit_{$_GET[‘action’]}" )操作挂钩:启动请求的处理程序操作。Action Hook: Fires the requested handler action.目录锚点:#说明#源码说明(Description)钩子名称的动态部分$u GET['action']引用请求的操作的名称。源码(Source)更新版本源码位置使用被使用3.1.0 wp-admin/network/edit.php:3600...

日期:2020-08-02 09:56:09 浏览:848

network_sites_updated_message_{$_GET[‘updated’]}

apply_filters( "network_sites_updated_message_{$_GET[‘updated’]}", string $msg )筛选器挂钩:在网络管理中筛选特定的非默认站点更新消息。Filter Hook: Filters a specific, non-default site-updated message in the Network admin.目录锚点:#说明#参数#源码说明(Description)钩子名称的动态部分$_GET['updated']引用了非默认的...

日期:2020-08-02 09:56:03 浏览:834

pre_wp_is_site_initialized

过滤器::过滤在访问数据库之前是否初始化站点的检查。Filter Hook: Filters the check for whether a site is initialized before the database is accessed.目录锚点:#源码源码(Source)更新版本源码位置使用被使用 wp-includes/ms-site.php:93910...

日期:2020-07-29 10:15:38 浏览:809

WordPress 的SEO 教学:如何在网站中加入关键字(Meta Keywords)与Meta 描述(Meta Description)?

你想在WordPress 中添加关键字和meta 描述吗?关键字和meta 描述使你能够提高网站的SEO。在本文中,我们将向你展示如何在WordPress 中正确添加关键字和meta 描述。为什么要在WordPress 中添加关键字和Meta 描述?关键字和说明让搜寻引擎更了解您的帖子和页面的内容。关键词是人们寻找您发布的内容时,可能会搜索的重要词语或片语。而Meta Description则是对你的页面和文章的简要描述。如果你想要了解更多关于中继标签的资讯,可以参考Google的说明。Meta 关键字和描...

日期:2020-10-03 21:18:25 浏览:1619

谷歌的SEO是什么

SEO (Search Engine Optimization)中文是搜寻引擎最佳化,意思近于「关键字自然排序」、「网站排名优化」。简言之,SEO是以搜索引擎(如Google、Bing)为曝光媒体的行销手法。例如搜寻「wordpress教学」,会看到本站的「WordPress教学:12个课程…」排行Google第一:关键字:wordpress教学、wordpress课程…若搜寻「网站架设」,则会看到另一个网页排名第1:关键字:网站架设、架站…以上两个网页,每月从搜寻引擎导入自然流量,达2万4千:每月「有机搜...

日期:2020-10-30 17:23:57 浏览:1262