Is there any way to fetch the last eg.30 rows in descending order? I tried with ascending: false but the order is not correct. The last item of table is first on fetched array.

Hello @theuknowner! This thread has been automatically created from your message in #843999948717555735 a ``few seconds ago``. Pinging @User so that they see this as well! Want to unsubscribe from this thread? Right-click the thread in Discord (or use the ... menu) and select Leave Thread to unsubscribe from future updates. Want to change the title? Use the

/title

command! We have solved your problem? Click the button below to archive it.

theuknowner (2022-03-28)

and https://supabase.com/docs/reference/javascript/range. Some combo should allow you to get something sorted.

I tried with ascending: false but as I wrote, I get incorrect results. The order is wrong

> 0 -> 450, 1 -> 499 ...29 - > 421 I don't understand what you're showing here. Are you getting rows with ID's in this order:

[0, 1, 2, 3, 4, ..., 450]

? What is the

1 -> 499

and

...29 -> 421

showing us? If you do a

range(30, 100).order('id', {ascending: false})

, do you get results with ID's in the order

[100, 99, 98, 97, 96, ..., 33, 32, 31, 30]

?

Yes these are ids in the array position

use .limit(30) instead of range

let cityList = await supaClient .from('cities') .select('*') .limit(30) .range(0,30) .order('id', { ascending: false})

drop range

My original code was without range

const { data:messages, error:error1 } = await supabase .from('test') .select('*') .limit(5) .order('id',{ascending:false})

It should be the opposite. 29 -> 450, 0-> 420

So...

data.reverse()

if you want the indexes reversed?

So only on client side can I achieve this?

The DB is bringing back the data you wanted in the order you asked for it. The indexes in the JS side of things are completely independent. Index 0 will map to 450 because it's in descending order from the end as you asked.

Okay so the only way is on client side with reverse

I think limitToLast is only on their realtime Database. When I used Firestore database it worked the same as here.

Yes, I was using the Realtime Database

@User Actually you could also use a .range(451,500) and it will return what it can. This is the best way for pagination. Normally you have filters on your data like by user_id and so id column is not useful as a direct number. Range is on your query result set.

Yes but If i don't know the item's row (451)? For example, I have stored 100 items in db, user fetches the last 30 , I save the last 30 items in an indexed db. When the user logs in again, I want to fetch the next items of the last 30 if exist. In firebase i would do it with startAt(last saved index id), with range I should know from the beginning

Same here sort of. Limit is the number of the result in query, not the id of the row. So if you don't change the query, then if you fetched 100 last time, you start at 100 this time.

In firebase is the key id of item

You can do .gt(‘id’, 450).limit(30)

Yes I think that's the best-easiest way

@User You say you are storing these to indexedDb. That works fine if the saved rows never change or are not deleted on the server. If they do your, indexedDb data will be stale. Since you are coming from Firebase, just wondering. I cache data to indexedDb, but rely on live database queries when user logs in to get current "live" record numbers in case some have been deleted. I also always write a new record if data changes and delete the old record versus updating...

If you really want to do it on the server side, you can create a stored procedure that subqueries your filtered set and restores ascending order,

select * from (
  select * from cities order by id desc limit 30
) order by id asc

@User It's for my chat module. So the flow is like this: 1) User fetches last 30 msgs from DB, I store them in Indexed DB. When he receives a new msg, I append the msg to array and I store it in indexed db also. If user is not connected when he received the msgs, I check the last msg id of conv with the last id of stored conv. If they are different, I fetch all new msgs and store them in indexed db as well.

Sounds good for chat as messages don’t change or get deleted that might be in indexedDb.

@User I have created a conversations table in which every user can listen for update. In conversation table I store id, participants etc, and Last message details. So instead of having multiple listeners for each conversation, I have one listener on conversations table and the user is notified when the last message id has been changed. Is there any way to fetch specific columns on Update event for realtime listener? I want to fetch only the columns with the last_message (last_message_id, lastMessage_sentBy etc..) not the whole row.

You get the whole row, although that would be a good feature request, at least to just get primary key(s)

Yes that would be very useful