Find Options

Basic Options

All Model.find* methods accept special options to query data without using QueryBuilder.

🔹 `select` – Select Specific Columns

Indicates which properties of the main object must be selected.

untitled

Users.find({
  select: {
    firstName: true,
    lastName: true,
  },
});

untitled

SELECT "firstName", "lastName" FROM "users"

🔹 `relations` – Load Related Entities

Loads related entities (shorthand for join / leftJoinAndSelect).

untitled

Users.find({
  relations: {
    profile: true,
    photos: true,
    videos: true,
  },
});

untitled

Users.find({
  relations: {
    profile: true,
    photos: true,
    videos: {
      videoAttributes: true,
    },
  },
});

untitled

SELECT * FROM "users"
LEFT JOIN "profile" ON "profile"."id" = "users"."profileId"
LEFT JOIN "photos" ON "photos"."id" = "users"."photoId"
LEFT JOIN "videos" ON "videos"."id" = "users"."videoId"

untitled

SELECT * FROM "users"
LEFT JOIN "profile" ON "profile"."id" = "users"."profileId"
LEFT JOIN "photos" ON "photos"."id" = "users"."photoId"
LEFT JOIN "videos" ON "videos"."id" = "users"."videoId"
LEFT JOIN "video_attributes" ON "video_attributes"."id" = "videos"."video_attributesId"

🔹 `where` – Filter Conditions

untitled

Users.find({
  where: {
    firstName: "Timber",
    lastName: "Saw",
  },
});

untitled

SELECT * FROM "users"
WHERE "firstName" = 'Timber' AND "lastName" = 'Saw'

Embedded entity condition:

untitled

Users.find({
  relations: {
    project: true,
  },
  where: {
    project: {
      name: "TypeORM",
      initials: "TORM",
    },
  },
});

untitled

SELECT * FROM "users"
LEFT JOIN "project" ON "project"."id" = "users"."projectId"
WHERE "project"."name" = 'TypeORM' AND "project"."initials" = 'TORM'

OR condition:

untitled

Users.find({
  where: [
    { firstName: "Timber", lastName: "Saw" },
    { firstName: "Stan", lastName: "Lee" },
  ],
});

untitled

SELECT * FROM "users"
WHERE ("firstName" = 'Timber' AND "lastName" = 'Saw')
   OR ("firstName" = 'Stan' AND "lastName" = 'Lee')

🔹 `order` – Sorting Results

untitled

Users.find({
  order: {
    name: "ASC",
    id: "DESC",
  },
});

untitled

SELECT * FROM "users"
ORDER BY "name" ASC, "id" DESC

🔹 `withDeleted` – Include Soft Deleted Entities

untitled

Users.find({
  withDeleted: true,
});

🔹 `skip` and `take` – Pagination

skip:

untitled

Users.find({
  skip: 5,
});

untitled

SELECT * FROM "users"
OFFSET 5

take:

untitled

Users.find({
  take: 10,
});

untitled

SELECT * FROM "users"
LIMIT 10

💡 skip and take should be used together for pagination.

For MSSQL (requires order):

untitled

Users.find({
  order: {
    columnName: "ASC",
  },
  skip: 0,
  take: 10,
});

untitled

SELECT * FROM "users"
ORDER BY "columnName" ASC
LIMIT 10 OFFSET 0

🔹 `cache` – Enable Caching

untitled

Users.find({
  cache: true,
});

🔹 `lock` – Query Locking (Only in `findOne` / `findOneBy`)

Optimistic Lock:

untitled

Users.findOne({
  where: {
    id: 1,
  },
  lock: { mode: "optimistic", version: 1 },
});

Pessimistic Lock (Structure):

untitled

{
  mode: "pessimistic_read" |
        "pessimistic_write" |
        "dirty_read" |
        "pessimistic_partial_write" | // Deprecated
        "pessimistic_write_or_fail" | // Deprecated
        "for_no_key_update" |
        "for_key_share",
  tables: string[],
  onLocked: "nowait" | "skip_locked"
}

📖 See lock modes for more details.

🔹 Full Example

untitled

Users.find({
  select: {
    firstName: true,
    lastName: true,
  },
  relations: {
    profile: true,
    photos: true,
    videos: true,
  },
  where: {
    firstName: "Timber",
    lastName: "Saw",
    profile: {
      userName: "tshaw",
    },
  },
  order: {
    name: "ASC",
    id: "DESC",
  },
  skip: 5,
  take: 10,
  cache: true,
});

