I have free reign over the db. I think a view would simplify the join approach from the app perspective, and this might be a good idea when the db is normalizing data not needed to be seen by the app as separate "objects", but I sense a snag with join. It would work for 1-1 relationships, but for 1- many, my pagination requirements would cull needed table rows (1 book with 29 authors would saturate my 30 row retrieval limits). I'd need to execute my base query of the books with limits via sub-select, then eager fetch join at a higher level.
Using two queries is definitely looking much simpler at this point, and I can't see myself breaching SELECT "IN" limitations for the number of passed IDs I might be handling. The process of manually eager fetching works as hoped in experimental code (I get a nice functional-friendly set of all the data I need), and so I'm now thinking on how I might hook that in to ClojureQL. My pagination is currently taking a ClojureQL relational table object (before deref), cloning the query, and running one refined with a count function, one refined for limited data retreival. That works, but it won't if the eager fetching is part of the query passed up to the paginator doing this. That's because I'd need to have processed the query already, to find out the IDs for prefetch, and the with- paginated-results is sitting up at controller level currently ( & wanting the query object, not it's result set). Perhaps I can push pagination down into the IO portion of the model code base, setting up a dynamic variable for "page" at request handling time. When the model wishes to fetch some books, it discovers it is in paged mode, and refines the query appropriately, leaving the eager fetching to operate higher up the stack. ....Bah! Feels too magical and I'd prefer explicit parameter passing unless it's a real chore (like i18n locale handling throughout a request). It might actually be simple to define my query code as taking options, then explicitly passing in the page as one. This then leaves the trickiness of spawning the extra count query when the ClojureQL query is deref-ed, and updating the metadata of the result set vector with a total count value. Actually, the total count could be lazily generated. Rather than wrap anything coming back from ClojureQL, I'd prefer to keep it as-is, enabling further query refinement. Any ideas on hooking in to ClojureQL this way? Thanks On Oct 9, 4:08 pm, Base <basselh...@gmail.com> wrote: > 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
