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 -~----------~----~----~----~------~----~------~--~---
