> This approach works for some things, but I think it falls down when it > comes to doing complex database searches, particularly searches generated > ad-hoc on multiple columns in multiple tables.
In general, the user interface you provide for a search will be much higher-level than the SQL that implements it. That's what is gained by making this kind of object: it's a place to put the translation of the business concept "find people in New Jersey" into the four table join that might be needed to find them. > This is why Alzabo is much lower-level than what you have above. I needed > something where I could easily construct queries that might include 1+ > tables, with various types of searches of individual columns in those > tables (equal to, between, less than, like, etc.) with dynamic sorting > (again, on any of the columns in any of the tables, ascending or > descending). I would just write SQL at that point, but I do realize that Alzabo provides more database independence. You could easilly use Alzabo to build the queries that implement the model objects I'm talking about. > With what you're proposing, I think you could easily end up with either: > > A) a ridiculously flexible interface that looks sort of like SQL, except > where it is SQL, except where it's only sort of like SQL, etc. > > B) a ridiculous profusion of classes, methods, or both. I think you're overestimating the number of search variations and model objects that most applications have. For example, Fran's application generates his "stale watches report". There's no need to create a fully-parameterized search interface to Watch objects just for that. Instead, you make a Watches->find_stale() method or something and keep the knowledge of what that means to the database hidden behind that API. At eToys we had a very large and complex (highly normalized) database, and this approach worked very well. I think it works best when you have a complex database requiring complex SQL, because if you just have a bunch of simple 1-1 table mappings there isn't really much to abstract. I'm thinking of trying out SPOPS the next time I do this kind of thing, because it will automatically handle the no-brainer cases (1-1 mappings) and allow me to write the SQL for the complex cases by hand, all with a consistent interface and hooks for caching, etc. > Trying to jam a thick layer of OO-goodness over relational data is asking > for a mess. OO has its place, but if your application is primarily about > the database, I don't think that a heavy OO layer on top of that will do > you much good. There's nothing "thick" or "heavy" about the way I do it. There is no automatic SQL generation, and nothing to prevent me from using any SQL tricks that my database supports. It's just a way of wrapping up chunks of code that implement data-related tasks into an easy task-oriented API for the controller (or other model objects) to act on. - Perrin
