Virtual Fields

The virtual property of the @Field decorator allows you to define non-persistent fields whose values are calculated at runtime using SQL or MongoDB expressions.

UQL’s virtual fields use the QueryContext pattern, so the generated SQL stays correct and cheap to produce.

import { Entity, Id, Field, ManyToMany, raw } from 'uql-orm';
import { v7 as uuidv7 } from 'uuid';

@Entity()
export class Item {
  @Id({ type: 'uuid', onInsert: uuidv7 })
  id?: string;

  @Field({ type: String })
  name?: string;

  @ManyToMany({ entity: () => Tag, through: () => ItemTag, cascade: true })
  tags?: Tag[];

  @Field({
    type: Number,
    /**
     * Define the value for a non-persistent field using a sub-query.
     */
    virtual: raw(({ ctx, dialect, escapedPrefix }) => {
      ctx.append('(');
      dialect.count(ctx, ItemTag, {
        $where: {
          itemId: raw(({ ctx }) => ctx.append(`${escapedPrefix}.id`))
        }
      }, { autoPrefix: true });
      ctx.append(')');
    })
  })
  tagsCount?: number;
}

@Entity()
export class Tag {
  @Id({ type: 'uuid', onInsert: uuidv7 })
  id?: string;

  @Field({ type: String })
  name?: string;
}

@Entity()
export class ItemTag {
  @Id({ type: 'uuid', onInsert: uuidv7 })
  id?: string;

  @Field({ references: () => Item })
  itemId?: string;

  @Field({ references: () => Tag })
  tagId?: string;
}

 

Querying with Virtual Fields

Virtual fields behave exactly like regular fields in your queries. You can select them or filter by them.

1. Selection

You write

const items = await pool.findMany(Item, {
  $select: { id: true, tagsCount: true }
});

Generated SQL (PostgreSQL)

SELECT
  "id",
  (SELECT COUNT(*) "count" FROM "ItemTag" WHERE "ItemTag"."itemId" = "id") "tagsCount"
FROM "Item"

2. Filtering

You write

const items = await pool.findMany(Item, {
  $select: { id: true },
  $where: {
    tagsCount: { $gte: 10 },
  },
});

Generated SQL (PostgreSQL)

SELECT "id" FROM "Item"
WHERE (SELECT COUNT(*) "count" FROM "ItemTag" WHERE "ItemTag"."itemId" = "id") >= $1