Fuzzy Filtering and Relational Queries

Let us explore advanced queries in Supabase, focusing on complex filters, fuzzy search matching, and joined relational table queries.

1. Advanced Query Filters

Supabase JS SDK supports several filter methods:

• .eq() / .neq(): Equal / Not Equal comparisons.
• .gt() / .lt(): Greater than / Less than comparisons.
• .in(): Checks if a column value matches any item in a provided array.
• .ilike(): Case-insensitive substring matching (using SQL LIKE syntax).

const { data, error } = await supabase
  .from("products")
  .select("id, name, price")
  .ilike("name", "%chair%") // Match product names containing chair
  .gt("price", 100) // Price is greater than 100
  .in("status", ["active", "featured"]); // Status matches active or featured

2. Multi-Table Relation Queries

In traditional SQL, loading related data across tables requires writing JOIN clauses. In Supabase, relational queries are handled directly within the .select() statement.

If your Postgres tables contain foreign key definitions, Supabase detects them automatically.

A. One-to-Many Joined Queries

For example, if you have a posts table containing an author_id column that references the profiles table:

// Fetch posts and include their associated author profiles details
const { data, error } = await supabase
  .from("posts")
  .select(`
    id,
    title,
    author:profiles (
      username,
      avatar_url
    )
  `);

This returns a structured JSON payload:

[
  {
    "id": 1,
    "title": "Introduction to Supabase",
    "author": {
      "username": "johndoe",
      "avatar_url": "https://..."
    }
  }
]

B. Filtering Joined Relation Tables

You can also filter query results based on columns inside the joined relation table:

const { data, error } = await supabase
  .from("posts")
  .select("title, profiles(username)")
  .eq("profiles.username", "johndoe"); // Only return posts authored by johndoe