MYSQL sorting with doctrine

Question

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;

Answer 1

You cannot simply enumerate ORDER BY directives to Doctrine. You may give a single ->orderBy() method call, giving the field to use and direction (optional, ASC is default) as arguments, and further order fields must be add using the addOrderBy method. Your query should look like

$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.

Answer 2

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      |