$ cat /posts/supabase-filtering-and-ordering-advanced-query-techniques.md

Supabase Filtering and Ordering: Advanced Query Techniques

drwxr-xr-x2026-01-255 min0 views

Advanced filtering and ordering capabilities transform basic database queries into powerful data retrieval operations that serve real-world application needs. Supabase provides comprehensive filtering operators including equality checks, range comparisons, pattern matching, array operations, and full-text search—all through an intuitive query builder that generates optimized PostgreSQL queries. This guide covers all filtering operators (eq, neq, gt, gte, lt, lte, like, ilike, in, is), combining multiple filters with AND/OR logic, ordering results with multiple columns, case-insensitive searching, null handling, and performance optimization techniques. Mastering these patterns enables building sophisticated features like search functionality, data dashboards, filtered lists, and complex reporting. Before proceeding, understand basic CRUD operations and database fundamentals.

Filter Operators

Operator	Description	Example	SQL Equivalent
.eq()	Equals	.eq('status', 'active')	WHERE status = 'active'
.neq()	Not equals	.neq('status', 'deleted')	WHERE status != 'deleted'
.gt()	Greater than	.gt('age', 18)	WHERE age > 18
.gte()	Greater than or equal	.gte('price', 100)	WHERE price >= 100
.lt()	Less than	.lt('stock', 10)	WHERE stock < 10
.lte()	Less than or equal	.lte('discount', 50)	WHERE discount <= 50
.like()	Pattern match (case-sensitive)	.like('name', '%John%')	WHERE name LIKE '%John%'
.ilike()	Pattern match (case-insensitive)	.ilike('email', '%@gmail.com')	WHERE email ILIKE '%@gmail.com'
.is()	Is null/not null	.is('deleted_at', null)	WHERE deleted_at IS NULL
.in()	In array	.in('status', ['active', 'pending'])	WHERE status IN (...)

Basic Filtering Examples

javascriptbasic_filters.js

// Equality filter
const { data } = await supabase
  .from('products')
  .select('*')
  .eq('category', 'electronics')

// Not equals
const { data } = await supabase
  .from('users')
  .select('*')
  .neq('status', 'banned')

// Greater than
const { data } = await supabase
  .from('products')
  .select('*')
  .gt('price', 100)

// Range query (between)
const { data } = await supabase
  .from('products')
  .select('*')
  .gte('price', 50)
  .lte('price', 200)

// IN array
const { data } = await supabase
  .from('posts')
  .select('*')
  .in('status', ['published', 'featured'])

// NULL checks
const { data } = await supabase
  .from('users')
  .select('*')
  .is('deleted_at', null) // Active users

const { data } = await supabase
  .from('users')
  .select('*')
  .not('email_verified_at', 'is', null) // Verified users

Pattern Matching with LIKE

javascriptpattern_matching.js

// Case-insensitive search (ILIKE)
const { data } = await supabase
  .from('products')
  .select('*')
  .ilike('name', '%laptop%')

// Starts with
const { data } = await supabase
  .from('users')
  .select('*')
  .ilike('username', 'admin%')

// Ends with
const { data } = await supabase
  .from('users')
  .select('*')
  .ilike('email', '%@gmail.com')

// Case-sensitive search (LIKE)
const { data } = await supabase
  .from('products')
  .select('*')
  .like('sku', 'PROD-%')

// Search multiple columns
const searchTerm = 'laptop'
const { data } = await supabase
  .from('products')
  .select('*')
  .or(`name.ilike.%${searchTerm}%,description.ilike.%${searchTerm}%`)

Pattern matching with LIKE and ILIKE enables search functionality. Use ILIKE for case-insensitive searches (most common for user input). The % wildcard matches any characters. For production search features, consider implementing full-text search for better performance and relevance ranking.

Combining Multiple Filters

javascriptmultiple_filters.js

// AND conditions (default - chain filters)
const { data } = await supabase
  .from('products')
  .select('*')
  .eq('category', 'electronics')
  .gt('price', 100)
  .eq('in_stock', true)
// SQL: WHERE category = 'electronics' AND price > 100 AND in_stock = true