🔹 Find Without Arguments

untitled

Users.find();

untitled

SELECT * FROM "users"

Advanced options

Alapa Model provides a lot of built-in operators that can be used to create more complex comparisons:

🔹 `Not`

untitled

import { Not } from "alapa";

const loadedPosts = await Posts.findBy({
  title: Not("About #1"),
});

will execute following query:

SELECT * FROM "posts" WHERE "title" != 'About #1'

🔹 `LessThan`

untitled

import { LessThan } from "alapa";

const loadedPosts = await Posts.findBy({
  likes: LessThan(10),
});

will execute following query:

untitled

SELECT * FROM "posts" WHERE "likes" < 10

🔹 `LessThanOrEqual`

untitled

import { LessThanOrEqual } from "alapa";

const loadedPosts = await Posts.findBy({
  likes: LessThanOrEqual(10),
});

will execute following query:

untitled

SELECT * FROM "posts" WHERE "likes" <= 10

🔹 `MoreThan`

untitled

import { MoreThan } from "alapa";

const loadedPosts = await Posts.findBy({
  likes: MoreThan(10),
});

will execute following query:

untitled

SELECT * FROM "posts" WHERE "likes" > 10

🔹 `MoreThanOrEqual`

untitled

import { MoreThanOrEqual } from "alapa";

const loadedPosts = await Posts.findBy({
  likes: MoreThanOrEqual(10),
});

will execute following query:

untitled

SELECT * FROM "posts" WHERE "likes" >= 10

🔹 `Equal`

untitled

import { Equal } from "alapa";

const loadedPosts = await Posts.findBy({
  title: Equal("About #2"),
});

will execute following query:

untitled

SELECT * FROM "posts" WHERE "title" = 'About #2'

🔹 `Like`

untitled

import { Like } from "alapa";

const loadedPosts = await Posts.findBy({
  title: Like("%out #%"),
});

will execute following query:

untitled

SELECT * FROM "posts" WHERE "title" LIKE '%out #%'

🔹 `ILike`

untitled

import { ILike } from "alapa";

const loadedPosts = await Posts.findBy({
  title: ILike("%out #%"),
});

will execute following query:

untitled

SELECT * FROM "posts" WHERE "title" ILIKE '%out #%'

🔹 `Between`

untitled

import { Between } from "alapa";

const loadedPosts = await Posts.findBy({
  likes: Between(1, 10),
});

will execute following query:

untitled

SELECT * FROM "posts" WHERE "likes" BETWEEN 1 AND 10

🔹 `In`

untitled

import { In } from "alapa";

const loadedPosts = await Posts.findBy({
  title: In(["About #2", "About #3"]),
});

will execute following query:

untitled

SELECT * FROM "posts" WHERE "title" IN ('About #2','About #3')

🔹 `Any`

untitled

import { Any } from "typeorm";

const loadedPosts = await Posts.findBy({
  title: Any(["About #2", "About #3"]),
});

will execute following query:

untitled

SELECT * FROM "posts" WHERE "title" = ANY(['About #2','About #3'])

🔹 `IsNull`

untitled

import { IsNull } from "typeorm";

const loadedPosts = await Posts.findBy({
  title: IsNull(),
});

will execute following query:

untitled

SELECT * FROM "posts" WHERE "title" IS NULL

🔹 `ArrayContains`

untitled

import { ArrayContains } from "typeorm";

const loadedPosts = await Posts.findBy({
  categories: ArrayContains(["TypeScript"]),
});

will execute following query:

untitled

SELECT * FROM "posts" WHERE "categories" @> '{TypeScript}'

🔹 `ArrayContainedBy`

untitled

import { ArrayContainedBy } from "typeorm";

const loadedPosts = await Posts.findBy({
  categories: ArrayContainedBy(["TypeScript"]),
});

will execute following query:

untitled

SELECT * FROM "posts" WHERE "categories" <@ '{TypeScript}'

🔹 `ArrayOverlap`

untitled

import { ArrayOverlap } from "typeorm";

const loadedPosts = await Posts.findBy({
  categories: ArrayOverlap(["TypeScript"]),
});

will execute following query:

untitled

SELECT * FROM "posts" WHERE "categories" && '{TypeScript}'

🔹 `Raw`

untitled

import { Raw } from "typeorm";

const loadedPosts = await Posts.findBy({
  likes: Raw("dislikes - 4"),
});

will execute following query:

untitled

SELECT * FROM "posts" WHERE "likes" = "dislikes" - 4

