Question 114024 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Query the last record in supabase?

How to check the last record of a user in supabase. I would only need by means of the user ID to find his last record "date" in a descending way

I have something like the following, but this would just sort the table and I really don’t want to bring it all just the most recent dates. I am working on this in nodejs with express and I am using the @supabase/supabase-js library for auth with supabase

Does anyone know how?

const { data, error } = await supabase.from('activity-historic-tracked').select('*').in('user_id', user_ids).order('date', { ascending: false })

I made the query in supabase using sql with DISTINC ON to return only different values because I only want to list the different values and not bring repeated values and at the end ordering them descendingly

select distinct on (user_id) user_id, date
from "activity-historic-tracked"
order by user_id, date desc;

enter image description here

According to what I was reading in this question rpc function, doing something like this could be done using views or supabase stored procedures, but how is it done?

Please help me

Tags:

2

Answers


  1. 0

    As mentioned in the other SO answer you linked, you can create a view or a rpc function. To create a view you would use the SQL editor in the Supabase Dashboard with the following:

    CREATE VIEW view_name AS 
  SELECT DISTINCT on (user_id) user_id, date
  FROM "activity-historic-tracked"
  ORDER BY user_id, date DESC;

    And now you would use this with the supabase-js library just like you would with a normal table.

    await supabase.from('view_name').select('*')

    If you were to go the .rpc function route, you would call it via that method on the supabase-js library.

    Login or Signup to reply.
  2. 0

    take a look on this https://supabase.com/docs/reference/javascript/eq

    My suggestion was as follow:

             const { data, error } = await supabase
        .from('activity-historic-tracked') //table name
        .select("*") //columns to select from the database
        .eq('user_id', **theid you want to query with lastest result**)
        .order('id', { ascending: false })
        .limit(1)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search