Sub-Queries
$filter
by a raw
expression:
import { raw } from 'nukak/util'; await this.querier.findMany( Item, { $project: ['id'], $filter: { $and: [{ companyId: 1 }, raw('SUM(salePrice) > 500')] } } );
That ▲ code will generate this ▼ SQL
:
SELECT `id` FROM `Item` WHERE `companyId` = 1 AND SUM(salePrice) > 500
$nexists
comparison operator:
import { raw } from 'nukak/util'; await this.querier.findMany( Item, { $project: ['id'], $filter: { $nexists: raw(({ escapedPrefix, dialect }) => dialect.find( User, { $project: ['id'], $filter: { companyId: raw(escapedPrefix + dialect.escapeId(`companyId`)) }, }, { autoPrefix: true } ) ), }, } );
That ▲ code will generate this ▼ SQL
:
SELECT `id` FROM `Item` WHERE NOT EXISTS (SELECT `User`.`id` FROM `User` WHERE `User`.`companyId` = `Item`.`companyId`)