Mikro-orm: The fields property doesn't work with nested fields

[jsprw]

Describe the bug
For example wanting to retrieve the field ent1.prop1 with the FindOne option creates a wrong query (it has support for nested fields as it does not turn ent1.prop1 into e0.ent1.prop1 which happens when you dont specify anything before the dot).

It will create this query which does not work.
select `e0`.`id`, `ent1`.`prop1` from `ent0` as `e0` where `e0`.`id` = 9 and `e0`.`prop2` = 73 limit 1

Is it possible to have that option work the same way as the FilterQuery does?

[B4nan]

What exactly are you doing? Please add a code snippet.

[B4nan]

Is this what you are talking about?

const ret = await em.findOne(Entity1, 1, { fields: ['ent1.prop1'] });

If so, I am not planing to support this kind of auto-joining for fields, at least not in near future. The way it works now is to fire one query for each entity, so it would still mean firing 2 queries which is definitely not what you want.

Feature like this might be available once we have (optional) fetch joining implemented with result set mapper/hydrator that could handle such response.

[jsprw]

Sorry for the delay but that is exactly what I mean. Understandable that you are not supporting it, thank you for the response.

[B4nan]

Ok closing this now. Obvious workaround to achieve this is to use the query builder and map data to entities manually, something like this should work:

const qb = em.createQueryBuilder(Entity0, 'ent0');
qb.select(['ent0.*', 'ent1.prop1']).leftJoin('ent0.ent1').where({ id: 1 });
const res = await qb.execute('get'); // get the first row of raw data
const ent0 = em.map(Entity0, res); // create instance of Entity0, with reference to Entity1 under `ent1` property
ent0.ent1.prop1 = res.ent1.prop1; // or you could do `wrap(ent0.ent1).assign({ ... });` to assign multiple properties in one step

Not tested, but generally this is how you could do it. You could also remove the left join and populate ent1 manually by calling another em.findOne where you can use the fields option (which would result in 2 queries as opposed to the previous example):

const ent0 = await em.findOne(Entity0, 1); // this should also create reference of Entity1 under ent1 property
const ent1 = await em.findOne(Entity1, ent0.ent1.id, { fields: ['prop1'] }); // thanks to identity map, `ent0.ent1 === ent1`

[chrisbrantley]

@B4nan I'd like to revisit this as I'm running into a similar issue here. I'm using mikro-orm with a GraphQL server. Using the Book->Author example I have a GraphQL query that looks like this:

{
    getAllBooks() {
        id,
        title,
        publishYear,
        author {
            id,
            firstName,
            lastName
        }
}

Right now I can do this:

const books = await bookRepo.find({}, { populate: ["author"], fields: ["id", "title", "publishYear", "author"]});

This works, but it's over-fetching fields for Authors that are not needed because the second query to fetch the related authors selects *. It would be EXCELLENT if I could use the fields property to also define the fields I'd like selected on the author relationship.

I'd love to do something like this:

const books = await bookRepo.find({}, { populate: ["author"], fields: ["id", "title", "publishYear", "author.id", "author.firstName", "author.lastName"]});

Dot notation seems like a natural fit and could be parsed relatively easily assuming you have access to that property when you generate the related queries.

Admittedly I haven't looked at the code yet, but if this seems feasible to you I'd be happy to put together a PR.

Thoughts?

[B4nan]

Let's reopen this, I feel like the time for features like this has finally come.

@chrisbrantley sorry for the late response, if you are still willing to give it a try, feel free to do so!