How to create View Entities?

[adlanarifzr]

I have specific queries that I want to save as views. But how to I create View Entities inside mikro-orm? I cannot find any information in the documentation.

I did find what I need in typeorm docs though.
https://orkhan.gitbook.io/typeorm/docs/view-entities

[B4nan]

Not yet supported.

[vinverdy]

@B4nan I want to help with this issue, do you have any API in mind or the same like typeorm is good?

import {ViewEntity, ViewColumn} from "typeorm";

@ViewEntity({ 
    expression: (connection: Connection) => connection.createQueryBuilder()
        .select("post.id", "id")
        .addSelect("post.name", "name")
        .addSelect("category.name", "categoryName")
        .from(Post, "post")
        .leftJoin(Category, "category", "category.id = post.categoryId")
})
export class PostCategory {

    @ViewColumn()
    id: number;

    @ViewColumn()
    name: string;

    @ViewColumn()
    categoryName: string;
}

and where should I include this? if im not mistaken, there is no knex reference on core?

[B4nan]

It should go to the core package, it should be driver independent (but end users will have the correct EM flavour, with createQueryBuilder available for SQL drivers). I am not sure why would we need something like ViewColumn, probably just to augment the possible params?

So something like this:

import { ViewEntity, ViewProperty } from '@mikro-orm/core';
import { EntityManager } from '@mikro-orm/sqlite';

@ViewEntity({ 
  expression: (em: EntityManager) => em.createQueryBuilder()...
  // or one could also do `em.execute('...')` 
  // or even use `em.aggregate()` in mongo
  // or even `em.find()` could be used probably
})
export class PostCategory {

  @ViewProperty()
  id: number;

  @ViewProperty()
  name: string;

  @ViewProperty()
  categoryName: string;

}

Not sure what are the semantics in typeorm, it looks like this does not support relationships (which kinda makes sense)? Does it actually create native database views, or is it just executing the query in the background? It should be view only, so ignored during flushing.

[vinverdy]

It should go to the core package, it should be driver independent (but end users will have the correct EM flavour, with createQueryBuilder available for SQL drivers). I am not sure why would we need something like ViewColumn, probably just to augment the possible params?

I think is needed to hydrate the properties? i would check the implementation on typeorm.

So something like this:

import { ViewEntity, ViewProperty } from '@mikro-orm/core';
import { EntityManager } from '@mikro-orm/sqlite';

@ViewEntity({ 
  expression: (em: EntityManager) => em.createQueryBuilder()...
  // or one could also do `em.execute('...')` 
  // or even use `em.aggregate()` in mongo
  // or even `em.find()` could be used probably
})
export class PostCategory {

  @ViewProperty()
  id: number;

  @ViewProperty()
  name: string;

  @ViewProperty()
  categoryName: string;

}

okay thanks

Not sure what are the semantics in typeorm, it looks like this does not support relationships (which kinda makes sense)? Does it actually create native database views, or is it just executing the query in the background? It should be view only, so ignored during flushing.

if im not mistaken, it does not support relationship. but it actually create native views, I dont familiar with mongo, so i dont know what will be the "view" on mongo.

I will look this up on the weekend, thanks

[B4nan]

I think is needed to hydrate the properties? i would check the implementation on typeorm.

Yeah well we need a decorator, my point was that @Property() could be enough. But with @ViewProperty() we could make the options typings more strict, so probably let's do it that way.

if im not mistaken, it does not support relationship. but it actually create native views, I dont familiar with mongo, so i dont know what will be the "view" on mongo.

Ok makes sense, then we just need to validate that there are only @ViewProperty() decorators used in a view entity. If this is about real sql views, then I guess we should forget about mongo for the time being, and develop it for SQL drivers first, requiring the expression to be either an instance of QB or a raw string query like in typeorm.

In general I would like to have read only entities supported across all drivers, but that should be just about ignoring such during the flush operation (rest should work as usual), so a bit different feature (that would be somewhere in the middle or regular entities and view entities). I guess we could just have a EntityMetadata.readonly: boolean, that would be also set from the @ViewEntity() automatically (and configurable from @Entity()), and based on that such entity would be ignored in change set computation.

[vinverdy]

In general I would like to have read only entities supported across all drivers, but that should be just about ignoring such during the flush operation (rest should work as usual), so a bit different feature (that would be somewhere in the middle or regular entities and view entities). I guess we could just have a EntityMetadata.readonly: boolean, that would be also set from the @ViewEntity() automatically (and configurable from @Entity()), and based on that such entity would be ignored in change set computation.

should be readonly or persist? I have seen property metadata and its use persist

[B4nan]

I'd say readonly. persist: false on properties does a bit different thing, it basically disables the property from schema related actions, e.g. it won't be part of generated schema, it will never be selected, ...

