Add virtual column for order by it but does not return it

Question

I have an entity which has a point (postgis) property.I need to return a collection of that entity ordered by the distance between coordinates of user and the point of entity.

For this, Im adding an aggregate function for calculate that distance and add it in ORDER BY, but I dont want to return it. I only need that return and array of objects of entity.

Without order by, the result is:

[
  {
    "user": "/api/users/1",
    "id": 1,
    "gender": "MALE",
    "createdAt": "2019-04-05T11:03:03+02:00",
    "updateAt": "2019-04-11T11:34:06+02:00",
    "birthdate": "1991-05-13T08:02:32+02:00",
    "deletedAt": null,
    "town": "Miami"
  },
  {
    "user": "/api/users/3",
    "id": 2,
    "gender": "MALE",
    "createdAt": "2019-04-05T13:59:30+02:00",
    "updateAt": "2019-04-11T10:57:40+02:00",
    "birthdate": "1999-04-05T11:48:46+02:00",
    "deletedAt": null,
    "town": "New York"
  },
  {
    "user": "/api/users/7",
    "id": 3,
    "gender": "MALE",
    "createdAt": "2019-04-11T11:11:03+02:00",
    "updateAt": "2019-04-11T11:11:03+02:00",
    "birthdate": "1991-05-13T08:02:32+02:00",
    "deletedAt": null,
    "town": "New York"
  }
]

When I add the next code for ORDER BY disntace (calculated between coordinates of user and the point)

$queryBuilder
    ->addSelect("ST_Distance(o.point, ST_Point(:longitude,:latitude)) AS distance")
    ->addOrderBy("distance", "ASC")
    ->setParameter("longitude", $longitude)
    ->setParameter("latitude", $latitude)
;

I get:

[
  {
    "0": {
        "user": "/api/users/1",
        "id": 1,
        "gender": "MALE",
        "createdAt": "2019-04-05T11:03:03+02:00",
        "updateAt": "2019-04-11T11:34:06+02:00",
        "birthdate": "1991-05-13T08:02:32+02:00",
        "deletedAt": null,
        "town": "Miami"
    },
    "distance": "106496.35623204"
  },
  {
    "0": {
        "user": "/api/users/7",
        "id": 3,
        "gender": "MALE",
        "createdAt": "2019-04-11T11:11:03+02:00",
        "updateAt": "2019-04-11T11:11:03+02:00",
        "birthdate": "1991-05-13T08:02:32+02:00",
        "deletedAt": null,
        "town": "New York"
    },
    "distance": "109073.2944295"
  },
  {
    "0": {
        "user": "/api/users/3",
        "id": 2,
        "gender": "MALE",
        "createdAt": "2019-04-05T13:59:30+02:00",
        "updateAt": "2019-04-11T10:57:40+02:00",
        "birthdate": "1999-04-05T11:48:46+02:00",
        "deletedAt": null,
        "town": "New York"
    },
    "distance": "285892.32591062"
  }
]

I need to the result seem like the 1st json. It is possible to add ORDER BY but remove/hide the distance property?

Answer 1

As of doctrine 2.2, Scalar mappings can now be omitted from DQL result,

using the HIDDEN keyword, so to omit the computed field from the result:

->addSelect("ST_Distance(o.point, ST_Point(:longitude,:latitude)) AS HIDDEN distance")

DQL select expressions documentation

DQL examples