sqlalchemy文档资料翻译（八）

[程序]sqlalchemy文档资料翻译（八） -- 联合查询

在使用 joinedload() 函数创建联接时，我们还可以用多种方式明确的使用联接。例如，为了在 User 和 Address 之间创建内联接，我们仅需要使用 filter() 他们之间的关联列即可。接下来我们使用该方法来一起载入 User 和 Address 实体：

>>> for u, a in session.query(User, Address).filter(User.id==Address.user_id).\
...         filter(Address.email_address=='jack@google.com').all():  
...     print u, a

SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname,
users.password AS users_password, addresses.id AS addresses_id,
addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id
FROM users, addresses
WHERE users.id = addresses.user_id AND addresses.email_address = ?
('jack@google.com',)

<User('jack','Jack Bean', 'gjffdd')> <Address('jack@google.com')>

或者我们可以使用真正的 JOIN 来创建，最通常的用法是使用 join() 方法：

>>> session.query(User).join(Address).\
...         filter(Address.email_address=='jack@google.com').all()

SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password
FROM users JOIN addresses ON users.id = addresses.user_id
WHERE addresses.email_address = ?
('jack@google.com',)

[<User('jack','Jack Bean', 'gjffdd')>]

由于他们之间只有一个外键，因此 join() 知道如何去联接 User 和 Address。如果没有任何外键或者存在多个外键，使用如下的方式会使 join() 工作的更好：

query.join((Address, User.id==Address.user_id))  # explicit condition (note the tuple)
query.join(User.addresses)                       # specify relationship from left to right
query.join((Address, User.addresses))            # same, with explicit target
query.join('addresses')                          # same, using a string

注意当 join() 以明确的对象以及 ON 语句来调用时，我们使用元组参数。这也是为什么多重联接也能正确工作的原因，参考：

session.query(Foo).join(
                        Foo.bars,
                        (Bat, bar.bats),
                        (Widget, Bat.widget_id==Widget.id)
                        )

以上查询会生成类似于如下形式的 SQL 语句： foo JOIN bars ON <onclause> JOIN bats ON <onclause> JOIN widgets ON <onclause>.

join() 函数也可以作为一个独立的函数使用，该函数是一个由 SQL expression language 提供的同名的启用 ORM 的版本。该函数接受两至三个参数（左边，右边，可选的 ON 语句）并通过 select_from() 方法被应用于联接中以指定明确的 FROM 语句：

>>> from sqlalchemy.orm import join
>>> session.query(User).\
...                select_from(join(User, Address, User.addresses)).\
...                filter(Address.email_address=='jack@google.com').all()

SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password
FROM users JOIN addresses ON users.id = addresses.user_id
WHERE addresses.email_address = ?
('jack@google.com',)

[<User('jack','Jack Bean', 'gjffdd')>]

使用 join() 立即查询集合和属性

joinedload() 方法提供的立即查询功能以及 join() 的联合构造能力可以通过使用 contains_eager() 选项合并在一起。这是基于已经联接至某些相关实体的查询的典型用法（多对一的情况下屡见不鲜），此时我们更倾向于相关实体一步到位的载入到结果对象，而不需要浪费额外的查询，也不需要通过 joinedload() 方法自动联接。

>>> from sqlalchemy.orm import contains_eager
>>> for address in session.query(Address).\
...                join(Address.user).\
...                filter(User.name=='jack').\
...                options(contains_eager(Address.user)):
...         print address, address.user

SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname,
users.password AS users_password, addresses.id AS addresses_id,
addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id
FROM addresses JOIN users ON users.id = addresses.user_id
WHERE users.name = ?
('jack',)

<Address('jack@google.com')> <User('jack','Jack Bean', 'gjffdd')>
<Address('j25@yahoo.com')> <User('jack','Jack Bean', 'gjffdd')>

需要注意的是上面的 join 同样也用于将 Address 限制在与 User 对象名为“jack”相关行上。通过内联接的方式填充 Address.user 属性是非常安全的。无论如何，当在联接上进行过滤相当于在一个集合的特定元素上进行过滤，通过 contains_eager() 方式来填充相关集合可能仅仅只填充部分，因为集合自身已经经过了过滤。

使用别名

当使用多表查询，如果同一张表需要被多次访问， SQL 通常需要该表申明一个别名，以区别之。 Query 通过使用 aliased 支持该功能。接下来我们会两次联接 Address 实体，以查询同时拥有两个不同 email 地址的用户：