[vinverdy]

Ah okay, but readonly entity wont generate schema too right, or not?

…

On Wed, Aug 12, 2020, 14:51 Martin Adámek ***@***.***> wrote: I'd say readonly. persist: false on properties does a bit different thing, it basically disables the property from schema related actions, e.g. it won't be part of generated schema, it will never be selected, ... — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#672 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AI5BSZIXEON4U455SLTRWPTSAJCXFANCNFSM4PB4TLDQ> .

[B4nan]

I'd say it will, readonly would be just about making sure you won't be able to write to the table, otherwise it will be regular table. And the view entity (that will be readonly as well, implicitly) will generate a db view instead a table.

[vinverdy]

I have create PR for the readonly entity, is that what you expect for readonly feature?

[vinverdy]

Currently, I stuck on generate update migration for the view. Because, I cannot diff the query that user create with the one that database stored. I cant find any clean solution for this. the term how the databased store is canonical query on mysql.

I got 2 option.

1. Save the query that user create on database (typeorm does this)
2. Create temp view then diff the old with it, and update if needed. (dont like this personally)

[B4nan]

I guess 1. is fine. Where would you store the query? Some SQL comment?

[vinverdy]

I guess 1. is fine. Where would you store the query? Some SQL comment?

typeorm save it on a table called "typeorm_metadata", i look up about it. and found out, we cant add comment to view table on mysql. And sqlite does not support comment at all.

[B4nan]

I'd say not great, not terrible :] Interesting that this is not part of their docs, sounds like important thing to note...

[vinverdy]

I'd say not great, not terrible :] Interesting that this is not part of their docs, sounds like important thing to note...

There is another option, I dont like this option, but i think is better than place it on metadata table.

and, its support all platforms haha

[B4nan]

Mmmmm interesting, agreed it's better than maintaining a table. Would be good to try also postgres and sqlite to first understand whether it works fine there too. If so, I vote for that approach.

[vinverdy]

Mmmmm interesting, agreed it's better than maintaining a table. Would be good to try also postgres and sqlite to first understand whether it works fine there too. If so, I vote for that approach.

okay, I will try it, and if it works I will continue implement it. thanks

[followben]

How're you getting on with this @vinverdy?

One disadvantage I see with the comment approach is the schema generator would fail when retrofitting mikro-orm to an existing schema. Given the information_schema holds the db's canonical definition, what are the potential drawbacks of using going with the 2nd approach (diff a temp view)?

[B4nan]

@vinverdy do you have some WIP that you could share, so others could pick this up after you?

I am currently thinking about implementing #1104 for v5, which is kinda the same as this, but without all the schema diffing burden - we'll just have a QB or a raw SQL that represents given view entity.

[b-bellovic]

any update regarding ViewEntity entities? I would love to have a separate view model entity in addition to a normal entity but when I try to do so an entity is included 2 times in migration and I have to update migration manually.

[alphaofficial]

any progress on this for 2022/2023?

[MartinAndreev]

@B4nan and anyone interested, we with @veselin-angelov-lab08 managed to get it working with a small hack.

Here is how we did it.

We created an entity and flagged it as virtual,

@Entity({
  expression: (em: EntityManager) => {
    return em.createQueryBuilder('view-table-name', 'cs');
  },

and manually creating a migration to create the view. Its a hacky way but it works as a workaround.

[andrewzenkov]

any progress with View Entity in 2024?

[B4nan]

Same as last year and the two years before, no one wants to help apparently, so it waits for me :D

I'll get to this at some point during this year, but since this is easily workaroundable through the virtual entities, it didn't get much priority before v6 was out. This feature is missing only the schema diffing part basically, which you can handle via migrations yourself easily.

[andrewzenkov]

The point is that I think it's the base enough thing that should be presented in orms I believe

It's like having no native seeders with creation flow in typeorm: do we have a workaround - of course, should they add native maintenance - still yes. So the same logic is here :)

Anyway I understand your feelings, I hope v7 will bring this feature :)

[mdsystems]

For me the problem is that virtual entities do not appear to be updatable. Postgres (and other databases) allows you to create updatable views and to all intents and purposes the view acts exactly like a table. So, I would like to be able to define an entity that Mikro-orm can use, but that it does not necessarily maintain. If a virtual entity could be flagged as updatable, it would likely serve my purposes.

My process currently is to allow Mikro-orm to create my base entities and relationships and then I manually define the views (it's scriptable due to the nature of the views). Because I am unable to define a primary key on the virtual entity, I cannot update them at the moment. So, if it were possible to either define a primary key, or define a non-virtual entity that Mikro-orm does not manage, but can use, that would be enough for the time being.

Is there a way to achieve this relatively easily? I am hoping to move away from Typeorm to Mikro-orm, but this is currently the only blocker to using it.