meager locust
Let's say I have these types of object
{
id: 123
references: [156, 789]
}
{
id: 156
references: [482]
}
How would I be able to select an ID and find it's references and then that objects references and keep going for recursion till 50 items r hit in a single query.
arctic irisBOT
daring pivot
Normally in an a relational db, you would do the reverse
The row would tell you where it’s referenced from
Then there’s the question of whether you want a DFS or a BFS
DFS is quick bc you can use the in operator to check if a column matches any of the values ur looking for at once
BFS, you can prob just make a queue of the row ur searching by and run up to 50 queries in order
meager locust
Wdym the opposite how would u connect them ?
So I basically have a bunch of objects and they have reference to other objects which have references to other objects and it keeps going up a chain.
daring pivot
Wdym the opposite how would u connect them ?
The row would tell you where it’s referenced from
id: 156
referencedFrom: 123```referencedFrom (if not null) references to an existing row's id column
meager locust
I see but how would u do the recursive part in the query ?
meager locust
I'm sure it's different for dbs but what would the key word be? I'm using postgress
daring pivot
I see but how would u do the recursive part in the query ?
Prob a while loop
meager locust
Prob a while loop
I mean in a single query to the db
daring pivot
I mean in a single query to the db
I’d argue that’s how I’d do it. Apparently, WITH RECURSIVE statements exist
Although, it’s still internally running the queries multiple times, so ur not saving much performance by using this
meager locust
It's saving time on the network prob idk
Idk now bad this is cuz there is thousands of objects
meager locust
I’d argue that’s how I’d do it. Apparently, [WITH RECURSIVE](https://www.postgre...
Wait so that return an array with it all. Cuz a tree would could go many ways
6
|
|
--- 1----
2 4
| |
| |
3 5
Let's say I select 1
I would want to fetch all of the replys and the nest as well as number 6 which is above it.
daring pivot
Wait so that return an array with it all. Cuz a tree would could go many ways
...
Then you can check the other direction too
meager locust
I'm saying basically it returns just a 1d array. It is not nested. Shouldn't it be in sub arrays
daring pivot
No
That would make it difficult to query
meager locust
Wait so let's say there can be multiple references to messages.
Instead of a message pointing to reference there could be multiple references and multiple messages. How would that work cuz wouldn't many to many relationships be lots of extra data duplicated?
meager locust
The row would tell you where it’s referenced from
Like the whole referenced from value won't work if there can be multiple references
tough plover
Correct, that’s where you‘d introduce a relation table storing referencing_id and referenced_id