Sub-Queries

project a raw expression:

import { raw } from 'nukak/util'; this.querier.findMany( Item, { $group: ['companyId'], }, { companyId: true, total: raw(({ escapedPrefix, dialect }) => `SUM(${escapedPrefix}${dialect.escapeId('salePrice')})`), } );

That ▲ code will generate this ▼ SQL:

SELECT `companyId`, SUM(`salePrice`) `total` FROM `Item` GROUP BY `companyId`

 

$filter by a raw expression:

import { raw } from 'nukak/util'; await this.querier.findMany( Item, { $filter: { $and: [{ companyId: 1 }, raw('SUM(salePrice) > 500')] }, $group: ['creatorId'], }, ['id'] );

That ▲ code will generate this ▼ SQL:

SELECT `id` FROM `Item` WHERE `companyId` = 1 AND SUM(salePrice) > 500 GROUP BY `creatorId`

 

$nexists comparison operator:

import { raw } from 'nukak/util'; await this.querier.findMany( Item, { $filter: { $nexists: raw(({ escapedPrefix, dialect }) => dialect.find( User, { $project: ['id'], $filter: { companyId: raw(escapedPrefix + dialect.escapeId(`companyId`)) }, }, { autoPrefix: true } ) ), }, }, ['id'] );

That ▲ code will generate this ▼ SQL:

SELECT `id` FROM `Item` WHERE NOT EXISTS (SELECT `User`.`id` FROM `User` WHERE `User`.`companyId` = `Item`.`companyId`)