问题描述
如所述，并以不可能
i打开了一个新问题
所以我的问题就是这样：
我有一个表（ServiceProvider表）
每个服务提供商有一个OneToMany关系
的许多优惠（Offer表），每个Offer都有一个具有ManyToOne关系的主服务（Service table）。
我想要做的是让所有的服务提供商和他们的提供与serviceProvider.points相关的命令，然后serviceProvider.name，并且每个服务提供商的报价应该按照他们的要点排序。
所以为了更清晰的看法，我们说我有以下
ServiceProvider
------ -------------------------------------------
id |名称|积分|优惠|
---------------------------------------------- ---
1 | c | 2 | 1,2,3 |
---------------------------------------------- ---
2 | b | 1 | 1,2 |
---------------------------------------------- ---
3 | a | 0 | 1,3 |
---------------------------------------------- ---
ServiceOffer
-----------------------------------
id |名称|服务|
-----------------------------------
1 | a | 1 |
-----------------------------------
2 | b | 1 |
-----------------------------------
3 | c | 2 |
-----------------------------------
服务
- ---------------------------------
id |名称|积分|
-----------------------------------
1 | a | 23 |
-----------------------------------
2 | b | 88 |
-----------------------------------
我完全需要的是这样的结果
result
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）;
需要的字段
在serviceProvider
/ **
* @var ServiceOffer
*
* @ ORM\OneToMany（targetEntity =ServiceOffer，mappedBy =serviceProvider）
* /
private $ offers;
在服务中
code> / **
* @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;
@SolarBear的答案几乎是正确的。
$ query-> select（sp，offer，service）
- > from（CoreBundle ：ServiceProvider，sp）
- > andWhere（sp.city =：city_name） - > setParameter（city_name，$ cityName）
- > innerJoin（sp
- > innerJoin（offer.service，service）
- > orderBy（sp.points，DESC）
- > addOrderBy（sp.name，ASC）
- > addOrderBy（service.points，DESC）;
注意，我删除了服务Inner Join中不必要的代码。更重要的是，在 service.points 中添加DESC字符串。我们来看一下你的例子：
首先，我老实说没有'了解你在 ServiceProvider.offers 列中的地图。第二，我更改这些表的名称，以便更清楚地显示预期的结果。
ServiceProvider
------------------------------------------ -------
id |名称|积分|优惠|
---------------------------------------------- ---
1 | C | 2 | 1,2,3 |
---------------------------------------------- ---
2 | B | 1 | 1,2 |
---------------------------------------------- ---
3 | A | 0 | 1,3 |
---------------------------------------------- ---
ServiceOffer
-----------------------------------
id |名称|服务|
-----------------------------------
1 | Z | 1 |
-----------------------------------
2 | Y | 1 |
-----------------------------------
3 | X | 2 |
-----------------------------------
服务
------- ----------------------------
id |名称|积分|
-----------------------------------
1 | J | 23 |
-----------------------------------
2 | K | 88 |
-----------------------------------
结果
ServiceProvider.name | offers.name |
---------------------------------------------
C | X，Y，Z或X，Z，Y | < ----我在下面解释
------------------------------------- --------
B | Y，Z或Z，Y | < ----相同
--------------------------------------- ------
A | X，Z |
---------------------------------------------
在这个例子中，SeviceOffer X code> K （ points 88 ）。而ServiceOffer Y 和 Z 使用服务 J （ points 23 ）所以在ServiceProvider Y 和 Z ，所以数据库可以以任何顺序返回。
修改
关于优惠栏。注意你映射为一对多关系。但是请注意，在给定的表格中，例子听起来像一对多到多数。一次， ServiceProvider 有很多 ServiceOffer ，反之亦然。所以为了正常工作，你应该创建一个连接表并删除提供列。
并在ServiceProvider中映射a 。
如：
Providers_Offers
--- ---------------------------
provider_id | offer_id |
------------------------------
1 | 1 |
------------------------------
1 | 2 |
------------------------------
1 | 3 |
------------------------------
2 | 1 |
------------------------------
2 | 2 |
------------------------------
3 | 1 |
------------------------------
3 | 3 |
as described in this question and as its answered with "Not possible" i opened a new question
so my problem goes like this :I have a table (ServiceProvider Table)each service provider have many offers (Offer table) with a OneToMany relation and each Offer have a master service (Service table) with a ManyToOne relation.What i am trying to do is to get all the service providers and their offers in an order related to serviceProvider.points and then serviceProvider.name and each service provider's offers should be ordered by their points.So for more clear view let's say i have the following
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 |
-----------------------------------
what i exactly need is a result like this
results
service_provider.name| offers.name |
-------------------------------------
c | b , a |
-------------------------------------
b | a , b |
-------------------------------------
a | b , a |
-------------------------------------
this is the query i tried but it's not working
$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");
needed fields
in serviceProvider
/**
* @var ServiceOffer
*
* @ORM\OneToMany(targetEntity="ServiceOffer", mappedBy="serviceProvider")
*/
private $offers;
in service
/**
* @var integer
*
* @ORM\Column(name="points", type="integer", nullable=true)
*/
private $points;
in service offer
/**
* @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;
The @SolarBear 's answer is almost correct.
$query->select("sp, offer, service")
->from("CoreBundle:ServiceProvider","sp")
->andWhere("sp.city = :city_name")->setParameter("city_name",$cityName)
->innerJoin("sp.offers","offer")
->innerJoin("offer.service","service")
->orderBy("sp.points", "DESC")
->addOrderBy("sp.name", "ASC")
->addOrderBy("service.points","DESC");
Notice, I remove the unnecessary code in the service Inner Join. And more important, add 'DESC' string in service.points order by. It should do the trick.
Let's jump to your example :
First of all, I honestly didn't understand you map in column ServiceProvider.offers. Second, I change names of these table to be more clear to show the expected result.
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 | Z | 1 |
-----------------------------------
2 | Y | 1 |
-----------------------------------
3 | X | 2 |
-----------------------------------
Service
-----------------------------------
id | name | points |
-----------------------------------
1 | J | 23 |
-----------------------------------
2 | K | 88 |
-----------------------------------
results
ServiceProvider.name | offers.name |
---------------------------------------------
C | X, Y, Z or X, Z, Y | <---- I explain below
---------------------------------------------
B | Y, Z or Z, Y | <---- Same
---------------------------------------------
A | X, Z |
---------------------------------------------
In this example, SeviceOffer X uses Service K (points 88). While, ServiceOffer Y and Z use Service J (points 23) so there is a draw between the ServiceProvider Y and Z, so the DB can returns they in any order.
Edit
About "offers" column. Notice you mapped as a One to Many relationship. But notice, in the given table example is sound like a Many to Many. Once, ServiceProvider has many ServiceOffer and vice-versa. So for this works properly you should create a join table and removing offers column. And map in ServiceProvider a ManyToMany relationship.Such as:
Providers_Offers
------------------------------
provider_id | offer_id |
------------------------------
1 | 1 |
------------------------------
1 | 2 |
------------------------------
1 | 3 |
------------------------------
2 | 1 |
------------------------------
2 | 2 |
------------------------------
3 | 1 |
------------------------------
3 | 3 |
这篇关于MYSQL用原则排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！