> ## Documentation Index
> Fetch the complete documentation index at: https://sonamu.cartanova.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# where

> Filter Conditions with WHERE Clause

`where` is a method that specifies conditions to filter query results. It supports various operators and condition combinations, enabling type-safe filtering.

## Basic Usage

### Simple Equality Condition

```typescript theme={null}
const users = await puri
  .table("users")
  .where("users.status", "active")
  .select({ id: "users.id", name: "users.name" });

// WHERE users.status = 'active'
```

### Comparison Operators

```typescript theme={null}
// Greater than
await puri.table("users").where("users.age", ">", 18);

// Less than or equal
await puri.table("orders").where("orders.amount", "<=", 1000);

// Not equal
await puri.table("posts").where("posts.status", "!=", "deleted");
```

**Supported Operators:**

* `=` - Equal (default)
* `!=` - Not equal
* `>` - Greater than
* `>=` - Greater than or equal
* `<` - Less than
* `<=` - Less than or equal
* `like` - Pattern matching
* `not like` - Pattern mismatch
* `ilike` - Case-insensitive pattern matching (PostgreSQL)
* `not ilike` - Case-insensitive pattern mismatch (PostgreSQL)

### Object-Style Conditions

You can specify multiple AND conditions as an object.

```typescript theme={null}
const users = await puri
  .table("users")
  .where({
    "users.status": "active",
    "users.role": "admin",
    "users.verified": true,
  })
  .select({ id: "users.id", name: "users.name" });

// WHERE users.status = 'active'
//   AND users.role = 'admin'
//   AND users.verified = true
```

## NULL Handling

### IS NULL

```typescript theme={null}
// Explicit null check
await puri.table("users").where("users.deleted_at", null);

// WHERE users.deleted_at IS NULL

// Using operator
await puri.table("users").where("users.deleted_at", "=", null);

// WHERE users.deleted_at IS NULL
```

### IS NOT NULL

```typescript theme={null}
await puri.table("users").where("users.email", "!=", null);

// WHERE users.email IS NOT NULL
```

## Pattern Matching (LIKE)

### Partial Matching

```typescript theme={null}
// Contains
await puri.table("users").where("users.email", "like", "%@gmail.com");

// Starts with
await puri.table("users").where("users.name", "like", "John%");

// Ends with
await puri.table("users").where("users.phone", "like", "%1234");

// Contains anywhere
await puri.table("posts").where("posts.title", "like", "%typescript%");
```

### NOT LIKE

```typescript theme={null}
await puri.table("users").where("users.email", "not like", "%spam%");

// WHERE users.email NOT LIKE '%spam%'
```

### ILIKE (Case-Insensitive, PostgreSQL)

Performs case-insensitive pattern matching in PostgreSQL.

```typescript theme={null}
// Search regardless of case
await puri.table("users").where("users.name", "ilike", "%john%");

// WHERE users.name ILIKE '%john%'
// Matches 'John', 'JOHN', 'john', etc.
```

### NOT ILIKE (PostgreSQL)

```typescript theme={null}
await puri.table("users").where("users.email", "not ilike", "%SPAM%");

// WHERE users.email NOT ILIKE '%SPAM%'
// Excludes 'spam', 'Spam', 'SPAM', etc.
```

<Info>
  `ilike` and `not ilike` are PostgreSQL-specific operators. In MySQL, you can achieve similar
  results by combining `LOWER()` function with `like`.
</Info>

## whereIn / whereNotIn

### One of Multiple Values

```typescript theme={null}
// IN
const userIds = [1, 2, 3, 4, 5];
await puri.table("users").whereIn("users.id", userIds);

// WHERE users.id IN (1, 2, 3, 4, 5)

// NOT IN
await puri.table("users").whereNotIn("users.status", ["banned", "suspended"]);

// WHERE users.status NOT IN ('banned', 'suspended')
```

### Empty Array Handling

```typescript theme={null}
// Empty array always returns false
await puri.table("users").whereIn("users.id", []);

// WHERE 1 = 0 (returns nothing)
```

## Complex Condition Groups

### whereGroup (AND Group)

Creates conditions wrapped in parentheses.

```typescript theme={null}
await puri
  .table("users")
  .where("users.role", "admin")
  .whereGroup((group) => {
    group.where("users.status", "active").orWhere("users.status", "pending");
  });

// WHERE users.role = 'admin'
//   AND (users.status = 'active' OR users.status = 'pending')
```

### orWhereGroup (OR Group)

