MYSQL排序与学说

Question

如本 question 中所述并回答“不可能” 我开了一个新问题

所以我的问题是这样的:我有一张表(ServiceProvider 表)每个服务提供商都有许多具有 OneToMany 关系的报价(报价表)每个 Offer 都有一个具有 ManyToOne 关系的主服务(服务表)。我想做的是按照与 serviceProvider.points 和 serviceProvider.name 相关的顺序获取所有服务提供商及其报价，每个服务提供商的报价应按其点数排序。所以为了更清楚的观点，假设我有以下内容

ServiceProvider
-------------------------------------------------
id      |        name     |    points    |offers|
-------------------------------------------------  
1       |        c        |      2       |1,2,3 |
-------------------------------------------------
2       |        b        |      1       |1,2   |
-------------------------------------------------
3       |        a        |      0       |1,3   |
-------------------------------------------------
ServiceOffer
-----------------------------------
id      |     name        |service|
-----------------------------------
1       |      a          |   1   |
-----------------------------------
2       |      b          |   1   |
-----------------------------------
3       |      c          |   2   |
-----------------------------------
Service
-----------------------------------
id      |    name    |    points  |
-----------------------------------
1       |      a     |      23    |
-----------------------------------
2       |      b     |      88    |
-----------------------------------

我真正需要的是这样的结果

results
service_provider.name| offers.name  |
-------------------------------------
c                    |   b , a      |
-------------------------------------
b                    |   a , b      |
-------------------------------------
a                    |   b , a      |
-------------------------------------

这是我试过的查询，但它不起作用

$query->select("sp")
            ->from("CoreBundle:ServiceProvider","sp")
            ->andWhere("sp.city = :city_name")->setParameter("city_name",$cityName)
            ->innerJoin("sp.offers","offer")
            ->innerJoin("offer.service","service","with","offer.service = service")
            ->orderBy("sp.points DESC , sp.name ASC , service.points");

需要的字段

在服务提供商中

/**
* @var ServiceOffer
*
* @ORM\OneToMany(targetEntity="ServiceOffer", mappedBy="serviceProvider")
*/
private $offers;

在职

/**
* @var integer
*
* @ORM\Column(name="points", type="integer", nullable=true)
*/
private $points;

提供服务

/**
* @ORM\ManyToOne(targetEntity="Service", inversedBy="offer")
* @ORM\JoinColumn(name="service_id", referencedColumnName="id")
*
* @Serializer\Expose
* @Serializer\Groups({"service-offer", "order-entry"})
*
* @Assert\NotBlank(message="constraint.serviceOffer.service.not_blank")
*/
private $service;

Answer 1

您不能简单地将 ORDER BY 指令枚举到 Doctrine。您可以调用单个 ->orderBy() 方法，将要使用的字段和方向(可选，ASC 是默认值)作为参数，并且进一步的订单字段必须是使用 addOrderBy 方法添加。您的查询应如下所示

$query->select("sp")
    ->from("CoreBundle:ServiceProvider","sp")
    ->andWhere("sp.city = :city_name")->setParameter("city_name",$cityName)
    ->innerJoin("sp.offers","offer")
    ->innerJoin("offer.service","service","with","offer.service = service")
    ->orderBy("sp.points", "DESC")
    ->addOrderBy("sp.name", "ASC")
    ->addOrderBy("service.points");

See the documentation on this topic.