Given an array of IDs, what is the best way to fetch each one?
I have an event table, and within each entry is an attendees column. This column is an array of the IDs of all the attendees. How would I go about taking this array and fetching each ID. The first thought I had was to just use a map, and fetch each one, but that seems very resource intensive. Is there a better way of doing this?
64 Replies
Better to not have an array of id's to start with and a join table for things like that.... but
If the list is not too long do .in('id_col',array_of_ids)
If the number of ids is too high then you would need to do an rpc call because filters are in the url and there are url length limits on the internet.
So I'm still happy to change my approach. My concern here is that I'm trying to learn relational design and it is failing miserably, and this app is due in a month. So I've abandoned any semblance of clean code for this MVP.
Should I make a
event_attendee table then instead? If so, how would that change this approach? I'm VERY new to drizzle/Supabase/SQL in general so this might just be me taking on more than I should have with a new technology I'm unfamiliar with.
I'm not sure what .in('id_col',array_of_ids) is or how I'd use it. I'm sorry!The array approach will work, just not the best way. And the URL limit is 2048 so your array stringified can't be close to that.
https://supabase.com/docs/reference/javascript/in
https://supabase.com/docs/reference/javascript/in
JavaScript: Column is in an array | Supabase Docs
Supabase API reference for JavaScript: Column is in an array
Is this the same?
an event attendee table with a foreign key to both the events table and the attendee table is the "correct" way. Then you populate the pairs.
So coming from firebase, etc, how would I go about getting that list of attendees for this event, and then doing the same thing by taking that list and getting each attendees info from the attendees table?
Querying Joins and Nested tables | Supabase Docs
The Data APIs automatically detect relationships between Postgres tables.
Would that just be an table called
event_attendee that has an ID, event_id, attendee_id and that's it?
Seems like not a lot of info for a whole table, no?That link shows what to do. the attendee_id and event_id need to be foreign keys and part of the primary key as the doc says.
Got it
That is much much faster than doing two fetches to get your array of ids and then go get all the attendess from another table.
So that's what I have with the event
The
event_orginizer is a forgien key to the users table (users and attendees aren't the same)
not to mention, this will also cause issues if the list is longer than 2048, right?
Considering the last event had 200+ people, and an ID is longer than 10, that is not an optionsYes you would have to do an rpc call which is another level of complexity as it would send the ids in the body and not the URL
So I'll go with the new table
I'll reach about joins, thanks!
It is a bit more work to insert but pays off.
One question I'm not sure about is where should this be done? Can the organizer screen run these locally, or do I need to do something else?
At this point, if it works, and I can finish it on time, then it is good for me!
Also if lists are dynamic it is a major win as you don't have to try an update an array which is slow..
I lost a month while I was in and out of the hospital so I'm in crunch now to get this done
:lolsob: which is what I'm doing now lol
This was my code to add someone to that array
I'm an idiot
How can I stop duplicates though? Since each id (event and attendee) can be used multipletimes, just not together so saying each column is unique won't help
The pair will be unique because of them being both part of the primary key.
A user can be in multiple events, but only one time for each event.
The AI is pretty good at SQL stuff.
What do you mean AI?
So I should make the entry ID a combo of both the event & attendee IDs?
This is how I was going to make it
unless this wasn't what you meant
You need to add them to the primary key.
And you don't really need an id column.
You're adming right?
Or mod?
Why did it just tell me I can't send this message?
oh wait
it was probably my choice of words lol
There is an option to let the AI see your structures too and it is much smarter with the answers.
one sec
I think what scares me about using AI in situations like this is that I'm not familiar with a lot of things here so if it makes stuff up or doesn't do something right, I woulnd't know
That is a risk for sure. It is just much better at SQL than say supabase JS calls.
Stuff == OK
$h!t == Not okay
Got it, I'll give it a try
Anyway add your two columns to the primary key and drop the id.
Oh, didn't even know you can do that
The join link I gave you shows the correct way.
Let me see how!
This was the AIs response
very different from yours
It is adding indexes for performance and it does not know the REST API requires both be part of the primary key... SIGH.
So did I screw up?
Just follow the link and forget I said AI.
Oh lol
Okay
this, right?
That:
Ohhhh
and you can add created_at if you want.
doesn't hurt
don't call it members though
You can also set it up in the UI and just click primary for the two columns
Ohhhh
So I can click make this primary for both ids and then not bother with make unique?
yes primary has to be unique
each column cannot be unique or you could not set up multiple events or people.
Got it! Thank you
Will Supabase throw an error if I try to add something to a table that should be unique but isn't?
Like an attendee to the same event twince?
You said not to call it members, but should I still call it
event_attendee or would something like event_guests be better?
Something that has been confusing me. Why is it defaulting to int instead of UUID for ids?Anything meaningful members is pretty generic and more implies clubs.
It will error if you insert a pair of the same event and user.
Int Id's are a bit faster and auto increment. UUID is more come for user_id as you can't guess the next one.
got it!
this is what you meant
At some point I will need to learn more about SQL itself, but for now, this works for me
Looks correct. and I assume those are both fk's to your other tables.
Oh! I see now. Any downside to using uuid then in this case, or should I keep most things on int?
yupp
I would use UUID for event id and attendee, especially attendee.
If they are signed in users then they have a uuid already.
Oh so someone can't take their ID, and add one to get someone else?
So users, are people that signed up for an account. Attendees could be anon signins
RLS would still protect you, but just weird to know.
So they already have UUID's either way.
So should the attendee have the same primary key (id) as the user id?
Up to you. I do.
Ok, got it!
You have been so immensly helpful
I can't thank you enough
The complex part is still ahead of me, so I'm sure you'll see another post, but for now, this answered my question perfectly! With a lot more info on how to proceed
Then you can do something like
.from('events').select('*,attendees!inner(*)').eq('event_id',1)
And get all your info in one call.
Assuming you meet RLS on the tables.I'll use the docs you sent to break down that command so I can understand what's going on
Thank you so so much
I just need to re-populate now ugh
since I added a new table
That might be a seperate question later about typing since it isn't working as I'd expect
but it could also be me
If you mean typescript don't ask me... I don't use it.
You are even smarter than I had imagined lol
Again though, thank you so so much!