```typescript theme={null}
await puri
  .table("products")
  .where("products.published", true)
  .orWhereGroup((group) => {
    group.where("products.featured", true).where("products.stock", ">", 0);
  });

// WHERE products.published = true
//   OR (products.featured = true AND products.stock > 0)
```

### Nested Groups

```typescript theme={null}
await puri.table("orders").whereGroup((outer) => {
  outer.where("orders.status", "completed").whereGroup((inner) => {
    inner.where("orders.amount", ">", 1000).orWhere("orders.vip", true);
  });
});

// WHERE (
//   orders.status = 'completed'
//   AND (orders.amount > 1000 OR orders.vip = true)
// )
```

## Full-Text Search

### whereMatch (MySQL)

Uses MySQL's FULLTEXT index.

```typescript theme={null}
await puri.table("posts").whereMatch("posts.content", "typescript programming");

// WHERE MATCH (posts.content) AGAINST ('typescript programming')
```

<Info>To use `whereMatch`, the column must have a FULLTEXT index.</Info>

### whereTsSearch (PostgreSQL)

Uses PostgreSQL's tsvector.

```typescript theme={null}
await puri.table("posts").whereTsSearch("posts.content_tsv", "typescript programming");

// WHERE posts.content_tsv @@ websearch_to_tsquery('simple', 'typescript programming')
```

**Options:**

```typescript theme={null}
// Specify parser
await puri.table("posts").whereTsSearch("posts.content_tsv", "typescript", {
  parser: "plainto_tsquery", // or "to_tsquery", "phraseto_tsquery"
  config: "english", // Language configuration
});

// Specify config only
await puri.table("posts").whereTsSearch("posts.content_tsv", "typescript", "english");
```

### whereSearch (PGroonga)

Full-text search using the PGroonga extension.

```typescript theme={null}
// Single column search
await puri.table("posts").whereSearch("posts.title", "타입스크립트");

// WHERE posts.title &@~ pgroonga_condition('타입스크립트')

// Multiple column search (array)
await puri.table("posts").whereSearch(["posts.title", "posts.content"], "타입스크립트");

// WHERE ARRAY[posts.title::text, posts.content::text]
//   &@~ pgroonga_condition('타입스크립트')

// Specify weights
await puri.table("posts").whereSearch(
  ["posts.title", "posts.content"],
  "타입스크립트",
  { weights: [10, 1] }, // title is 10x more important than content
);
```

<Info>PGroonga search must use the **same column configuration** as the index to be used.</Info>

### whereFuzzy (pg\_trgm)

Fuzzy string matching using PostgreSQL's `pg_trgm` extension. Based on trigrams, it is tolerant of typos. Combined with generated columns and GIN indexes, it is effective for multi-column search.

```typescript theme={null}
// Basic usage (word similarity, <% operator)
await puri.table("items").whereFuzzy("items.search_text", "typscript");

// WHERE 'typscript'::text <% items.search_text::text
```

**Specifying operators:**

```typescript theme={null}
// similarity (% operator)
await puri.table("items").whereFuzzy("items.search_text", "typscript", { operator: "%" });

// WHERE items.search_text::text % 'typscript'::text

// strict word similarity (<<% operator)
await puri.table("items").whereFuzzy("items.search_text", "typscript", { operator: "<<%" });

// WHERE 'typscript'::text <<% items.search_text::text
```

**Operator comparison:**

| Operator       | Meaning                | SQL                              | Description                                             |
| -------------- | ---------------------- | -------------------------------- | ------------------------------------------------------- |
| `<%` (default) | word similarity        | `'query'::text <% column::text`  | Checks if query is similar to a substring of the column |
| `%`            | similarity             | `column::text % 'query'::text`   | Compares overall string similarity                      |
| `<<%`          | strict word similarity | `'query'::text <<% column::text` | Stricter substring matching than word similarity        |

<Info>
  `whereFuzzy` requires the `pg_trgm` extension. Install it with `CREATE EXTENSION IF NOT EXISTS
      pg_trgm`. Creating a GIN index (`gin_trgm_ops`) significantly improves performance.
</Info>

## Raw SQL Conditions

### whereRaw

You can write complex SQL conditions directly.

```typescript theme={null}
// No parameters
await puri.table("users").whereRaw("YEAR(users.created_at) = YEAR(CURRENT_DATE)");

// Parameter binding
await puri.table("users").whereRaw("users.age BETWEEN ? AND ?", [18, 65]);

// Multiple conditions
await puri
  .table("orders")
  .whereRaw("DATE(orders.created_at) = ?", ["2024-01-01"])
  .whereRaw("orders.amount > ?", [1000]);
```

