CODEWITHSUNDEEP
javascriptnode.jspostgresqlsupabasesupabase-database
Florentino Moore
Florentino Moore

Reputation: 63

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

Upvotes: 1

Views: 5230

Answers (2)

Man Man Yu
Man Man Yu

Reputation: 198

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)

Upvotes: 2

Andrew Smith
Andrew Smith

Reputation: 1841

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.

Upvotes: 4

Related Questions