WHERE Clauses

The WHERE clause is essential for filtering records in SELECT, UPDATE, and DELETE operations.
With CL ORM, you can use the OP object to construct complex queries efficiently.

The operator names eq, ne, gt, lt, gte and lte are inspired by Sequelize.
See all available operators here.

The following examples apply as well to SELECT, UPDATE and DELETE.

Single condition

OP

import { OP, useD1 } from 'cl-orm';

const { eq } = OP;
const db = useD1(env.DB);

await db.select({
  from: 'users',
  where: eq('id', 15),
});

Manual

import { useD1 } from 'cl-orm';

const db = useD1(env.DB);

await db.select({
  from: 'users',
  where: '`id` = ?',
  params: [15],
});

SQL Query

SELECT * FROM `users`
WHERE `id` = ?

-- params [15]

---

Object shorthand

You can use a plain object as a shorthand for multiple equality conditions combined with AND.

Object

import { useD1 } from 'cl-orm';

const db = useD1(env.DB);

await db.select({
  from: 'users',
  where: { status: 'active', role: 'admin' },
});

Manual

import { useD1 } from 'cl-orm';

const db = useD1(env.DB);

await db.select({
  from: 'users',
  where: '`status` = ? AND `role` = ?',
  params: ['active', 'admin'],
});

SQL Query

SELECT * FROM `users`
WHERE `status` = ? AND `role` = ?

-- params ['active', 'admin']

• Each key becomes a column and each value becomes the parameter, using equality (=).
• All conditions are joined with AND.

---

Multiple conditions

Ensure to use AND, OR, XOR between each condition.

OP.AND

import { OP, useD1 } from 'cl-orm';

const { AND, in: IN, eq } = OP;
const db = useD1(env.DB);

await db.select({
  from: 'users',
  where: AND(IN('id', [15, 30]), eq('status', 1)),
});

Array

import { OP, useD1 } from 'cl-orm';

const { in: IN, eq } = OP;
const db = useD1(env.DB);

await db.select({
  from: 'users',
  where: [IN('id', [15, 30]), 'AND', eq('status', 1)],
});

Manual

import { useD1 } from 'cl-orm';

const db = useD1(env.DB);

await db.select({
  from: 'users',
  where: '`id` IN (?, ?) AND `status` = ?',
  params: [15, 30, 1],
});

SQL Query

SELECT * FROM `users`
WHERE `id` IN (?, ?) AND `status` = ?

-- params [15, 30, 1]

---

Combine conditions

Ensure to use AND, OR, XOR between each combination.

OP.OR

import { OP, useD1 } from 'cl-orm';

const { OR, lt, eq } = OP;
const db = useD1(env.DB);

await db.select({
  from: 'users',
  where: OR(lt('age', 18), eq('status', 'locked')),
});

Array

import { OP, useD1 } from 'cl-orm';

const { lt, eq } = OP;
const db = useD1(env.DB);

await db.select({
  from: 'users',
  where: [[lt('age', 18)], 'OR', [eq('status', 'locked')]],
});

Manual

import { useD1 } from 'cl-orm';

const db = useD1(env.DB);

await db.select({
  from: 'users',
  where: '(`age` < ?) OR (`status` = ?)',
  params: [18, 'locked'],
});

SQL Query

SELECT * FROM `users`
WHERE (`age` < ?) OR (`status` = ?)

-- params [18, 'locked']

---

Combine multiple conditions

Ensure to use AND, OR, XOR between each condition and combination.

OP.AND + OP.OR

import { OP, useD1 } from 'cl-orm';

const { OR, AND, gte, lt, eq } = OP;
const db = useD1(env.DB);

await db.select({
  from: 'users',
  where: OR(
    AND(gte('age', 18), eq('status', 'locked')),
    AND(lt('age', 18), eq('status', 'enabled'))
  ),
});

Array

import { OP, useD1 } from 'cl-orm';

const { gte, lt, eq } = OP;
const db = useD1(env.DB);

await db.select({
  from: 'users',
  where: [
    [gte('age', 18), 'AND', eq('status', 'locked')],
    'OR',
    [lt('age', 18), 'AND', eq('status', 'enabled')],
  ],
});

Manual

import { useD1 } from 'cl-orm';

const db = useD1(env.DB);

await db.select({
  from: 'users',
  where: '(`age` >= ? AND `status` = ?) OR (`age` < ? AND `status` = ?)',
  params: [18, 'locked', 18, 'enabled'],
});

SQL Query

SELECT * FROM `users`
WHERE (`age` >= ? AND `status` = ?) OR (`age` < ? AND `status` = ?);

-- params [18, 'locked', 18, 'enabled']

• You can play into depth with combined conditions by encapsulating your conditions in an array. But note that this can become complex.