(Doctrine ORM) 如何通过多对一关系对 SELECT 进行排序

Question

我有一个数据库，其中包含了类型和类别（以及其他无关的内容）。这些类型与类别之间是多对一的关系。我想要选择所有的类型行，首先按照类别名称排序，然后按照类型权重排序，最后按照类型名称排序（全部升序）。关键部分是，我希望具有相同类别的所有类型在结果中被分组在一起。作为SQL新手，我认为一个简单的连接语句加上适当的排序语句就足够了。但是我错了，我得到的结果对我来说毫无意义。

实际结果（类型名称 - 类别名称）：

1. Aircraft Landing Strut - Aircraft 2. Aicraft Rotor Blade - Aircraft 3. Aircraft Wing - Aircraft 4. Bicycle Frame - Riding Cycles 5. Boat Bow - Watercraft 6. Boat Cargo Deck - Watercraft 7. Boulder Bottom - Rocks 8. Boulder Top - Rocks 9. Bowed Element - Others 10. Brick 1x1 w/Juice Carton Print - Bricks Printed 11. Brick 1x1 w/Milk Carton Print - Bricks Printed 12. Car Wash Brush Holder - Others 13. [等等]

期望结果（类型名称 - 类别名称）：

1. Aircraft Landing Strut - Aircraft 2. Aicraft Rotor Blade - Aircraft 3. Aircraft Wing - Aircraft 4. Brick 1x1 w/Juice Carton Print - Bricks Printed 5. Brick 1x1 w/Milk Carton Print - Bricks Printed 6. Bowed Element - Others 7. Car Wash Brush Holder - Others 8. Bicycle Frame - Riding Cycles 9. Boulder Bottom - Rocks 10. Boulder Top - Rocks 11. Boat Bow - Watercraft 12. Boat Cargo Deck - Watercraft 13. [等等]

总共有超过2000个类型行，因此上述列表显然非常缩略。没有错误。也可能很重要的是，结果被分页（这一点一直运行得非常完美）。

我正在使用Doctrine 2.5.x与我的自己的内容管理系统。在类型实体的存储库中，我使用QueryBuilder构建查询，如下所示（最大结果是全局设置，第一个结果基于当前页面编号在存储库外计算）：

$qb = $this->createQueryBuilder('t');

$qb->select('t, c'); // omitting this does not change the result
$qb->join('t.category', 'c');
$qb->addOrderBy('c.name', 'ASC'); // this does not work as expected
$qb->addOrderBy('t.weight', 'ASC');
$qb->addOrderBy('t.name', 'ASC');
$qb->setMaxResults(20);
$qb->setFirstResult(0);

return $qb->getQuery()->getResult();

生成的 SQL 语句如下：

SELECT b0_.ID AS ID_0, b0_.weight AS weight_1, b0_.name AS name_2, b0_.number AS number_3, b0_.name_alt AS name_alt_4, b0_.note AS note_5, b1_.ID AS ID_12, b1_.slug AS slug_13, b1_.name AS name_14, b1_.`desc` AS desc_15, b0_.cat_id AS cat_id_16 FROM types b0_ INNER JOIN categories b1_ ON b0_.cat_id = b1_.ID ORDER BY b1_.name ASC, b0_.weight ASC, b0_.name ASC LIMIT 20 OFFSET 0

type实体的设置如下（省略了不相关的字段）：

/**
 * @Entity(repositoryClass="Nevermind\Repository\TypeRepository")
 * @Table(name="types", options={"collate"="utf8mb4_unicode_ci", "charset"="utf8mb4"})
 */
class Type {

    /**
     * @Id
     * @Column(type="integer", name="ID")
     * @GeneratedValue
     */
    protected $id;

    /**
     * @ManyToOne(targetEntity="Category", inversedBy="types", fetch="EAGER")
     * @JoinColumn(name="cat_id", referencedColumnName="ID")
     */
    protected $category;

    /**
     * @Column(type="integer")
     */
    protected $weight = 0;

    /**
     * @Column(type="string", unique=true)
     */
    protected $name = '';

    /**
     * @Column(type="string", unique=true, length=12)
     */
    protected $number = '';

    /**
     * @Column(type="string", nullable=true)
     */
    protected $name_alt;

    /**
     * @Column(type="text", nullable=true)
     */
    protected $note;

}

而分类实体的设置如下：

/**
 * @Entity(repositoryClass="Nevermind\Repository\DefaultRepository")
 * @Table(name="categories", options={"collate"="utf8mb4_unicode_ci", "charset"="utf8mb4"})
 */
class Category {

    /**
     * @Id
     * @Column(type="integer", name="ID")
     * @GeneratedValue
     */
    protected $id;

    /**
     * @Column(type="string", unique=true)
     */
    protected $slug = '';

    /**
     * @Column(type="string", nullable=true)
     */
    protected $name;

    /**
     * @Column(type="text", name="`desc`", nullable=true)
     */
    protected $desc;

    /**
     * @OneToMany(targetEntity="Type", mappedBy="category", fetch="EXTRA_LAZY", cascade={"persist", "remove"})
     * @OrderBy({"weight"="ASC", "name"="ASC"})
     */
    protected $types;

}

TypeRepository继承DefaultRepository，而DefaultRepository又继承了Doctrine的EntityRepository。

- user11301756

---

我将SQL直接放入phpMyAdmin中，结果与我的CMS HTML完全相同（包括如果我更改OFFSET编号后的后续页面）。 - user11301756

请原谅我，但这似乎是一个很大的要求（特别是因为我的问题已经非常长了），所以你能帮助我理解从所有这些无关信息中可能获得什么吗？我希望这将是一种相对常见的选择语句，一个典型的、熟练的SQL开发人员会知道，而不是一个错误的寻找。 - user11301756

我尽力使用您的SQL语句和一些数据行进行了最佳实验，排序结果正确。因此，我想知道数据是否有问题，因此想查看您的实际行。每个表的一行就可以了。这根本不是一个常规的SQL问题（从问题的赞数可以看出）。 - MandyShaw

@MandyShaw 你是对的 - 确实是数据本身的问题。我完全忘记了我已经更改了类别名称（在显示时），使其从名称列或（如果名称列为空）从slug列转换而来。因此，将查询更改为使用 category.slug 就解决了这个问题，因为slug列永远不会为空。感谢您指引我正确的方向！ - user11301756

哦，抱歉。我猜你能看出来我是新来的 :/ - user11301756

Answer 1

奇怪的结果排序是由于类别 name 列有时是 null 导致的（当显示名称回退到 slug 列的转换内容时，我完全忘记了这个变化）。因此，将第一个 order-by 语句更改为 c.slug 就可以解决问题。天啊！

所以请记住，按可能为空的列排序会导致奇怪的结果！

- user11301756

Answer 2

你试过了吗？

$qb->select('t, c');
...
$qb->orderBy('c.name', 'ASC'); // orderBy not addOrderBy for the first clause
$qb->addOrderBy('t.weight', 'ASC');
$qb->addOrderBy('t.name', 'ASC');
...

- acucchieri

---

我没有改变输出。 - user11301756