// OR conditions using .or()
const { data } = await supabase
  .from('posts')
  .select('*')
  .or('status.eq.published,status.eq.featured')
// SQL: WHERE status = 'published' OR status = 'featured'

// Complex OR with multiple fields
const { data } = await supabase
  .from('products')
  .select('*')
  .or('category.eq.electronics,category.eq.computers,price.lt.50')

// Combining AND and OR
const { data } = await supabase
  .from('products')
  .select('*')
  .or('category.eq.electronics,category.eq.computers')
  .gt('price', 100)
  .eq('in_stock', true)
// SQL: WHERE (category = 'electronics' OR category = 'computers') 
//       AND price > 100 AND in_stock = true

// NOT operator
const { data } = await supabase
  .from('users')
  .select('*')
  .not('role', 'eq', 'admin')
// SQL: WHERE role != 'admin'

Ordering Results

javascriptordering.js

// Order by single column (ascending)
const { data } = await supabase
  .from('products')
  .select('*')
  .order('price', { ascending: true })

// Order by single column (descending)
const { data } = await supabase
  .from('posts')
  .select('*')
  .order('created_at', { ascending: false })

// Order by multiple columns
const { data } = await supabase
  .from('products')
  .select('*')
  .order('category', { ascending: true })
  .order('price', { ascending: false })
// SQL: ORDER BY category ASC, price DESC

// NULL values positioning
const { data } = await supabase
  .from('products')
  .select('*')
  .order('discount', { ascending: false, nullsFirst: false })

// Order with foreign table
const { data } = await supabase
  .from('posts')
  .select('*, users(name)')
  .order('created_at', { ascending: false })

Limiting and Pagination

javascriptpagination.js

// Limit results
const { data } = await supabase
  .from('posts')
  .select('*')
  .limit(10)

// Offset-based pagination
const page = 2
const pageSize = 10
const { data } = await supabase
  .from('posts')
  .select('*')
  .range((page - 1) * pageSize, page * pageSize - 1)

// Get specific range
const { data } = await supabase
  .from('products')
  .select('*')
  .range(0, 9) // First 10 items (0-9)

// Pagination with count
const { data, count } = await supabase
  .from('posts')
  .select('*', { count: 'exact' })
  .range(0, 9)

console.log(`Showing ${data.length} of ${count} total posts`)

For large datasets, implement proper pagination to improve performance and user experience. Use .range() for offset-based pagination or cursor-based approaches for infinite scroll. Learn comprehensive pagination strategies for production applications.

Performance Optimization

Create Indexes: Add indexes to columns used in WHERE clauses and ORDER BY for faster queries
Limit Results: Always use .limit() or .range() to prevent loading excessive data
Select Specific Columns: Avoid SELECT * in production; request only needed columns
Use Appropriate Operators: ILIKE is slower than simple equality; use indexes with pattern matching
Implement Full-Text Search: For complex search, use PostgreSQL's full-text search instead of ILIKE
Monitor Query Performance: Use EXPLAIN in SQL Editor to analyze slow queries

Pro Tip: When building search features, combine multiple filters with .or() for searching across multiple columns. For production apps with heavy search requirements, implement full-text search indexes for 10-100x better performance than ILIKE pattern matching.

Next Steps

Learn Joins: Query related data with relationships and foreign keys
Implement Search: Build advanced search with full-text search functionality
Optimize Pagination: Master efficient pagination techniques for large datasets
Secure Queries: Add Row Level Security policies to all tables

Conclusion

Advanced filtering and ordering techniques transform basic queries into powerful data retrieval operations that serve complex application requirements. Supabase's comprehensive operator set—equality checks, range comparisons, pattern matching, array operations, and logical combinations—provides everything needed for sophisticated filtering while maintaining query performance. Proper ordering with multiple columns and null handling ensures predictable result sets, while pagination techniques enable efficient data loading for large datasets. Remember to create indexes on filtered columns, limit result sets, and implement full-text search for production search features. With these advanced query patterns mastered, you're equipped to build data-intensive applications with complex filtering, sorting, and search capabilities. Continue enhancing your database skills with relationship queries, full-text search, and security policies for production-ready Supabase applications.