Fetching and showing data

[WaterlessPiano2]

done: fetch status error handling fetching only required rows fetching only on first render displaying all the fetched data

todo remove rows of data that we don't need to show

[DavidReider]

Hi @WaterlessPiano2 !

Thank you for this work so far! I see what you're attempting! I think I attempted similar but without luck.

I think we may need an 'rpc' function or custom 'view' to make this work.

• rpc function documentation https://supabase.com/docs/reference/javascript/rpc
• views example (see comedies example): https://www.postgresql.org/docs/13/sql-createview.html

I am not sure how exactly these work with Supabase, which is why I requested help, but I may have an idea of what a custom view might look like. I'll include it in about an hour or two!

[DavidReider]

Maybe something like this? No sure - as mentioned, I'm not exactly sure how these work (hence why this topic was marked 'requested help' 😅 ) Also feel free to to continue to pursue the other method, just thought I'd mention this!

create view SolanaFloorTrackers AS 
-- this is a CTE
with psft as (
  SELECT *, rank() over (partition by (sft.name) order by sft."timestamp" desc),
select 
c.id , c."timestamp", c.name, c.price, c.volume, p.price, p.volume
from psft c. -- c for current
left outer join psft p ON c.name = p.name. -- p for previous
where c.rank = 1 and p.rank =2;

[WaterlessPiano2]

It looks like you would need to log in to supabase dashboard and add this view in there, and then we can call it in from the javascript

https://stackoverflow.com/questions/68735679/complex-query-with-parameters-in-supabase

I can give this a go if you like , but u need yo give me access

On Tue, 28 Dec 2021, 21:28 David Reider, @.***> wrote:

Maybe something like this? No sure - as mentioned, I'm not exactly sure how these work (hence why this topic was marked 'requested help' 😅 )

create view SolanaFloorTrackers AS -- this is a CTE

with psft as (

SELECT *, rank() over (partition by (sft.name) order by sft."timestamp" desc), select c.id , c."timestamp", c.name, c.price, c.volume, p.price, p.volume from psft c. -- c for current left outer join psft p ON c.name = p.name. -- p for previous where c.rank = 1 and p.rank =2;

— Reply to this email directly, view it on GitHub https://github.com/DavidReider/MagicEdenFloorTrack/pull/9#issuecomment-1002288496, or unsubscribe https://github.com/notifications/unsubscribe-auth/AHXPGNGFUXXPUZPR5WRVEKTUTITY3ANCNFSM5K3FCN4A . You are receiving this because you were mentioned.Message ID: @.***>

[DavidReider]

Does the email invite that I sent a couple of days ago provide the access needed?

[WaterlessPiano2]

no, that access you gave me was led me to a localhost:3000 page, and the next email only had the .env variables.

you will need to add me to the supabase team for me to create this function in there, I added a picture of where you can do that, within then organisations section. My user is also shown there.

Alternatively you can create the PostgreSql function your self on this page...

I will be unavailable for the next few days so I if you wait I can do it in early Jan, or you can give it a go. I can reply but wont be able to contribute.

[DavidReider]

I've added you to the organization in case you're able to find time before I'm able to get to it!

[WaterlessPiano2]

I found some docs on how to do this. https://www.youtube.com/watch?v=MJZCCpCYEqk for how to create the database functions that will get the data.

I started working on this but I cant get the SQL query to work yet. for some reason, can you have a look please? I will also commit the code that calls that function from the front end,

[DavidReider]

Thanks for taking a look and experimenting with this. I haven't had a chance to work on it lately, but I am hoping to take a look at this tonight again!

Are you getting errors, or is it just returning an empty set of data?

I took your snippet and tested quickly with the Supabase SQL Editor and initially was getting an error, but then I stripped out begin and end to leave just SQL and received no error, but due to no SELECT parameter, it returned an empty set. begin SELECT FROM "SolanaFloorTracker" WHERE created_at >= '2021-12-26 14:50' AND created_at <= '2021-12-26 14:58'; end;

SELECT * FROM "SolanaFloorTracker" WHERE created_at >= '2021-12-26 14:50' AND created_at <= '2021-12-26 14:58';

It is definitely possible that the function needs the begin and end though, but that is just my initial thoughts on what might be happening!

[DavidReider]

So I was playing around with this a bit more and I realized the begin and end are necessary in the function because it is written in 'plpgsql'.

Either way, after playing around a bit more, I discovered that we don't really need to use a "Function" here, but rather just create a function in the SQL Editor.

I created this function create or replace function test1() returns setof "SolanaFloorTracker" language plpgsql as $$ begin return query select * from "SolanaFloorTracker" where created_at >= '2021-12-26 14:50' AND created_at <= '2021-12-26 14:58'; end; $$

and then I can call it in the test function as supabase.rpc("test1") and it seems to retrieve the data! It seems to log the success 6x though (I think as the table state updates, the test function also reruns?)

The change here is so small and I've pushed it to my feature branch rather than to this commit (mainly because I am not really sure if I am able to commit to your PR?).

Please feel free to keep working with this way of using the SQL editor if you'd like. I am still going to try and learn some SQL to be able to achieve what we are looking for, but my SQL is rusty!

[DavidReider]

Hi @WaterlessPiano2 !

I think I was able to get a function working! Function fetch_data should return just the two most recent prices for a given collection name!

Now, we just need to get it to loop through the data and populate the table, which you've been working on!

Would you be able to review the change I have in the feature branch and continue working on this implementation? Let me know if I can help with anything in the meantime!

Much thanks!

[WaterlessPiano2]

Amazing SQL skills there, I will use this data to finish the table :+1:

[WaterlessPiano2]

yes looks good so far, I need to think of a way to loop this data in the front end so we have only one object per collection and both prices in the same object. I will look in to this tomorrow. If you can do a SQL magic to accomplish this in the server side it will be better. The more of heavy lifting we do in the server the better, so the user's browser would do less processing, and able load and function faster. When we have loads of collections, this process could take longer, and the effect would become more noticeable.

If you can commit to my branch feel free to do so, or you can merge it to your react branch and continue from there, up to you :)

[DavidReider]

It took me a while to just figure out this SQL so it only returns the top two for each collection name. I am not sure how quickly I'd be able to figure out how to combine them into one row, so it may be quicker to do this with some JavaScript!

I'll leave things as-is for now to keep working on it!

[WaterlessPiano2]

okay cool, done it in the front end, was easier then I thought :) let me know if we need to add anything to this ticket, but I feel like we can merge this now

[DavidReider]

Awesome, looks great! Thank you!

[WaterlessPiano2]

no problem :)