Can you create a db view for this? I have often tried to build this sort of thing into the db as much as possible to reduce the complexity of the app code and since this Ultimately what db's do well. Of course if you don not have create rights in the db you can't do that...
On Oct 7, 7:55 am, Si <si...@mintsource.org> wrote: > Hi all, > > I have a PostgreSQL database, which I am using via ClojureQL, and > whilst basic relational operations are going smoothly, I'm wondering > how to best tackle the "n+1 selects" issue and more generally how to > construct a graph of maps and arrays from the relational data. > > As a specific example, I might fetch an array of (max 30 per page) > books, but need access to the author via foreign key. I've thought of > 3 methods so far: > > * Leave as n + 1 selects. This might be useful when laziness is really > needed, but almost all the time I would want eager fetching. I'll > always want the author for a given book. > > * Use join to retrieve all the data in one hit. I would have clashing > column names, which prevents ClojureQL result map creation, and would > need to rename the projected fields to include the table name or > similar (there's seemingly no way to instead specify table for columns > at consumption time). Then I'd need to somehow convert the flattened > data to hierarchical. Perhaps "create or reuse" by table name > partitioned columns whilst moving through the array of hash data. > > * Use two selects, one for the books, then one for the authors where > the id is in the foreign key set found by the first query. I can see > that building a nested structure from the two arrays of hash data > would be fairly straightforward. > > It feels like this last method is more simple, or perhaps a > combination might be needed, but having been infantilized by ORMs for > too long, the longer term best-bet is not clear to me, so I'm > soliciting advice. > > Does anybody have any tips on this kind of functional <-> relational > mapping? -- You received this message because you are subscribed to the Google Groups "Clojure" group. To post to this group, send email to clojure@googlegroups.com Note that posts from new members are moderated - please be patient with your first post. To unsubscribe from this group, send email to clojure+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