>>> from sqlalchemy.orm import aliased
>>> adalias1 = aliased(Address)
>>> adalias2 = aliased(Address)
>>> for username, email1, email2 in \
...     session.query(User.name, adalias1.email_address, adalias2.email_address).\
...     join((adalias1, User.addresses), (adalias2, User.addresses)).\
...     filter(adalias1.email_address=='jack@google.com').\
...     filter(adalias2.email_address=='j25@yahoo.com'):
...     print username, email1, email2     

SELECT users.name AS users_name, addresses_1.email_address AS addresses_1_email_address,
addresses_2.email_address AS addresses_2_email_address
FROM users JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id
JOIN addresses AS addresses_2 ON users.id = addresses_2.user_id
WHERE addresses_1.email_address = ? AND addresses_2.email_address = ?
('jack@google.com', 'j25@yahoo.com')

jack jack@google.com j25@yahoo.com

使用子查询

 Query 还适用于生成用于子查询的语句。假设我们希望根据其拥有的 Address 记录数量来载入 User  对象。此问题最佳的生成 SQL 方法是将 user 通过 addresses 数量对 ids 进行分组，并将其与父属进行联接。在本例中，我们使用左外联接以确保即便没有任何地址的用户也能被正确返回：

SELECT users.*, adr_count.address_count FROM users LEFT OUTER JOIN
    (SELECT user_id, count(*) AS address_count FROM addresses GROUP BY user_id) AS adr_count
    ON users.id=adr_count.user_id

通过使用 Query，我们从内到外创建了一个这样的语句。该语句返回一个用于特殊 Query 的 SQL 表达式 - 参考 SQL Expression Language Tutorial：

>>> from sqlalchemy.sql import func
>>> stmt = session.query(Address.user_id, func.count('*').label('address_count')).group_by(Address.user_id).subquery()

The func keyword generates SQL functions, and the subquery() method on Query produces a SQL expression construct representing a SELECT statement embedded within an alias (it’s actually shorthand for query.statement.alias()).

Once we have our statement, it behaves like a Table construct, such as the one we created for users at the start of this tutorial. The columns on the statement are accessible through an attribute called c:

>>> for u, count in session.query(User, stmt.c.address_count).\
...     outerjoin((stmt, User.id==stmt.c.user_id)).order_by(User.id): 
...     print u, count
<User('ed','Ed Jones', 'f8s7ccs')> None
<User('wendy','Wendy Williams', 'foobar')> None
<User('mary','Mary Contrary', 'xxg527')> None
<User('fred','Fred Flinstone', 'blah')> None
<User('jack','Jack Bean', 'gjffdd')> 2

Selecting Entities from Subqueries

Above, we just selected a result that included a column from a subquery. What if we wanted our subquery to map to an entity ? For this we use aliased() to associate an “alias” of a mapped class to a subquery:

>>> stmt = session.query(Address).filter(Address.email_address != 'j25@yahoo.com').subquery()
>>> adalias = aliased(Address, stmt)
>>> for user, address in session.query(User, adalias).join((adalias, User.addresses)): 
...     print user, address
<User('jack','Jack Bean', 'gjffdd')> <Address('jack@google.com')>

Using EXISTS

The EXISTS keyword in SQL is a boolean operator which returns True if the given expression contains any rows. It may be used in many scenarios in place of joins, and is also useful for locating rows which do not have a corresponding row in a related table.

There is an explicit EXISTS construct, which looks like this:

>>> from sqlalchemy.sql import exists
>>> stmt = exists().where(Address.user_id==User.id)
>>> for name, in session.query(User.name).filter(stmt):   
...     print name
jack

The Query features several operators which make usage of EXISTS automatically. Above, the statement can be expressed along the User.addresses relationship using any():

>>> for name, in session.query(User.name).filter(User.addresses.any()):   
...     print name
jack

any() takes criterion as well, to limit the rows matched:

>>> for name, in session.query(User.name).\
...     filter(User.addresses.any(Address.email_address.like('%google%'))):   
...     print name
jack

has() is the same operator as any() for many-to-one relationships (note the ~ operator here too, which means “NOT”):

>>> session.query(Address).filter(~Address.user.has(User.name=='jack')).all() 
[]