1"use strict";(self.webpackChunkdocs=self.webpackChunkdocs||[]).push([["20972"],{29899(e,t,n){n.r(t),n.d(t,{metadata:()=>i,default:()=>m,frontMatter:()=>l,contentTitle:()=>d,toc:()=>u,assets:()=>c});var i=JSON.parse('{"id":"view-entities","title":"View Entities","description":"View entities represent actual database views that are created and managed by MikroORM\'s schema generator. Unlike virtual entities which evaluate expressions at query time, view entities create persistent CREATE VIEW statements in your database.","source":"@site/versioned_docs/version-7.2/view-entities.md","sourceDirName":".","slug":"/view-entities","permalink":"/docs/view-entities","draft":false,"unlisted":false,"editUrl":"https://github.com/mikro-orm/mikro-orm/edit/master/docs/versioned_docs/version-7.2/view-entities.md","tags":[],"version":"7.2","lastUpdatedBy":"Martin Ad\xe1mek","lastUpdatedAt":1788766630000,"frontMatter":{"title":"View Entities"},"sidebar":"docs","previous":{"title":"Virtual Entities","permalink":"/docs/virtual-entities"},"next":{"title":"Materialized Views","permalink":"/docs/materialized-views"}}'),s=n(74848),a=n(28453),r=n(50773),o=n(57250);let l={title:"View Entities"},d,c={},u=[{value:"Virtual Entities vs View Entities",id:"virtual-entities-vs-view-entities",level:2},{value:"Defining View Entities",id:"defining-view-entities",level:2},{value:"Using String Expression",id:"using-string-expression",level:3},{value:"Using QueryBuilder Expression",id:"using-querybuilder-expression",level:3},{value:"Querying View Entities",id:"querying-view-entities",level:2},{value:"Read-Only Behavior",id:"read-only-behavior",level:2},{value:"Primary Keys",id:"primary-keys",level:2},{value:"Use Cases",id:"use-cases",level:2},{value:"Supported Databases",id:"supported-databases",level:2},{value:"Materialized Views",id:"materialized-views",level:2},{value:"Limitations",id:"limitations",level:2}];function h(e){let t={a:"a",blockquote:"blockquote",code:"code",h2:"h2",h3:"h3",li:"li",ol:"ol",p:"p",pre:"pre",strong:"strong",table:"table",tbody:"tbody",td:"td",th:"th",thead:"thead",tr:"tr",ul:"ul",...(0,a.R)(),...e.components};return(0,s.jsxs)(s.Fragment,{children:[(0,s.jsxs)(t.p,{children:["View entities represent actual database views that are created and managed by MikroORM's schema generator. Unlike ",(0,s.jsx)(t.a,{href:"/docs/virtual-entities",children:"virtual entities"})," which evaluate expressions at query time, view entities create persistent ",(0,s.jsx)(t.code,{children:"CREATE VIEW"})," statements in your database."]}),"\n",(0,s.jsx)(t.h2,{id:"virtual-entities-vs-view-entities",children:"Virtual Entities vs View Entities"}),"\n",(0,s.jsxs)(t.table,{children:[(0,s.jsx)(t.thead,{children:(0,s.jsxs)(t.tr,{children:[(0,s.jsx)(t.th,{children:"Feature"}),(0,s.jsx)(t.th,{children:"Virtual Entities"}),(0,s.jsx)(t.th,{children:"View Entities"})]})}),(0,s.jsxs)(t.tbody,{children:[(0,s.jsxs)(t.tr,{children:[(0,s.jsx)(t.td,{children:"Database object"}),(0,s.jsx)(t.td,{children:"None (expression evaluated at query time)"}),(0,s.jsx)(t.td,{children:"Actual database view"})]}),(0,s.jsxs)(t.tr,{children:[(0,s.jsx)(t.td,{children:"Primary key"}),(0,s.jsx)(t.td,{children:"Not allowed"}),(0,s.jsx)(t.td,{children:"Allowed"})]}),(0,s.jsxs)(t.tr,{children:[(0,s.jsx)(t.td,{children:"Schema generation"}),(0,s.jsx)(t.td,{children:"Ignored"}),(0,s.jsxs)(t.td,{children:[(0,s.jsx)(t.code,{children:"CREATE VIEW"})," / ",(0,s.jsx)(t.code,{children:"DROP VIEW"})," generated"]})]}),(0,s.jsxs)(t.tr,{children:[(0,s.jsx)(t.td,{children:"Migrations"}),(0,s.jsx)(t.td,{children:"Not tracked"}),(0,s.jsx)(t.td,{children:"Tracked and diffed"})]}),(0,s.jsxs)(t.tr,{children:[(0,s.jsx)(t.td,{children:"Read-only"}),(0,s.jsx)(t.td,{children:"Yes"}),(0,s.jsx)(t.td,{children:"Yes"})]}),(0,s.jsxs)(t.tr,{children:[(0,s.jsx)(t.td,{children:"Use case"}),(0,s.jsx)(t.td,{children:"Dynamic queries, aggregations"}),(0,s.jsx)(t.td,{children:"Reusable views, complex queries, legacy views"})]})]})]}),"\n",(0,s.jsx)(t.h2,{id:"defining-view-entities",children:"Defining View Entities"}),"\n",(0,s.jsxs)(t.p,{children:["To define a view entity, set both ",(0,s.jsx)(t.code,{children:"view: true"})," and provide an ",(0,s.jsx)(t.code,{children:"expression"}),". The expression defines the SQL query that backs the view. Without ",(0,s.jsx)(t.code,{children:"view: true"}),", an entity with only ",(0,s.jsx)(t.code,{children:"expression"})," becomes a virtual entity (the expression is evaluated at query time with no database object created)."]}),"\n",(0,s.jsx)(t.h3,{id:"using-string-expression",children:"Using String Expression"}),"\n",(0,s.jsxs)(r.A,{groupId:"entity-def",defaultValue:"define-entity-class",values:[{label:"defineEntity + class",value:"define-entity-class"},{label:"defineEntity",value:"define-entity"},{label:"reflect-metadata",value:"reflect-metadata"},{label:"ts-morph",value:"ts-morph"}],children:[(0,s.jsx)(o.A,{value:"define-entity-class",children:(0,s.jsx)(t.pre,{children:(0,s.jsx)(t.code,{className:"language-ts",metastring:'title="./entities/AuthorStats.ts"',children:"import { defineEntity, p } from '@mikro-orm/core';\n\nconst AuthorStatsSchema = defineEntity({\n name: 'AuthorStats',\n tableName: 'author_stats_view',\n view: true,\n expression: `\n select a.name, count(b.id) as book_count\n from author a\n left join book b on b.author_id = a.id\n group by a.id, a.name\n `,\n properties: {\n name: p.string().primary(),\n bookCount: p.integer(),\n },\n});\n\nexport class AuthorStats extends AuthorStatsSchema.class {}\nAuthorStatsSchema.setClass(AuthorStats);\n"})})}),(0,s.jsx)(o.A,{value:"define-entity",children:(0,s.jsx)(t.pre,{children:(0,s.jsx)(t.code,{className:"language-ts",metastring:'title="./entities/AuthorStats.ts"',children:"import { defineEntity, p } from '@mikro-orm/core';
1\n\nexport const AuthorStats = defineEntity({\n name: 'AuthorStats',\n tableName: 'author_stats_view',\n view: true,\n expression: `\n select a.name, count(b.id) as book_count\n from author a\n left join book b on b.author_id = a.id\n group by a.id, a.name\n `,\n properties: {\n name: p.string().primary(),\n bookCount: p.integer(),\n },\n});\n"})})}),(0,s.jsx)(o.A,{value:"reflect-metadata",children:(0,s.jsx)(t.pre,{children:(0,s.jsx)(t.code,{className:"language-ts",metastring:'title="./entities/AuthorStats.ts"',children:"@Entity({\n tableName: 'author_stats_view',\n view: true,\n expression: `\n select a.name, count(b.id) as book_count\n from author a\n left join book b on b.author_id = a.id\n group by a.id, a.name\n `,\n})\nexport class AuthorStats {\n\n @PrimaryKey()\n name!: string;\n\n @Property()\n bookCount!: number;\n\n}\n"})})}),(0,s.jsx)(o.A,{value:"ts-morph",children:(0,s.jsx)(t.pre,{children:(0,s.jsx)(t.code,{className:"language-ts",metastring:'title="./entities/AuthorStats.ts"',children:"@Entity({\n tableName: 'author_stats_view',\n view: true,\n expression: `\n select a.name, count(b.id) as book_count\n from author a\n left join book b on b.author_id = a.id\n group by a.id, a.name\n `,\n})\nexport class AuthorStats {\n\n @PrimaryKey()\n name!: string;\n\n @Property()\n bookCount!: number;\n\n}\n"})})})]}),"\n",(0,s.jsx)(t.h3,{id:"using-querybuilder-expression",children:"Using QueryBuilder Expression"}),"\n",(0,s.jsx)(t.p,{children:"You can also use a callback that returns a QueryBuilder for type-safe view definitions:"}),"\n",(0,s.jsxs)(r.A,{groupId:"entity-def",defaultValue:"define-entity-class",values:[{label:"defineEntity + class",value:"define-entity-class"},{label:"defineEntity",value:"define-entity"},{label:"reflect-metadata",value:"reflect-metadata"},{label:"ts-morph",value:"ts-morph"}],children:[(0,s.jsx)(o.A,{value:"define-entity-class",children:(0,s.jsx)(t.pre,{children:(0,s.jsx)(t.code,{className:"language-ts",metastring:'title="./entities/BookSummary.ts"',children:"import { defineEntity, p } from '@mikro-orm/core';\n\nconst BookSummarySchema = defineEntity({\n name: 'BookSummary',\n tableName: 'book_summary_view',\n view: true,\n expression: (em: EntityManager) => {\n return em.createQueryBuilder(Book, 'b')\n .select(['b.title', 'a.name as author_name'])\n .join('b.author', 'a');\n },\n properties: {\n title: p.string().primary(),\n authorName: p.string(),\n },\n});\n\nexport class BookSummary extends BookSummarySchema.class {}\nBookSummarySchema.setClass(BookSummary);\n"})})}),(0,s.jsx)(o.A,{value:"define-entity",children:(0,s.jsx)(t.pre,{children:(0,s.jsx)(t.code,{className:"language-ts",metastring:'title="./entities/BookSummary.ts"',children:"import { defineEntity, p } from '@mikro-orm/core';\n\nexport const BookSummary = defineEntity({\n name: 'BookSummary',\n tableName: 'book_summary_view',\n view: true,\n expression: (em: EntityManager) => {\n return em.createQueryBuilder(Book, 'b')\n .select(['b.title', 'a.name as author_name'])\n .join('b.author', 'a');\n },\n properties: {\n title: p.string().primary(),\n authorName: p.string(),\n },\n});\n"})})}),(0,s.jsx)(o.A,{value:"reflect-metadata",children:(0,s.jsx)(t.pre,{children:(0,s.jsx)(t.code,{className:"language-ts",metastring:'title="./entities/BookSummary.ts"',children:"@Entity({\n tableName: 'book_summary_view',\n view: true,\n expression: (em: EntityManager) => {\n return em.createQueryBuilder(Book, 'b')\n .select(['b.title', 'a.name as author_name'])\n .join('b.author', 'a');\n },\n})\nexport class BookSummary {\n\n @PrimaryKey()\n title!: string;\n\n @Property()\n authorName!: string;\n\n}\n"})})}),(0,s.jsx)(o.A,{value:"ts-morph",children:(0,s.jsx)(t.pre,{children:(0,s.jsx)(t.code,{className:"language-ts",metastring:'title="./entities/BookSummary.ts"',children:"@Entity({\n tableName: 'book_summary_view',\n view: true,\n expression: (em: EntityManager) => {\n return em.createQueryBuilder(Book, 'b')\n .select(['b.title', 'a.name as author_name'])\n .join('b.author', 'a');\n },\n})\nexport class BookSummary {\n\n @PrimaryKey()\n title!: string;\n\n @Property()\n authorName!: string;\n\n}\n"})})})]}),"\n",(0,s.jsx)(t.h2,{id:"querying-view-entities",children:"Querying View Entities"}),"\n",(0,s.jsx)(t.p,{children:"View entities can be queried like any other entity:"}),"\n",(0,s.jsx)(t.pre,{children:(0,s.jsx)(t.code,{className:"language-ts",children:"// Find all\nconst stats = await em.find(AuthorStats, {});\n\n// Find with conditions\nconst prolificAuthors = await em.find(AuthorStats, {\n bookCount: { $gte: 5 },\n});\n\n// Using QueryBuilder\nconst topAuthors = await em.createQueryBuilder(AuthorStats, 'a')\n .where({ bookCount: { $gt: 0 } })\n .orderBy({ bookCount: 'desc' })\n .limit(10)\n .getResult();\n"})}),"\n",(0,s.jsx)(t.h2,{id:"read-only-behavior",children:"Read-Only Behavior"}),"\n",(0,s.jsx)(t.p,{children:"View entities are automatically marked as read-only. Attempting to persist changes to a view entity will have no effect:"}),"\n",(0,s.jsx)(t.pre,{children:(0,s.jsx)(t.code,{className:"language-ts",children:"const stat = await em.findOne(AuthorStats, { name: 'John' });\nstat.bookCount = 100; // This change won't be persisted\nawait em.flush(); // No INSERT/UPDATE generated for view entities\n"})}),"\n",(0,s.jsx)(t.h2,{id:"primary-keys",children:"Primary Keys"}),"\n",(0,s.jsx)(t.p,{children:"Unlike virtual entities, view entities can (and should) have primary keys. This allows for:"}),"\n",(0,s.jsxs)(t.ul,{children:["\n",(0,s.jsx)(t.li,{children:"Proper identity map tracking within a request"}),"\n",(0,s.jsxs)(t.li,{children:["Using ",(0,s.jsx)(t.code,{children:"findOne"})," with primary key lookups"]}),"\n",(0,s.jsx)(t.li,{children:"Referencing view entities in relations (if needed)"}),"\n"]}),"\n",(0,s.jsx)(t.pre,{children:(0,s.jsx)(t.code,{className:"language-ts",children:"@Entity({ tableName: 'my_view', view: true, expression: '...' })\nexport class MyView {\n @PrimaryKey()\n id!: number; // Primary key is allowed\n\n @Property()\n value!: string;\n}\n"})}),"\n",(0,s.jsx)(t.h2,{id:"use-cases",children:"Use Cases"}),"\n",(0,s.jsx)(t.p,{children:"View entities are ideal for:"}),"\n",(0,s.jsxs)(t.ol,{children:["\n",(0,s.jsxs)(t.li,{children:[(0,s.jsx)(t.strong,{children:"Reporting queries"}),": Pre-aggregate data for dashboards"]}),"\n",(0,s.jsxs)(t.li,{children:[(0,s.jsx)(t.strong,{children:"Legacy database views"}),": Map existing database views to entities"]}),"\n",(0,s.jsxs)(t.li,{children:[(0,s.jsx)(t.strong,{children:"Complex joins"}),": Simplify access to frequently-joined data"]}),"\n",(0,s.jsxs)(t.li,{children:[(0,s.jsx)(t.strong,{children:"Denormalized data"}),": Provide a flattened view of normalized tables"]}),"\n",(0,s.jsxs)(t.li,{children:[(0,s.jsx)(t.strong,{children:"Access control"}),": Expose limited data through views"]}),"\n"]}),"\n",(0,s.jsx)(t.h2,{id:"supported-databases",children:"Supported Databases"}),"\n",(0,s.jsx)(t.p,{children:"View entities are supported in all SQL databases:"}),"\n",(0,s.jsxs)(t.ul,{children:["\n",(0,s.jsx)(t.li,{children:"PostgreSQL"}),"\n",(0,s.jsx)(t.li,{children:"MySQL / MariaDB"}),"\n",(0,s.jsx)(t.li,{children:"SQLite"}),"\n",(0,s.jsx)(t.li,{children:"Microsoft SQL Server"}),"\n"]}),"\n",(0,s.jsxs)(t.blockquote,{children:["\n",(0,s.jsxs)(t.p,{children:["Note: MongoDB does not support view entities as it doesn't have the concept of database views. Use ",(0,s.jsx)(t.a,{href:"/docs/virtual-entities",children:"virtual entities"})," instead for MongoDB."]}),"\n"]}),"\n",(0,s.jsx)(t.h2,{id:"materialized-views",children:"Materialized Views"}),"\n",(0,s.jsx)(t.p,{children:"Materialized views are a database feature that allows you to pre-compute and store query results in a table."}),"\n",(0,s.jsxs)(t.p,{children:["MikroORM supports materialized views in PostgreSQL through view entities by setting ",(0,s.jsx)(t.code,{children:"view: { materialized: true }"}),":"]}),"\n",(0,s.jsx)(t.pre,{children:(0,s.jsx)(t.code,{className:"language-ts",children:"@Entity({\n tableName: 'author_stats_mat_view',\n view: { materialized: true },\n expression: `\n select a.name, count(b.id) as book_count\n from author a\n left join book b on b.author_id = a.id\n group by a.id, a.name\n `,\n})\nexport class AuthorStatsMatView {\n @PrimaryKey()\n name!: string;\n\n @Property()\n bookCount!: number;\n}\n"})}),"\n",(0,s.jsxs)(t.p,{children:["Read more about materialized views ",(0,s.jsx)(t.a,{href:"/docs/materialized-views",children:"in their own section"}),"."]}),"\n",(0,s.jsx)(t.h2,{id:"limitations",children:"Limitations"}),"\n",(0,s.jsxs)(t.ul,{children:["\n",(0,s.jsx)(t.li,{children:"View entities are read-only and cannot be persisted."}),"\n",(0,s.jsx)(t.li,{children:"Some databases may have limitations on updatable views."}),"\n"]})]})}function m(e={}){let{wrapper:t}={...(0,a.R)(),...e.components};return t?(0,s.jsx)(t,{...e,children:(0,s.jsx)(h,{...e})}):h(e)}}}]);
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.