Filtering and sorting queries in Supabase

Introduction

Filtering and sorting help you find and organize data easily. They let you get only what you need in the order you want.

You want to see only users from a specific city in your app.

You need to list products by price from low to high.

You want to find all orders made after a certain date.

You want to show the newest blog posts first.

You want to filter tasks by status and sort by deadline.

Syntax

Supabase

const { data, error } = await supabase
  .from('table_name')
  .select('*')
  .eq('column_name', 'value')
  .order('column_name', { ascending: true })

Use .eq() or other filter methods to pick rows matching a condition.

Use .order() to sort results by a column.

Examples

Gets all users where city equals 'Paris'.

Supabase

const { data, error } = await supabase
  .from('users')
  .select('*')
  .eq('city', 'Paris')

Gets all products sorted by price from high to low.

Supabase

const { data, error } = await supabase
  .from('products')
  .select('*')
  .order('price', { ascending: false })

Gets orders made after Jan 1, 2024, sorted from oldest to newest.

Supabase

const { data, error } = await supabase
  .from('orders')
  .select('*')
  .gt('created_at', '2024-01-01')
  .order('created_at', { ascending: true })

Sample Program

This code gets users from the USA and sorts them by their last login time, newest first. It prints the list or an error if something goes wrong.

Supabase

import { createClient } from '@supabase/supabase-js'

const supabaseUrl = 'https://xyzcompany.supabase.co'
const supabaseKey = 'public-anonymous-key'
const supabase = createClient(supabaseUrl, supabaseKey)

async function getFilteredSortedUsers() {
  const { data, error } = await supabase
    .from('users')
    .select('*')
    .eq('country', 'USA')
    .order('last_login', { ascending: false })

  if (error) {
    console.error('Error:', error.message)
    return
  }

  console.log('Filtered and sorted users:', data)
}

getFilteredSortedUsers()

Important Notes

Use operators like eq (equals), gt (greater than), lt (less than) for filtering.

Sorting defaults to ascending order; set ascending: false for descending.

Always check for errors after queries to handle problems gracefully.

Summary

Filtering lets you pick only the data you want.

Sorting arranges data in the order you need.

Supabase makes filtering and sorting easy with simple methods.

Practice

1. What does filtering do in a Supabase query?

easy

A. It deletes rows from the database.

B. It changes the order of the rows returned.

C. It selects only the rows that match certain conditions.

D. It creates a new table with filtered data.

Solution

1. Step 1: Understand filtering purpose

   Filtering is used to pick only rows that meet specific conditions from a table.

2. Step 2: Differentiate filtering from sorting and other actions

   Sorting changes order, deleting removes data, and creating tables is unrelated to filtering.

3. Final Answer:

   It selects only the rows that match certain conditions. -> Option C

4. Quick Check:

   Filtering = select matching rows [OK]

Hint: Filtering picks rows by condition, not order or deletion [OK]

Common Mistakes:

• Confusing filtering with sorting
• Thinking filtering deletes data
• Believing filtering creates new tables

2. Which of the following is the correct way to filter rows where the column age is greater than 30 in Supabase?

easy

A. supabase.from('users').select('*').where('age', '>', 30)

B. supabase.from('users').select('*').filter('age', 'gt', 30)

C. supabase.from('users').select('*').gt('age', 30)

D. supabase.from('users').select('*').filter('age', '>', 30)

Solution

1. Step 1: Recall Supabase filtering syntax

   Supabase uses .filter(column, operator, value) to filter data.

2. Step 2: Identify correct operator and method usage

   The operator for greater than is 'gt', so .filter('age', 'gt', 30) is correct.

3. Final Answer:

   supabase.from('users').select('*').filter('age', 'gt', 30) -> Option B

4. Quick Check:

   Use .filter with 'gt' for greater than [OK]

Hint: Use .filter with 'gt' for greater than in Supabase [OK]

Common Mistakes:

• Using .where instead of .filter
• Using '>' instead of 'gt' as operator
• Using .gt() method which does not exist

3. What will be the result of this Supabase query?

supabase.from('products').select('*').order('price', { ascending: false }).limit(3)

medium

A. Returns the 3 most expensive products.

B. Returns the 3 cheapest products.

C. Returns all products sorted by price ascending.

D. Returns an error because of wrong order syntax.

Solution

1. Step 1: Understand the order method

   The .order('price', { ascending: false }) sorts products by price from highest to lowest.

2. Step 2: Understand the limit method

   The .limit(3) restricts the result to only the first 3 rows after sorting.

3. Final Answer:

   Returns the 3 most expensive products. -> Option A

4. Quick Check:

   Descending order + limit 3 = top 3 expensive [OK]

Hint: Descending order + limit picks top expensive items [OK]

Common Mistakes:

• Confusing ascending false with ascending true
• Ignoring the limit effect
• Assuming syntax error in order method

4. Identify the error in this Supabase query that tries to filter users with status 'active' and sort by signup date ascending:

supabase.from('users').select('*').filter('status', 'eq', 'active').order('signup_date', ascending: true)

medium

A. The filter operator 'eq' is invalid.

B. The from method should be called after order.

C. The select method is missing columns.

D. The order method syntax is incorrect.

Solution

1. Step 1: Check filter method usage

   The filter method with 'eq' operator is correct to check equality.

2. Step 2: Check order method syntax

   The order method requires an object for options: .order('signup_date', { ascending: true }). Missing braces causes syntax error.

3. Final Answer:

   The order method syntax is incorrect. -> Option D

4. Quick Check:

   Order options must be inside braces { } [OK]

Hint: Use braces { } around order options object [OK]

Common Mistakes:

• Writing order options without braces
• Thinking 'eq' is invalid operator
• Misordering method calls

5. You want to get all orders where the status is either 'pending' or 'processing', sorted by order_date descending. Which Supabase query is correct?

hard

A. supabase.from('orders').select('*').filter('status', 'in', ['pending', 'processing']).order('order_date', { ascending: false })

B. supabase.from('orders').select('*').filter('status', 'eq', ['pending', 'processing']).order('order_date', { ascending: false })

C. supabase.from('orders').select('*').or('status.eq.pending,status.eq.processing').order('order_date', { ascending: true })

D. supabase.from('orders').select('*').filter('status', 'or', ['pending', 'processing']).order('order_date', { ascending: true })

Solution

1. Step 1: Understand filtering for multiple values

   Supabase supports .filter('status', 'in', ['pending', 'processing']) to match any value in the list.

2. Step 2: Check sorting order

   Descending sort uses { ascending: false }.

3. Step 3: Identify correct query

   supabase.from('orders').select('*').filter('status', 'in', ['pending', 'processing']).order('order_date', { ascending: false }) uses the proper 'in' operator and descending order.

4. Final Answer:

   supabase.from('orders').select('*').filter('status', 'in', ['pending', 'processing']).order('order_date', { ascending: false }) -> Option A

5. Quick Check:

   Use .filter 'in' with array + descending [OK]

Hint: Use .filter with 'in' operator for multiple values [OK]

Common Mistakes:

• Using 'eq' with array instead of 'in'
• Using .or() but wrong ascending direction
• Using invalid 'or' operator