Advanced Filtering

The list operation accepts a JSON serializable filters object that gets converted to SQL WHERE conditions. Root-level properties are combined with AND logic, while nested OR/AND arrays allow for complex boolean expressions.

Basic Filters

Simple property-value filters:

const users = await userCrud.list({
  filters: {
    isActive: true,
    role: 'admin',
  },
})

This translates to: WHERE isActive = true AND role = 'admin'

Filter Operators

Use operator objects for more complex conditions:

const users = await userCrud.list({
  filters: {
    age: { op: 'gte', value: 18 },               // age >= 18
    status: { op: 'in', value: ['active', 'pending'] }, // status IN (...)
    name: { op: 'ilike', value: '%john%' },      // case-insensitive LIKE
    createdAt: { op: 'lt', value: new Date() },  // created before now
  },
})

Available Operators

Operator	SQL Equivalent	Description
`eq`	`=`	Equal to
`ne`	`!=`	Not equal to
`gt`	`>`	Greater than
`gte`	`>=`	Greater than or equal
`lt`	`<`	Less than
`lte`	`<=`	Less than or equal
`in`	`IN`	In array of values
`like`	`LIKE`	Pattern matching (case-sensitive)
`ilike`	`ILIKE`	Pattern matching (case-insensitive)

Boolean Logic

OR Conditions

Use OR arrays for alternative conditions:

const users = await userCrud.list({
  filters: {
    OR: [
      { name: 'John' },
      { name: 'Jane' },
    ],
  },
})

This translates to: WHERE (name = 'John' OR name = 'Jane')

AND Conditions

Use AND arrays for additional required conditions:

const users = await userCrud.list({
  filters: {
    AND: [
      { isActive: true },
      { role: 'admin' },
    ],
  },
})

This translates to: WHERE (isActive = true AND role = 'admin')

Complex Nested Filters

Combine multiple levels of boolean logic:

const users = await userCrud.list({
  filters: {
    isActive: true,
    OR: [
      {
        AND: [
          { role: 'admin' },
          { department: 'engineering' },
        ],
      },
      {
        role: 'owner',
      },
    ],
  },
})

This translates to:

WHERE isActive = true 
  AND (
    (role = 'admin' AND department = 'engineering')
    OR role = 'owner'
  )

Date Range Filters

Filter by date ranges using comparison operators:

const posts = await postCrud.list({
  filters: {
    createdAt: {
      gte: new Date('2024-01-01'),
      lt: new Date('2024-02-01'),
    },
  },
})

Multiple operators on the same field are combined with AND:

WHERE createdAt >= '2024-01-01' AND createdAt < '2024-02-01'

Search vs Filters

Search

Search performs full-text search across specified searchFields:

const userCrud = createCrud(users, {
  searchFields: ['name', 'email'],
})

const results = await userCrud.list({
  search: 'john',
})

This searches across both name and email fields.

Filters

Filters apply exact conditions to specific fields:

const results = await userCrud.list({
  filters: {
    name: 'john', // Exact match
  },
})

Allowed Filters

Control which fields can be filtered by users:

const userCrud = createCrud(users, {
  allowedFilters: ['isActive', 'role'],
})

// This will work
await userCrud.list({
  filters: { isActive: true },
})

// This will be ignored/filtered out
await userCrud.list({
  filters: { secretField: 'value' },
})

Combining Search and Filters

Search and filters can be used together:

const results = await userCrud.list({
  search: 'john',           // Search across searchFields
  filters: {
    isActive: true,         // Exact filter
    createdAt: {
      gte: new Date('2024-01-01'),
    },
  },
})

Filter Examples

Active users created this year

const activeUsers = await userCrud.list({
  filters: {
    isActive: true,
    createdAt: {
      gte: new Date('2024-01-01'),
    },
  },
})

Users with specific roles in certain departments

const engineeringTeam = await userCrud.list({
  filters: {
    department: 'engineering',
    OR: [
      { role: 'developer' },
      { role: 'senior-developer' },
      { role: 'tech-lead' },
    ],
  },
})

Posts by multiple authors or public posts

const posts = await postCrud.list({
  filters: {
    OR: [
      {
        authorId: { op: 'in', value: ['123', '456', '789'] },
      },
      {
        isPublic: true,
      },
    ],
  },
})

Next Steps

Access Control - Implement security
Lifecycle Hooks - Add custom business logic
Validation - Custom validation schemas

Advanced Filtering

On this page