<Warning>Always use parameter binding to prevent SQL injection.</Warning>

## Real-World Examples

<Tabs>
  <Tab title="User Filtering" icon="user">
    ```typescript theme={null}
    async function getActiveAdmins() {
      return puri.table("users")
        .where({
          "users.role": "admin",
          "users.status": "active",
          "users.email_verified": true
        })
        .where("users.last_login", ">", new Date(Date.now() - 30 * 24 * 60 * 60 * 1000))
        .select({
          id: "users.id",
          email: "users.email",
          name: "users.name",
          last_login: "users.last_login"
        })
        .orderBy("users.last_login", "desc");
    }
    ```
  </Tab>

  <Tab title="Order Search" icon="cart-shopping">
    ```typescript theme={null}
    async function searchOrders(filters: {
      status?: string;
      minAmount?: number;
      userIds?: number[];
      dateFrom?: Date;
      dateTo?: Date;
    }) {
      let query = puri.table("orders").selectAll();

      // Status filter
      if (filters.status) {
        query = query.where("orders.status", filters.status);
      }

      // Amount range
      if (filters.minAmount) {
        query = query.where("orders.amount", ">=", filters.minAmount);
      }

      // User list
      if (filters.userIds && filters.userIds.length > 0) {
        query = query.whereIn("orders.user_id", filters.userIds);
      }

      // Date range
      if (filters.dateFrom) {
        query = query.where("orders.created_at", ">=", filters.dateFrom);
      }
      if (filters.dateTo) {
        query = query.where("orders.created_at", "<=", filters.dateTo);
      }

      return query.orderBy("orders.created_at", "desc");
    }
    ```
  </Tab>

  <Tab title="Complex Conditions" icon="filter">
    ```typescript theme={null}
    async function getEligibleProducts() {
      return puri.table("products")
        // Basic conditions
        .where("products.published", true)
        .where("products.deleted_at", null)

        // (In stock OR Pre-order available) AND Price conditions
        .whereGroup((group1) => {
          group1.whereGroup((group2) => {
            group2.where("products.stock", ">", 0)
                  .orWhere("products.preorder_available", true);
          }).whereGroup((group3) => {
            group3.where("products.price", ">=", 1000)
                  .where("products.price", "<=", 100000);
          });
        })

        // Exclusion conditions
        .whereNotIn("products.category", ["adult", "restricted"])

        .select({
          id: "products.id",
          name: "products.name",
          price: "products.price",
          stock: "products.stock"
        })
        .orderBy("products.featured", "desc")
        .orderBy("products.created_at", "desc");
    }
    ```
  </Tab>

  <Tab title="Full-Text Search" icon="magnifying-glass">
    ```typescript theme={null}
    async function searchPosts(keyword: string) {
      return puri.table("posts")
        // PGroonga full-text search
        .whereSearch(
          ["posts.title", "posts.content"],
          keyword,
          { weights: [10, 1] }  // Higher weight for title
        )

        // Additional filters
        .where("posts.published", true)
        .where("posts.deleted_at", null)

        .select({
          id: "posts.id",

          // Highlighted title/content
          title: Puri.highlight("posts.title", keyword),
          content: Puri.highlight("posts.content", keyword),

          // Search score
          score: Puri.score(),

          created_at: "posts.created_at"
        })

        // Sort by score
        .orderBy("score", "desc")
        .limit(20);
    }
    ```
  </Tab>

  <Tab title="Date Range" icon="calendar">
    ```typescript theme={null}
    async function getRecentActivity(days: number = 7) {
      const startDate = new Date();
      startDate.setDate(startDate.getDate() - days);

      return puri.table("activities")
        // Date range
        .where("activities.created_at", ">=", startDate)
        .where("activities.created_at", "<=", new Date())

        // Active users only
        .whereGroup((group) => {
          group.where("activities.user_status", "active")
               .orWhere("activities.user_status", "premium");
        })

        // Exclude specific types
        .whereNotIn("activities.type", ["system", "automated"])

        .select({
          id: "activities.id",
          type: "activities.type",
          user_id: "activities.user_id",
          created_at: "activities.created_at"
        })
        .orderBy("activities.created_at", "desc");
    }
    ```
  </Tab>
</Tabs>

## Condition Chaining

All `where` methods are chainable and are connected with **AND** by default.

