Thanks Mike for your response. In your response you mention having my own function around polymorphic_union and call it on every request based on the table list in the request params. Instead it seems to me that I can construct my union using polymorphic_union once for good (with all the tables) and use with_polymorphic on each request.
Is my understanding correct? Eric 2008/8/31, Michael Bayer <[EMAIL PROTECTED]>: > > > On Aug 31, 2008, at 4:44 PM, Eric Lemoine wrote: > >> >> Hello >> >> Here's my case. I have 3 tables, "summits", "parkings", and "huts". >> They all have an "elevation" column. And I want to do queries like >> that: "the summits, parkings, and huts that are higher than 2000 >> meters", "the summits that are lower than 1000 meters", "the parkings >> and huts that are between 500 and 2000 meters". What's to be noted is >> that the list of tables to query isn't fixed, it actually depends on >> some parameter in the received HTTP request. >> >> The more naive approach involves doing separate queries. For example >> if the HTTP request has tables=summits,huts, then the queries >> s.query(Summit).filter() and s.query(Hut).filter() are performed. This >> might not be efficient because of the separate database queries. >> >> I thought about using Concrete Table Inheritance for that, but I'm not >> sure if it can meet my "the list of tables to query varies from one >> HTTP request to another". >> >> I'd appreciate some advice and guidance on that issue. > > > if the tables must be three separate tables, and not linked to a > common "inherited" table which contains the attributes common to all > three, then that is exactly concrete table inheritance. The only way > to issue SQL across all of them in one execution, whether or not you > consider the pattern to be "concrete inheritance", is to use a UNION > (or UNION ALL which may be more efficient). SQLA's concrete feature > does support linking a "base" mapper to a UNION query, and I think > you'll find it works pretty well. You can vary the specifics of the > UNION which is issued for a particular Query, using the > with_polymorphic() method (select_from() and from_statement() can work > too). The reason using SQLA's "inheritance" as opposed to just using > union() by itself is advantageous, is that Query can return classes > polymorhically, that is, it knows to return a Hut, Summit or Parking > based on the "discriminator" column. > > There's two main issues with concrete - one is that its slightly > awkward to construct the UNION, SQLA would like you to construct this > query semi-manually using the "polymorphic_union" function (it can > also be constructed using the plain union(table1, table2, ...) > construct, polymorphic_union() is just a helper) and this UNION query > quickly becomes inefficient if it needs to be joined to other tables > or nested in a subquery, for example. Constructing it on a per-query > basis implies you'd want to build yourself a function that can call > polymorphic_union() with the right arguments based on an incoming set > of classes (or tables, however your application needs to do it), so > there's some awkwardness there which you wouldn't have with joined > table inheritance. > > The other is that concrete inheritance plays poorly with relations to > other tables which are shared among more than one class in the > hierarchy, since a foreign key is needed for each individual table > relationship and SQLA needs you to define individual relation()s for > each one. Its always much easier to use single or joined-table > inheritance for a hierarchy where the base class or classes define > much of the behavior/attributes of subclasses. > > > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
