Comparison Operators

Comparison Operators

UQL provide a comprehensive set of operators for comparing field values. These operators are context-aware, meaning they are typed according to the field they are applied to.

Name	Description
$eq	Equal to.
$ne	Not equal to.
$lt	Less than.
$lte	Less than or equal to.
$gt	Greater than.
$gte	Greater than or equal to.
$startsWith	Starts with (case-sensitive).
$istartsWith	Starts with (case-insensitive).
$endsWith	Ends with (case-sensitive).
$iendsWith	Ends with (case-insensitive).
$includes	Contains substring (case-sensitive).
$iincludes	Contains substring (case-insensitive).
$in	Value matches any in a given array.
$nin	Value does not match any in a given array.
$text	Full-text search (where supported by the database).

 

Practical Example

import { pool } from './shared/orm.js';
import { User } from './shared/models/index.js';

const users = await pool.transaction(async (querier) => {
  return querier.findMany(User, {
    $select: { id: true, name: true },
    $where: {
      name: { $istartsWith: 'Some', $ne: 'Something' },
      age: { $gte: 18, $lte: 65 }
    },
    $sort: { name: 1 },
    $limit: 50
  });
});

Context-Aware SQL Generation

UQL transparently handles the differences between database vendors. For example, $istartsWith is translated to ILIKE in PostgreSQL, but to LOWER(field) LIKE 'some%' in MySQL.

SQL for PostgreSQL:

SELECT "id", "name" FROM "User"
WHERE "name" ILIKE 'Some%' AND "name" <> 'Something' AND "age" >= 18 AND "age" <= 65
ORDER BY "name" ASC
LIMIT 50

SQL for MySQL/SQLite:

SELECT `id`, `name` FROM `User`
WHERE LOWER(`name`) LIKE 'some%' AND `name` <> 'Something' AND `age` >= 18 AND `age` <= 65
ORDER BY `name` ASC
LIMIT 50