https://supabase.com/ logo
Join Discord
Powered by
So I can order by timestamp, but is there a way to...
# javascript
f
So I can order by timestamp, but is there a way to just pull in the latest two timetamps? The table has a lot of data and I can see it taking a long time to loop through it in Javascript to organize it by name and prices if that makes sense
g
wait are you trying to get lots of items and just the last 2 prices for each item... I may have misunderstood.
f
Yes. I may have worded it poorly as well. Let me try and visualize it a bit if that helps... This is how I want the table to display on the front end if it makes more sense of things!
Copy code
html
<tr>
    <th>Collection Name</th>
    <th>Current Floor Price</th>
    <th>Previous Floor Price</th>
    <th>Volume</th>
</tr>
Where I will have a lot of different collection names.
g
yeah, more complicated. I suspect it can be done with sql or a view. I'd google that and see what the sql/view would be and then decide if the js code can do the sql or you can do a view and read that. If view does not work then sql in an rpc returning table. I really think it can be done, but beyond what I've dealt with.
f
I wasn't too sure how to word a google search for an issue like that, which is why I was hoping to describe it here, but I'll keep trying to see what else I can find!
g
These would require an rpc function return a table.
f
Sorry, I am quite new with dealing with databases. I am not sure what you mean 'rpc function return a table'.
I was thinking my first step into achieving this might be to first figure out how to get the query set up to group the data by Name first, even if it returns all timestamps for a given name, then worry about just getting the most recent 2...
I see I can use .order() to organize the names, but is there a way to group the data differently than that or not really?
g
so basically the javascript calls to supabase are limited to simple sql. If you have to do more complex you have to write it in a postgres function and then you can call that function with the rpc call in javascript. Functions can return any type of data, including tables like the standard sql calls. This is some moderate lifting though if you don't know sql.
you can use order to group by name then time for sure
f
what would the .order() look like to group by name? Or are you meaning to just organize in a specific order?
g
I can't find an example, but I know the api supabase uses supports multiple order where it orders first by a column and then by another column .order(col1).order(col2) might work.
f
So its just ordering them, not really grouping?
g
well it would order by one col creating groups and then order by next to sort the groups.
I'm trying to find some reference on how/if it is done with js. None of this really helps you get the last 2 though without returning all the data and then processing.
GET /people?order=age.desc,height.asc HTTP/1.1 is an example from the postgrest api that supabase uses so it can be done.
f
I am fine if there is some processing that needs to be done I suppose, but ideally not for page speed
Anyway, I have my query
Copy code
javascript
   const test = async () => {
    let { data, error } = await supabase
      .from("SolanaFloorTracker")
      .select("*")
      .order("CollectionName", { ascending: true })
      .order("created_at", { ascending: false });
    if (error) {
      console.error(error);
    }
    console.log(data);
   };
which does organize by name and then timestamp; however, I may be misunderstanding the groups, as the first three arrays returned in the data are all the same name, then the next 4 arrays are a different name. Do I need to use JS + logic when looping through creating my table so that I don't put the same name multiple times?
For example, when looping through 
data
I wouldn't want to display this same name 3 times:
g
All you can do is do the ordering with js. You would then have to loop and pull first 2. This is why the real answer is to do it in sql on the server.
plus that only returns your needed data.
f
I don't have a server, I am just playing around locally right now, with the database being from supabase
g
yes you do
postgres has functions
I meant data base server
you can write real sql in those functions
f
hm i guess i need to figure out the postgres functions
I am very new to it so I am a bit clueless 😅
g
but like I said the "lifting" goes way up on what you need to learn.
There are probably people here more qualified here on what the sql is you. You might ask in the sql help when you figure out functions (there is a function editor in the database part of the ui).
f
What is the best way to word what I am looking for when asking in the sql channel? Or should I just describe it similar to above?
g
If you are just playing around you can certainly process the double ordered data set, but that won't work well long term if you have lots of updates per item.
f
Yeah, that was why I was originally seeing if I could get just the data I needed for speed
g
I'd present your example of what you want for output and mention group by and only last 2 rows per group ordered by time (or whatever detail you were looking for). But you will need to be able to write a function and use .rpc call to get it.
Good luck, night.
a
I think u can experiment with SQL in the SQL editor in supabase.
f
In the database section UI, it says that the functions are alpha and not suitable for production.
g
everyone is using them, the alpha is mainly the ui interface versus writing them in the SQL window. @User advice is good for testing the sql before putting it in a function
f
Ahh okay
a
Search for rpc in discord or in github discussions to see some examples: https://github.com/supabase/supabase/discussions
t
I had a shot at answering your question in a view on the sql thread. I didn't see this one. I made it a view though it would be straightforward to make it a rpc
PreviousNext
Powered by