How to query a view instead of a table?

[Deilan]

Let's say I have sqlite3 and an entity Person. I want to create a view PersonView with some calculable data along with the original data in Person.

1. How to query the view instead of the table with typeorm in sqlite3?
2. Is there a way to "redirect" queries to the view instead of the table?

[pleerock]

No, typeorm does not provide any additional functionality to work with views. You can create a proposal how do you see this functionality in typeorm and we can discuss it.

[progral]

A view with typorm would be a dream.

My first thoughts:

In ERM a view is just a query. A view could be a query in typeorm too.

@View('special_view_name'') //<<--optional view name
AdminUserView() => { //<<--function name = view name for database if not set in @View
  return getRepository(User)
    .createQueryBuilder("user")
    .where("user.id = :id", { id: 1 })
    .getOne();
}

This would create a view directly in your database.

We should be able to make query from a view.

getView(AdminUserView) //instead of getRepository
    .createQueryBuilder("user")
    .where("user.active = :active", { active: true })
    .getOne();

What do you think @pleerock ?

[progral]

Second: A view in an orm is most of time a subset of an entity. I think this is not the correct syntax and I do not know if we could do it with decorators this way, but you get the point:

@Entity()
export class Product {
    PrimaryColumn()
    id: number;

    Column()
    productNumber: string;

    Column()
    title: string;

    Column()
    description: string;

    Column()
    someAdminStuff: string;

    Column()
    other2: string;

    //....
}

//mobile view
@View('special_view_name') 
export class ProductView { //class name = view name if not set in @View
    id: Product.id;

    title: Product.title;

}

If this can not work we could do it similar like in relations with another decorator.

//mobile view
@View('special_view_name') 
export class ProductView { //class name = view name if not set in @View
    @ViewLink(type => Product, product => product.id) ;
    id: Product.id;

    @ViewLink(type => Product, product => product.title) ;
    title: Product.title;

   //To make it awesome we can use other views in this view
    @ViewLink(type => ProductShortDescriptionView, productShort => productShort.description) ;
    shortDescription: Product.description;}

[pleerock]

@xipheCom thanks for the feedback! First approach looks better - its flexible, cover all cases, easy to implement and does not introduce overhead.

getView(AdminUserView) //instead of getRepository
    .createQueryBuilder("user")
    .where("user.active = :active", { active: true })
    .getOne();

what other methods getView can contain? Can we do something else rather then querying with views? If no, then createQueryBuilder is enough, e.g. createQueryBuilder(UserView, "user").

Looks like easy feature to implement, are you able to contribute?

[progral]

@pleerock You are right, there are no benefits from a "getView" if we can use it as a parameter in createQueryBuilder. So we can make (even cascading or "nested") views and everyone should be happy.

Unfortunatly this is very new for me. Developing my first project with typorm just now. I have created my first decorator just a few days ago. So I can not contribute with coding in next months. But if you make the code I will write the documentation as a time saver for you. Write to me if code is ready and documentation ist needed. Topics would be:

• Views
  Introduction - Views are just queries
• Create a View
  with @view Decorator, function name is view name in database (note: naming strategy), View naming with @view('special_name')
• Use a view
  example
• Cascading / Nested views
  short sentence about nested views in general, example of using

[pleerock]

Okay, then let's see if someone else is interesting in implementing this. Its easy to implement feature. Thanks for the design proposal.

[chcas]

In my case it would be very interesting to build the result of a search without having to use QueryBuilder

[Mijago]

Any progress here?
I'd love to see this feature, so maybe i can find some time to implement it or at least do some tests.

[mdostal]

I've now worked at a number of clients that are integrating with old SAP systems and only have access to reporting views, had a poor implementation on a system and the table truly has no primary id (making all of the columns in culmination an id), or just want to be able to search and report on a view that exists. I would definitely like to see this functionality.

[einnjo]

Is it possible at all to work with views with the current version of TypeORM?

[mdostal]

Yeah, I'm able to attach to synonyms and views in MSSQL. Currently I'm attaching primary column to all columns, will see if I can limit down to true uniqueness. But this seems to be working for me.

[romanszedzielorz]

I have created normal TypeORM @entity, like for tables, but using it on MariaDB 10.3 view. No problems to make selects. Did someone tried it on PostgreSQL?

[phrohdoh]

In a few projects of mine using PostgreSQL I have tables that only have JSONB columns which of course do not have primary keys.

It would be nice if typeorm supported this out-of-the-box but while it doesn't I can of course create a "throwaway" PK column.

[maxpain]

+1

[P0oOOOo0YA]

Views are an unavoidable feature of any serious project. After two personal project now i'm going to use typeorm in production and certainly need to work with views. @pleerock What are the latest news on this feature? Is it implemented or is there any guideline or recommendation?

[P0oOOOo0YA]

@romanszedzielorz could you please elaborate more on what you have exactly done? How did you create the view in the first place? Maybe someone can create a view by queryRunner and set synchronize to false in ormconfig. Then creates an Entity decorator which mirrors the view fields and use it with selects. IMHO It is messy

[romanszedzielorz]

@P0oOOOo0YA To make any changes on database structure I'm using TypeORM Migrations. I don't use any automatic schema synchronization based on Entity files (synchronize: false). So I have created my views also this way, like regular table create. That's it.

PS. In migrations I'm using queryRunner.query(sqlQuery), so you can execute any SQL query.

[P0oOOOo0YA]

@romanszedzielorz thanks i had forgotten migrations totally :) .

[AlexMesser]

View entity implemented and will be released in 0.2.17. Here is the docs

[smtaha512]

Hi. I can not find the 0.2.17 version of typeorm mentioned above on npm.

[phrohdoh]

and will be released in 0.2.17

@smtaha512, It hasn't been published yet.

[smtaha512]

Any expected timeline?

[pleerock]

Think I'll publish it end of this week

[tzarger]

Is this still planned to be pushed at the end of this week? @pleerock

[pleerock]

awaiting few PRs to be completely reviewed and merged

[pleerock]

0.2.17 was released

[kordeviant]

quick question: for using already created views we should just delete the creation step in migrations and continue?

[Firaenix]

I'm getting errors in 0.3.0-alpha that say ViewEntity doesnt exist in TypeORM anymore. Anyone else getting this?

[gsmental]

const data = await PostgresDataSource.createQueryBuilder()
.select("*")
.from("invent.vmasterproduct", "p")
.getRawMany();

[shobhaeventhq]

Give me suggestion over use of view with typeorm , postgresql and also work with leftjoin when i have own querybuilder and and also i have persistance services and also can i will be able to use view entity with typeorm entity?