Some to-many relationships have reasonably small size. Others do not. Tens, hundreds, even thousands of objects are all "reasonable". Millions are not. Often relationships from "lookup" / configuration tables are in the later category (e.g. "user_type" -> "user", where "user" has millions of records). My "normal" approach to such relationships is to avoid them all together. I map them on Db* side, but skip them on the Obj* side. Cayenne supports one-way relationships and it works pretty well. The assumption is that there are no realistic scenarios for traversing such relationships in memory all at once. If I ever need all users for a given type, I'd write a query and run it as an iterator, or add an extra qualifier to work with a smaller subset of users.
Today I ran into an issue where I could not easily bypass these relationships (and as expected all these objects are faulted into memory). The scenario is "Deny" delete rule (e.g. when deleting a user_type, check that there are no users for this type, and throw DeleteDenyException otherwise). If I don't map "user_type" -> "user" relationship, I can't setup the "Deny" rule. The delete is still denied due to the DB-side FK constraint, but I can't build a user-friendly error message. I see a few ways to solve this. Anyways: 1. We can move delete rules from ObjRelationship to DbRelationship level. And then implement the delete rule to check in-memory relationship first, and if not faulted, run some form of EXISTS query that checks related records presence without faulting them in memory. That's a model change. And it sidesteps dealing with huge relationships in general, focusing on this single case. Feels like one of those minor edge cases that will require a massive refactoring effort to fix :) 2. We can treat these huge ObjRelationships as a special type of relationships (marked as such in the Modeler), and apply special strategies to them. E.g. pagination on faulting, List.isEmpty() and List.size() resolved without faulting (thus allowing to deal with Deny rule). 3. And the simplest of them - don't map such relationships, don't map delete rules, and handle "Deny" in a 'validateForDelete'. Both 1&2 have benefits and downsides. I am still undecided how to better handle it (and whether I should bother at all, and instead just use #3). In any event I figured I'd mention it here. Perhaps somebody has some thoughts on it. Cheers, Andrus