In the simplest case, a raw query is inserted immediately after the equal symbol. But you can also completely rewrite the comparison logic using the function.

untitled

import { Raw } from "typeorm";

const loadedPosts = await Posts.findBy({
  currentDate: Raw((alias) => `${alias} > NOW()`),
});

will execute following query:

untitled

SELECT * FROM "posts" WHERE "currentDate" > NOW()

If you need to provide user input, you should not include the user input directly in your query as this may create a SQL injection vulnerability. Instead, you can use the second argument of the Raw function to provide a list of parameters to bind to the query.

untitled

import { Raw } from "typeorm";

const loadedPosts = await Posts.findBy({
  currentDate: Raw((alias) => `${alias} > :date`, { date: "2020-10-06" }),
});

will execute following query:

untitled

SELECT * FROM "posts" WHERE "currentDate" > '2020-10-06'

If you need to provide user input that is an array, you can bind them as a list of values in the SQL statement by using the special expression syntax:

untitled

import { Raw } from "typeorm";

const loadedPosts = await Posts.findBy({
  title: Raw((alias) => `${alias} IN (:...titles)`, {
    titles: ["Go To Statement Considered Harmful", "Structured Programming"],
  }),
});

will execute following query:

untitled

SELECT * FROM "posts" WHERE "title" IN ('Go To Statement Considered Harmful', 'Structured Programming')

Combining Advanced Options

Also you can combine these operators with below:

🔹 `Not`

untitled

import { Not, MoreThan, Equal } from "typeorm";

const loadedPosts = await Posts.findBy({
  likes: Not(MoreThan(10)),
  title: Not(Equal("About #2")),
});

will execute following query:

untitled

SELECT * FROM "posts" WHERE NOT("likes" > 10) AND NOT("title" = 'About #2')

🔹 `Or`

untitled

import { Or, Equal, ILike } from "typeorm";

const loadedPosts = await Posts.findBy({
  title: Or(Equal("About #2"), ILike("About%")),
});

will execute following query:

untitled

SELECT * FROM "posts" WHERE "title" = 'About #2' OR "title" ILIKE 'About%'

🔹 `And`

untitled

import { And, Not, Equal, ILike } from "typeorm";

const loadedPosts = await Posts.findBy({
  title: And(Not(Equal("About #2")), ILike("%About%")),
});

will execute following query:

untitled

SELECT * FROM "posts" WHERE NOT("title" = 'About #2') AND "title" ILIKE '%About%'

Basic Options​

🔹 select – Select Specific Columns​

🔹 relations – Load Related Entities​

🔹 where – Filter Conditions​

🔹 order – Sorting Results​

🔹 withDeleted – Include Soft Deleted Entities​

🔹 skip and take – Pagination​

🔹 cache – Enable Caching​

🔹 lock – Query Locking (Only in findOne / findOneBy)​

🔹 Full Example​

🔹 Find Without Arguments​

Advanced options​

🔹 Not​

🔹 LessThan​

🔹 LessThanOrEqual​

🔹 MoreThan​

🔹 MoreThanOrEqual​

🔹 Equal​

🔹 Like​

🔹 ILike​

🔹 Between​

🔹 In​

🔹 Any​

🔹 IsNull​

🔹 ArrayContains​

🔹 ArrayContainedBy​

🔹 ArrayOverlap​

🔹 Raw​

Combining Advanced Options​

🔹 Not​

🔹 Or​

🔹 And​

Basic Options

🔹 `select` – Select Specific Columns

🔹 `relations` – Load Related Entities

🔹 `where` – Filter Conditions

🔹 `order` – Sorting Results

🔹 `withDeleted` – Include Soft Deleted Entities

🔹 `skip` and `take` – Pagination

🔹 `cache` – Enable Caching

🔹 `lock` – Query Locking (Only in `findOne` / `findOneBy`)

🔹 Full Example

🔹 Find Without Arguments

Advanced options

🔹 `Not`

🔹 `LessThan`

🔹 `LessThanOrEqual`

🔹 `MoreThan`

🔹 `MoreThanOrEqual`

🔹 `Equal`

🔹 `Like`

🔹 `ILike`

🔹 `Between`

🔹 `In`

🔹 `Any`

🔹 `IsNull`

🔹 `ArrayContains`

🔹 `ArrayContainedBy`

🔹 `ArrayOverlap`

🔹 `Raw`

Combining Advanced Options

🔹 `Not`

🔹 `Or`

🔹 `And`