You can use the query builder to create complex SELECT queries; including joins, subqueries and CTEs. First create a query builder instance:

// SQL: SELECT u.* FROM users u 
const query = DB.queryBuilder('u', Users)

Then continue building your query:

where

Adds a WHERE condition. Multiple calls to this method result in the conditions AND-ed together.

// SQL: SELECT u.* FROM users u 
//      WHERE u.username = u.lastName AND NOT(u.id = 5)
const query = DB.queryBuilder('u', Users)
  .where("u", "username", "=", "u", "lastName")
  .where("u", "id", Not(Eq(5)))

subquery

// SQL: SELECT u.* FROM users u 
//      WHERE u.id IN(
//        SELECT p.user_id FROM pets p
//        WHERE p.name = 'dog'
//      )
const query = DB.queryBuilder('u', Users)
  .where("u", "id", "IN", (qb) =>
    qb.from("p", Pets).where("p", "name", Eq("dog"))
  )

innerJoin

INNER JOINs a table. Does not select columns from the joined table. Supports joining by relation, condition or sql.

// SQL: SELECT u.* FROM users u 
//      INNER JOIN pets p ON p.user_id = u.id
const query = DB.queryBuilder('u', Users)
  .innerJoin("p", Pets, "u", "pets")

innerJoinAndSelect

INNER JOINs a table. Selects columns from the joined table. Supports joining by relation, condition or sql.

// SQL: SELECT u.*, p.* FROM users u 
//      INNER JOIN pets p ON p.user_id = u.id
const query = DB.queryBuilder('u', Users)
  .innerJoinAndSelect("p", Pets, "u", "pets")

innerJoinAndMap

INNER JOINs a table. Selects and maps columns from the joined table into entity result. Supports joining by relation, condition or sql.

// SQL: SELECT u.*, p.* FROM users u 
//      INNER JOIN pets p ON p.user_id = u.id
const query = DB.queryBuilder('u', Users)
  .innerJoinAndMap("p", Pets, "u", "pets")

with

Adds a CTE which you can reference later in your query. First argument is CTE name, second is CTE query.

// SQL: WITH p AS (
//        SELECT pet.user_id AS uid FROM pets p
//      )
//      SELECT u.* FROM users u 
//      WHERE u.id IN(
//        SELECT p.uid FROM p
//      )
const query = DB.queryBuilder('u', Users)
  .with("p", (qb) => qb.from("pet", Pets).select("pet", "userId", "uid"))
  .where("u", "id", "IN", (qb) => qb.from("p").select("p", "uid"))

orderBy

Adds ORDER BY clause. Default is ASC.

// SQL: SELECT u.* FROM users u 
//      ORDER BY u.last_name ASC
const query = DB.queryBuilder('u', Users)
  .orderBy("u", "lastName")

getEntities

Returns result parsed into .

// [
//   Users {
//    id: 5,
//    firstName: 'John',
//    lastName: 'Doe',
//  }
// ]
const query = DB.queryBuilder('u', Users)
  .getEntities()

getRaw

Returns result as plain objects. Field names are column name as from the SQL query. Return type is automatically generated.

// [
//  {
//    "u.id": 5,
//    "u.firstName": 'John',
//    "u.lastName": 'Doe',
//  }
// ]
const query = DB.queryBuilder('u', Users)
  .getRaw()

Concepts

Query builder

where

subquery

innerJoin

innerJoinAndSelect

innerJoinAndMap

with

orderBy

getEntities

getRaw

​where

​subquery

​innerJoin

​innerJoinAndSelect

​innerJoinAndMap

​with

​orderBy

​getEntities

​getRaw

where

subquery

innerJoin

innerJoinAndSelect

innerJoinAndMap

with

orderBy

getEntities

getRaw