Querying relations

project a mandatory relation with $required: true:

@Transactional()
async function findLatestUserWithProfile(@InjectQuerier() querier?: Querier): Promise<User> {
  return querier.findOne(User,
    {
      $project: {
        id: true,
        name: true,
        profile: {
          $project: ['id', 'picture'],
          $required: true
        }
      },
      $sort: { createdAt: -1 },
    }
  );
}

That ▲ code will generate this ▼ SQL:

SELECT `User`.`id`, `User`.`name`, `profile.id`, `profile.picture`
FROM `User`
INNER JOIN `Profile` `profile` ON `profile`.`creatorId` = `User`.`id`
ORDER BY `User.createdAt` DESC

Simple query using getQuerier():

import { getQuerier } from 'nukak';

async function findLatestUserWithProfile(): Promise<User> {
  const querier = await getQuerier();
  const user = querier.findOne(
    User,
    {
      $project: {
        id: true,
        name: true,
        profile: ['id', 'picture'],
      },
      $sort: { createdAt: -1 },
    }    
  );
  await querier.release();
  return user;
}

That ▲ code will generate this ▼ SQL:

SELECT `User`.`id`, `User`.`name`, `profile.id`, `profile.picture`
FROM `User`
LEFT JOIN `Profile` `profile` ON `profile`.`creatorId` = `User`.`id`
ORDER BY `User.createdAt` DESC

More complex queries can be used, like the following:

import { Querier } from 'nukak';
import { Transactional, InjectQuerier } from 'nukak/querier';
import { Item } from './shared/models/index.js';

export class ItemService {
  @Transactional()
  async function findItems(@InjectQuerier() querier?: Querier): Promise<Item[]> {
    return querier.findMany(Item,
      {
        $project: {
          id: true,
          name: true,
          measureUnit: {
            $project: ['id', 'name'],
            $filter: { name: { $ne: 'unidad' } },
            $required: true
          },
          tax: ['id', 'name'],
        },
        $filter: { salePrice: { $gte: 1000 }, name: { $istartsWith: 'A' } },
        $sort: { tax: { name: 1 }, measureUnit: { name: 1 }, createdAt: -1 },
        $limit: 100,
      }      
    );
  }
}

export const itemService = new ItemService();

That ▲ code will generate this ▼ SQL:

SELECT `Item`.`id`, `Item`.`name`,
       `measureUnit`.`id` `measureUnit.id`, `measureUnit`.`name` `measureUnit.name`,
       `tax`.`id` `tax.id`, `tax`.`name` `tax.name`
FROM `Item`
INNER JOIN `MeasureUnit` `measureUnit` ON `measureUnit`.`id` = `Item`.`measureUnitId`
  AND `measureUnit`.`name` <> 'unidad' AND `measureUnit`.`deletedAt` IS NULL
LEFT JOIN `Tax` `tax` ON `tax`.`id` = `Item`.`taxId`
WHERE `Item`.`salePrice` >= 1000 AND LOWER(`Item`.`name`) LIKE 'a%'
ORDER BY `tax`.`name`, `measureUnit`.`name`, `Item`.`createdAt` DESC LIMIT 100