```typescript theme={null}
const result = await puri
  .table("users")
  .where("users.role", "admin") // AND
  .where("users.status", "active") // AND
  .where("users.age", ">", 18) // AND
  .whereIn("users.country", ["KR", "US"]) // AND
  .where("users.verified", true); // AND
```

## OR Conditions

### orWhere within WhereGroup

```typescript theme={null}
await puri.table("users").whereGroup((group) => {
  group
    .where("users.role", "admin")
    .orWhere("users.role", "moderator")
    .orWhere("users.role", "editor");
});

// WHERE (
//   users.role = 'admin'
//   OR users.role = 'moderator'
//   OR users.role = 'editor'
// )
```

<Info>
  There is no `orWhere` at the top level. Use OR conditions within `whereGroup` or `orWhereGroup`.
</Info>

## Type Safety

WHERE conditions are validated type-safely.

```typescript theme={null}
// ✅ Valid column
await puri.table("users").where("users.email", "like", "%@gmail.com");

// ❌ Type error: Non-existent column
await puri.table("users").where("users.unknown_field", "value");

// ✅ Valid type
await puri.table("users").where("users.age", 25); // number

// ❌ Type error: Wrong type
await puri.table("users").where("users.age", "25"); // string (age is number)
```

## Performance Optimization

### 1. Utilizing Indexes

```typescript theme={null}
// ✅ Good: Use indexed column
await puri.table("users").where("users.email", "user@example.com"); // email needs index

// ❌ Bad: Using functions (invalidates index)
await puri.table("users").whereRaw("LOWER(users.email) = ?", ["user@example.com"]);
```

### 2. LIKE/ILIKE Patterns

```typescript theme={null}
// ✅ Good: Fixed prefix (can use index)
.where("users.name", "like", "John%")
.where("users.name", "ilike", "john%")  // PostgreSQL

// ❌ Bad: Leading wildcard (cannot use index)
.where("users.name", "like", "%John")
.where("users.name", "ilike", "%john")  // PostgreSQL
```

<Info>
  In PostgreSQL, `ilike` is convenient for case-insensitive search, but it can affect performance on
  large datasets. Consider using `citext` type or `pg_trgm` extension indexes for such cases.
</Info>

### 3. IN vs Multiple ORs

```typescript theme={null}
// ✅ Good: Use IN
.whereIn("users.id", [1, 2, 3, 4, 5])

// ❌ Bad: Multiple ORs (inefficient)
.whereGroup((g) => {
  g.where("users.id", 1)
   .orWhere("users.id", 2)
   .orWhere("users.id", 3)
   .orWhere("users.id", 4)
   .orWhere("users.id", 5)
})
```

## Cautions

### 1. NULL Handling

```typescript theme={null}
// ✅ Correct: NULL check
.where("users.deleted_at", null)           // IS NULL
.where("users.deleted_at", "=", null)      // IS NULL
.where("users.deleted_at", "!=", null)     // IS NOT NULL

// ❌ Wrong: Regular comparison (doesn't work)
.where("users.deleted_at", "=", undefined)
```

### 2. Empty Arrays

```typescript theme={null}
// whereIn with empty array returns no results
const ids = [];
await puri.table("users").whereIn("users.id", ids); // Returns nothing

// Handle conditionally
if (ids.length > 0) {
  query = query.whereIn("users.id", ids);
}
```

### 3. Raw SQL Injection

```typescript theme={null}
// ❌ Dangerous: SQL injection possible
const userInput = "'; DROP TABLE users; --";
.whereRaw(`users.name = '${userInput}'`)

// ✅ Safe: Parameter binding
.whereRaw("users.name = ?", [userInput])
```

### 4. Condition Order

```typescript theme={null}
// WHERE order can affect performance

// ✅ Good: Highly selective conditions first
.where("users.id", 123)        // Very selective
.where("users.status", "active") // Less selective

// ❌ Inefficient: Low selectivity first
.where("users.status", "active") // Many rows
.where("users.id", 123)        // Few rows
```

## Next Steps

<CardGroup cols={2}>
  <Card title="select" icon="list-check" href="/en/api-reference/puri-methods/select">
    Select fields to retrieve
  </Card>

  <Card title="join" icon="link" href="/en/api-reference/puri-methods/join">
    Join tables
  </Card>

  <Card title="order-by" icon="arrow-down-1-9" href="/en/api-reference/puri-methods/order-by">
    Sort results
  </Card>

  <Card title="limit" icon="hashtag" href="/en/api-reference/puri-methods/limit">
    Limit number of results
  </Card>
</CardGroup>