From 3a9af19d145703254c0e738af31cd4d20047c449 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sun, 15 Jul 2007 04:50:11 +0000 Subject: [PATCH] - columns can be overridden in a reflected table with a "key" attribute different than the column's name, including for primary key columns [ticket:650] - more docs --- CHANGES | 3 +++ doc/build/content/datamapping.txt | 32 +++++++++++++++++++++++++++++++ lib/sqlalchemy/sql.py | 7 +++++++ test/engine/reflection.py | 19 +++++++++++++++++- 4 files changed, 60 insertions(+), 1 deletion(-) diff --git a/CHANGES b/CHANGES index 6caab88289..cdd08b9069 100644 --- a/CHANGES +++ b/CHANGES @@ -62,6 +62,9 @@ this is also mostly to help inheritance scenarios formulate the best choice of primary key columns. [ticket:185] - added 'bind' argument to Sequence.create()/drop(), ColumnDefault.execute() + - columns can be overridden in a reflected table with a "key" + attribute different than the column's name, including for primary key + columns [ticket:650] - some enhancements to "column targeting", the ability to match a column to a "corresponding" column in another selectable. this affects mostly ORM ability to map to complex joins diff --git a/doc/build/content/datamapping.txt b/doc/build/content/datamapping.txt index b0bcf220c3..85c32adb2c 100644 --- a/doc/build/content/datamapping.txt +++ b/doc/build/content/datamapping.txt @@ -562,6 +562,15 @@ But the easiest way to join is automatically, using the `join()` method on `Quer ORDER BY users.oid {'addresses_street', '123 Green Street'} +Each time the `join()` is called on `Query`, the **joinpoint** of the query is moved to be that of the endpoint of the join. As above, when we joined from `users_table` to `addresses_table`, all subsequent criterion used by `filter_by()` are against the `addresses` table. If we wanted to filter back on the starting table again, we can use the `reset_joinpoint()` function: + + {python} + l = session.query(User).join('addresses'). + filter(addresses_table.c.street=='123 Green Street'). + reset_joinpoint().filter_by(user_name='ed').all() + +With `reset_joinpoint()`, we can also issue new `join()`s which will start back from the root table. + In all cases, we can get the `User` and the matching `Address` objects back at the same time, by telling the session we want both. This returns the results as a tuple: {python} @@ -616,6 +625,29 @@ You can also create joins in "reverse", that is, to find an object with a certai addresses.user_id = :users_user_id ORDER BY addresses.oid {'users_user_id': 1, 'addresses_email_address': 'foo@bar.com'} +Sometimes it's necessary to create repeated joins that are independent of each other, even though they reference the same tables. Using our one-to-many setup, an example is to locate users who have two partcular email addresses. We can do this using table aliases: + + {python} + ad1 = addresses_table.alias('ad1') + ad2 = addresses_table.alias('ad2') + {sql}result = session.query(User).filter(and_( + ad1.c.user_id==users.c.user_id, + ad1.c.email_address=='foo@bar.com', + ad2.c.user_id==users.c.user_id, + ad2.c.email_address=='lala@yahoo.com' + )).all() + SELECT users.user_id AS users_user_id, + users.user_name AS users_user_name, users.password AS users_password + FROM users, addresses AS ad1, addresses AS ad2 + WHERE users.user_id=ad1.user_id + AND ad1.email_address=:ad1_email_address + AND users.user_id=ad2.user_id + AND ad2.email_address=:ad2_email_address + ORDER BY users.oid + {'ad1_email_address': 'foo@bar.com', 'ad2_email_address': 'lala@yahoo.com'} + +Version 0.4 of SQLAlchemy will include better ability to issue queries like the above with less verbosity. + ### Loading Relationships {@name=selectrelations} We've seen how the `relation` specifier affects the saving of an object and its child items, and also how it allows us to build joins. How to we get the actual related items loaded ? By default, the `relation()` function indicates that the related property should be attached a *lazy loader* when instances of the parent object are loaded from the database; this is just a callable function that when accessed will invoke a second SQL query to load the child objects of the parent. diff --git a/lib/sqlalchemy/sql.py b/lib/sqlalchemy/sql.py index 0961cd4ee3..4c720dfa2e 100644 --- a/lib/sqlalchemy/sql.py +++ b/lib/sqlalchemy/sql.py @@ -1592,6 +1592,13 @@ class ColumnCollection(util.OrderedProperties): The key attribute of the column will be used as the hash key for this dictionary. """ + + # Allow an aliased column to replace an unaliased column of the + # same name. + if self.has_key(column.name): + other = self[column.name] + if other.name == other.key: + del self[other.name] self[column.key] = column def remove(self, column): diff --git a/test/engine/reflection.py b/test/engine/reflection.py index 26cbe3722a..fc0ff400fb 100644 --- a/test/engine/reflection.py +++ b/test/engine/reflection.py @@ -136,7 +136,24 @@ class ReflectionTest(PersistTest): autoload=True) assert u3.join(a3).onclause == u3.c.id==a3.c.user_id - + + meta4 = MetaData(testbase.db) + u4 = Table('users', meta4, + Column('id', Integer, key='u_id', primary_key=True), + autoload=True) + a4 = Table('addresses', meta4, + Column('id', Integer, key='street', primary_key=True), + Column('street', String(30), key='user_id'), + Column('user_id', Integer, ForeignKey('users.u_id'), + key='id'), + autoload=True) + + assert u4.join(a4).onclause.compare(u4.c.u_id==a4.c.id) + assert list(u4.primary_key) == [u4.c.u_id] + assert len(u4.columns) == 2 + assert len(u4.constraints) == 1 + assert len(a4.columns) == 3 + assert len(a4.constraints) == 2 finally: meta.drop_all() -- 2.47.2