Is it possible to include navigation property IDs in projection query?

[safrazik]

From @slawojstanislawski on September 5, 2015 11:54

Here's a link from a project built with MongoDb, angular and breezeJs:
https://github.com/johnpapa/ng-demos/blob/master/cc-bmean/src/client/app/data/repositories/repository.session.js#L65

On line 65 in the code, as part of a projection selection there are navigation properties' IDs included. So one can first download all the track, timeSlot and room entities (say, as part of some lookup query, as was provided as an example for a PHP app here: #12 (comment)), and then ask for such a projection, and since the navigation properites' entities are in the cache, one can make use of, say, session.timeSlot.name, or session.track.title, things like that, and the navigation properties are retrieved from cache appropriately for a given session (after casting the query result to session entity with toType() method).

In my sandbox model (based on EmpDirectory sample app) User entity has ManyToOne association with Job entity and a ManyToOne association with Department entity, and when I do:
breeze.EntityQuery.from('Users').toType("User")
with no projection, I get properties like jobId and departmentId in the results. When I try to use projection:
breeze.EntityQuery.from('Users').select('id, firstName, lastName, jobId').toType("User")
I get 500 server response:
Invalid select expression "X.lastName" (500 Internal Server Error)

There's clearly something I'm missing but I've been unable to pinpoint exactly what it is.

Copied from original issue: adrotec/AdrotecWebApiBundle#2

[safrazik]

jobId and departmentId are virtual properties, i.e: they are
dynamically added when serialization happenes. Projection of those
properties are not supported (yet) unless you map jobId or departmentId as
a real properties like name or id on the entity

[safrazik]

From @slawojstanislawski on September 5, 2015 13:29

I suspected something like this, so I tried doing this on the doctrine entity:

protected $jobId;

public function getJobId()
{
    return $this->getJob()->getId();
}

and then in JS, as part of a query:

.select('id, firstName, lastName, jobId')

but with no success, yet it's entirely possible it's not what you meant, I didn't dig into the code behind breeze.server.php too deeply.

I think it would be great if projection of these properties would be supported. For now I need to not use projection if I want to access navigation properties' fields - but this results in pulling all root entity's fields, and if the root entity has tens of fields (clear theory here) it's not efficient, it would be more useful to pull just partials of the root entity plus IDs of navigation properties to access their fields if navigation properties are already cached.
Do you intend to introduce such support in the future maybe?

[safrazik]

You have to map $jobId as a database column field. Map the foreign key
job_id to $jobId property. I give you an example with an XML mapping.

<field name="jobId" column="job_id" type="integer" nullable="true"/>

The library already supports this. For now this could be the only
workaround. You should map the foreign key as a normal field.

[safrazik]

From @slawojstanislawski on September 5, 2015 14:17

It worked. Just in case anyone also has this idea and uses annotations:

/** 
* @ORM\ManyToOne(targetEntity="AppBundle\Entity\Job")
* @ORM\JoinColumn(name="job_id", referencedColumnName="id")
*/
protected $job;

/**
 * @ORM\Column(type="integer", name="job_id", nullable=true)
 */
protected $jobId;

Thank you, I seriously consider using your bundle in production environment soon.