3 ms·
So now suppose we refactor your database a bit, since all cakes are deserts and we don't really need to store that the fruit cake is both a cake and a dessert.
by einhverfr 14y ago
So now suppose we refactor your database a bit, since all cakes are deserts and we don't really need to store that the fruit cake is both a cake and a dessert.
Suppose we make category have a self-join (add a parent_id field that references category(id)).
Now we want to make sure that when we want to list all deserts, all subcategories are listed too. Assume the possibility of arbitrary depth.
How easy is tht to do in your ORM?
- stevenwei 14y agoGood question. I can't speak for all ORMs, but both SQLAlchemy and Core Data provide direct support for self-referential relationships SQLAlchemy lets you drop down to raw SQL as well if you need to, while still taking care of mapping the result set to objects for you. Core Data is less powerful (and technically not an ORM), but it's the defacto standard on iOS so I'm pretty much stuck with it.
- einhverfr 14y agoSo if you have self-referential relationships of arbitrary depth then can it generate clauses like WITH RECURSIVE or CONNECT BY? Or are you functionally limited to one level of self-joins without dropping to SQL?
- stevenwei 14y agoWITH RECURSIVE is supported in SQLAlchemy now, although the syntax is rather verbose. Not sure about CONNECT BY, I think you may need to drop to SQL for that.
- einhverfr 14y agothat's pretty cool. (I would assume that since WITH RECURSIVE is the standard and CONNECT BY is Oracle's invention that either it would be handled by db-specific extensions or just be another cost of running Oracle)