On Oct 9, 2012, at 9:54 AM, Benjamin Gonzalez wrote: > > Hi, > > I've been using SQLAlchemy for the last couple of weeks. I'll definitely > call myself a beginner so I apologize if I'm missing something obvious with > this question :)
> I'm working with a database where there is data split across tables with the > exact same structure, so there are cases where I need to query more than one > table and filter by the same criteria. this sounds like you're describing horizontal partitioning. such as if a particular row of data could equally well be in one table or the other, and its only some kind of sharding scheme that determines which table. > Looking around in the documentation I read about Concrete Table Inheritance > and thought that could help me in this case. yeah concrete inheritance is not really intended for this case, though I can see what you're going for. > And perform a query such as: > > session.query(Employee).filter(Employee.name=='test') > > It will apply the name filter after the union is done: > > SELECT pjoin.employee_id AS pjoin_employee_id, pjoin.name AS pjoin_name, > pjoin.type AS pjoin_type, pjoin.manager_data AS pjoin_manager_data, > pjoin.engineer_info AS pjoin_engineer_info > FROM (SELECT employees.employee_id AS employee_id, employees.name AS name, > CAST(NULL AS VARCHAR(50)) AS manager_data, CAST(NULL AS VARCHAR(50)) AS > engineer_info, 'employee' AS type > FROM employees UNION ALL SELECT managers.employee_id AS employee_id, > managers.name AS name, managers.manager_data AS manager_data, CAST(NULL AS > VARCHAR(50)) AS engineer_info, 'manager' AS type > FROM managers UNION ALL SELECT engineers.employee_id AS employee_id, > engineers.name AS name, CAST(NULL AS VARCHAR(50)) AS manager_data, > engineers.engineer_info AS engineer_info, 'engineer' AS type > FROM engineers) AS pjoin > WHERE pjoin.name = :name_1 > > Is there a way to ask SQLAlchemy to apply the filter to each table in the > inner queries? They all have a name column, and in the case of a large > dataset it's helpful because it avoids loading all the rows for each table > first and then applying the WHERE clause. oh OK so you're taking advantage of the UNION generated by polymorphic_union to try filtering across all those tables. this is a funny case because we do have an extension that serves as an example for "horizontal partitioning", which is in sqlalchemy.ext.horizontal_shard, but that example is architected around the idea that you're talking to multiple database backends, each of which has just one table in it. So it doesn't do the UNION thing which of course is preferable if it is possible. One example that might be worth peeking at is how this mapping scheme can be set up without necessarily using Concrete inheritance, though really it's probably easier that you are just using the concrete system, that example is at: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/EntityName . It has a "classical" version and a "declarative" version, though the declarative is a bit awkward. Stick with the concrete if that's easier. As far as the query, to come up with an ad-hoc, filtered UNION would have to be invented. I'd start by working it out manually using Query, then perhaps trying to genericize it, either that you have a simple function that comes up with a Query with unions, or if you really need to do this fluently, as a Query subclass. Here's sort of the idea of that: def query_all_shards(session, criterion): q = None for subclass in MyConcreteBase.__subclasses__(): if q is None: q = session.query(subclass).filter(criterion) else: union_q = session.query(subclass).filter(criterion) q = q.union_all(union_q) return q I haven't tested this, but something like that. Then if you wanted to bake this into Query, you could build a Query subclass that does this, giving it a method like "union_on_shards()" that would trigger a "union" operation similar to the above. There's a Query subclass example in the sqlalchemy/ext/horizontal_shard.py module, and also in some of the wiki examples such as http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PreFilteredQuery . Overall I'd stay away from polymorphic_union as a mapper-level default since it generates huge beasts of queries, which you often won't want. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
