How to retrieve only the relation ID, not the whole entity in MikroORM?

user_13817764 0
Viewed 715

I have the following basic test entities:

@Entity()
class Author {
    @PrimaryKey()
    public id!: number;

    @Property()
    public name!: string;
}

@Entity()
class Book {
    @PrimaryKey()
    public id!: number;

    @Property()
    public title!: string;

    @ManyToOne({joinColumn: 'authorID'})
    public author!: Author;
}

What i'm trying, is to select only a single Book record, with its 'author', but I care only about its ID, I don't want to actually load the entity.

If I simply call this, it won't work (no author data loaded at all):

em.getRepository(Book).findOne({id: 1}, {fields: ['id', 'title', 'author.id']});

'author.id' doesn't do the trick, the SQL doesn't even contain the 'authorID' field.

If I add 'author' to the fields list as well, it works, author is loaded (only with the ID), but as a separate entity, with a separate, additional SQL statement! That's what I'm trying to avoid.

em.getRepository(Book).findOne({id: 1}, {fields: ['id', 'title', 'author', 'author.id']})

@1. SQL
select `b0`.`id`, `b0`.`title`, `b0`.`authorID` from `book` as `b0` where `b0`.`id` = 1 limit 1

@2. SQL (this wouldn't be neccessary as I want only the ID)
select `a0`.`id` from `author` as `a0` where `a0`.`id` in (2)

--> Result:
Book: { id: 1, title: 'a book', author: { id: 2 } }

The only way I found is to add the specific 'authorID' field too to the Book entity:

@Property()
public authorID!: number;

But, I'd like to avoid introducing these foreign key columns, it would be better to handle through the already existing and used 'author' relation (only by the 'id' property).

Does any solution exists where I could retrieve a relation's ID without generating a 2nd SELECT statement (for the relation), and even avoid introducing the foreign key (next to the already existing relation property)? Would be great to receive through the relation without any extra sql statement.
Thanks in advance.

mikro-orm
1 Answers

It is correct behaviour you see the second query, that is how population works, and the fact that you want just a single property from the entity does not change anything, you still populate the relation, and each relation will use its own query to load it. You can use LoadStrategy.JOINED if you want to use a single query. But that would still do a join for that relation, which is not needed for your use case.

Given you only want the FK to be present, you dont need to care about the target entity at all. This should do the trick too:

em.getRepository(Book).findOne(1, {
  fields: ['id', 'title', 'author'],
  populate: [],
});

This way you say you want those 3 properties to be part of what's selected from the Book entity. You already have the author property, which represents the FK. You will end up with what you want once you serialize such entity. During runtime, you will see entity reference there - an entity with just the PK. It is represented as Ref<Author> when you console.log such entity.

Note that you need that populate: [] there, as otherwise it would be inferred from your fields which contains author property, and that would trigger the full load of it.

user_36658780
Related