Python ORM slqalchemy 中join的使用

Join的使用

第一种方法：简单外键关系
Consider a mapping between two classes User and Address, with a relationship User.addresses representing a collection of Address objects associated with each User. The most common usage of join() is to create a JOIN along this relationship, using the User.addresses attribute as an indicator for how this should occur:

q = session.query(User).join(User.addresses)

Where above, the call to join() along User.addresses will result in SQL equivalent to:

SELECT user.* FROM user JOIN address ON user.id = address.user_id

In the above example we refer to User.addresses as passed to join() as the on clause, that is, it indicates how the “ON” portion of the JOIN should be constructed. For a single-entity query such as the one above (i.e. we start by selecting only from User and nothing else), the relationship can also be specified by its string name:

q = session.query(User).join("addresses")

join() can also accommodate multiple “on clause” arguments to produce a chain of joins, such as below where a join across four related entities is constructed:

q = session.query(User).join("orders", "items", "keywords")

The above would be shorthand for three separate calls to join(), each using an explicit attribute to indicate the source entity:

q = session.query(User).\
        join(User.orders).\
        join(Order.items).\
        join(Item.keywords)

这里写代码片

第二种方法：连接到一个实体或者可选的对象

A second form of join() allows any mapped entity or core selectable construct as a target. In this usage, join() will attempt to create a JOIN along the natural foreign key relationship between two entities:

q = session.query(User).join(Address)

The above calling form of join() will raise an error if either there are no foreign keys between the two entities, or if there are multiple foreign key linkages between them. In the above calling form, join() is called upon to create the “on clause” automatically for us. The target can be any mapped entity or selectable, such as a Table:

q = session.query(User).join(addresses_table)

第三种方法：通过ON从句来连接对象
The third calling form allows both the target entity as well as the ON clause to be passed explicitly. Suppose for example we wanted to join to Address twice, using an alias the second time. We use aliased() to create a distinct alias of Address, and join to it using the target, onclause form, so that the alias can be specified explicitly as the target along with the relationship to instruct how the ON clause should proceed:

a_alias = aliased(Address)

q = session.query(User).\
        join(User.addresses).\
        join(a_alias, User.addresses).\
        filter(Address.email_address=='ed@foo.com').\
        filter(a_alias.email_address=='ed@bar.com')

Where above, the generated SQL would be similar to:

SELECT user.* FROM user
    JOIN address ON user.id = address.user_id
    JOIN address AS address_1 ON user.id=address_1.user_id
    WHERE address.email_address = :email_address_1
    AND address_1.email_address = :email_address_2

The two-argument calling form of join() also allows us to construct arbitrary joins with SQL-oriented “on clause” expressions, not relying upon configured relationships at all. Any SQL expression can be passed as the ON clause when using the two-argument form, which should refer to the target entity in some way as well as an applicable source entity:

q = session.query(User).join(Address, User.id==Address.user_id)

Changed in version 0.7: In SQLAlchemy 0.6 and earlier, the two argument form of join() requires the usage of a tuple: query(User).join((Address, User.id==Address.user_id)). This calling form is accepted in 0.7 and further, though is not necessary unless multiple join conditions are passed to a single join() call, which itself is also not generally necessary as it is now equivalent to multiple calls (this wasn’t always the case).

连接时的灵活性
There is a lot of flexibility in what the “target” can be when using join(). As noted previously, it also accepts Table constructs and other selectables such as alias() and select() constructs, with either the one or two-argument forms:

addresses_q = select([Address.user_id]).\
            where(Address.email_address.endswith("@bar.com")).\
            alias()

q = session.query(User).\
            join(addresses_q, addresses_q.c.user_id==User.id)

join() also features the ability to adapt a relationship() -driven ON clause to the target selectable. Below we construct a JOIN from User to a subquery against Address, allowing the relationship denoted by User.addresses to adapt itself to the altered target:

address_subq = session.query(Address).\
                filter(Address.email_address == 'ed@foo.com').\
                subquery()

q = session.query(User).join(address_subq, User.addresses)

Producing SQL similar to:

SELECT user.* FROM user
    JOIN (
        SELECT address.id AS id,
                address.user_id AS user_id,
                address.email_address AS email_address
        FROM address
        WHERE address.email_address = :email_address_1
    ) AS anon_1 ON user.id = anon_1.user_id

The above form allows one to fall back onto an explicit ON clause at any time:

q = session.query(User).\
        join(address_subq, User.id==address_subq.c.user_id)

控制连接方向
While join() exclusively deals with the “right” side of the JOIN, we can also control the “left” side, in those cases where it’s needed, using select_from(). Below we construct a query against Address but can still make usage of User.addresses as our ON clause by instructing the Query to select first from the User entity:

q = session.query(Address).select_from(User).\
                join(User.addresses).\
                filter(User.name == 'ed')

Which will produce SQL similar to:

SELECT address.* FROM user
    JOIN address ON user.id=address.user_id
    WHERE user.name = :name_1

匿名构造

join() can construct anonymous aliases using the aliased=True flag. This feature is useful when a query is being joined algorithmically, such as when querying self-referentially to an arbitrary depth:

q = session.query(Node).\
        join("children", "children", aliased=True)

When aliased=True is used, the actual “alias” construct is not explicitly available. To work with it, methods such as Query.filter() will adapt the incoming entity to the last join point:

q = session.query(Node).\
        join("children", "children", aliased=True).\
        filter(Node.name == 'grandchild 1')

When using automatic aliasing, the from_joinpoint=True argument can allow a multi-node join to be broken into multiple calls to join(), so that each path along the way can be further filtered:

q = session.query(Node).\
        join("children", aliased=True).\
        filter(Node.name='child 1').\
        join("children", aliased=True, from_joinpoint=True).\
        filter(Node.name == 'grandchild 1')

The filtering aliases above can then be reset back to the original Node entity using reset_joinpoint():

q = session.query(Node).\
        join("children", "children", aliased=True).\
        filter(Node.name == 'grandchild 1').\
        reset_joinpoint().\
        filter(Node.name == 'parent 1)

“`

For an example of aliased=True, see the distribution example XML Persistence which illustrates an XPath-like query system using algorithmic joins.
Parameters:

props – A collection of one or more join conditions, each consisting of a relationship-bound attribute or string relationship name representing an “on clause”, or a single target entity, or a tuple in the form of (target, onclause). A special two-argument calling form of the form target, onclause is also accepted.
aliased=False – If True, indicate that the JOIN target should be anonymously aliased. Subsequent calls to filter() and similar will adapt the incoming criterion to the target alias, until reset_joinpoint() is called.
isouter=False –

If True, the join used will be a left outer join, just as if the Query.outerjoin() method were called. This flag is here to maintain consistency with the same flag as accepted by FromClause.join() and other Core constructs.

New in version 1.0.0.
from_joinpoint=False – When using aliased=True, a setting of True here will cause the join to be from the most recent joined target, rather than starting back from the original FROM clauses of the query.

*See also
Querying with Joins in the ORM tutorial.
Mapping Class Inheritance Hierarchies for details on how join() is used for inheritance relationships.
orm.join() - a standalone ORM-level join function, used internally by Query.join(), which in previous SQLAlchemy versions was the primary ORM-level joining interface.*

例子
连接Users和UserRules两张表 查找出所有满足条件的记录

viewer_list =
self.session.query(Users.id,Users.user_id,Users.user_name,Users.telephone
,Users.py,Users.status,Users.role, UserRules.type.label(“type”)
,UserRules.rule.label(“rule”))\
.outerjoin(UserRules,Users.id == UserRules.user_id)\
.filter(Users.role == ‘viewer’)\
.order_by(sort).offset(beg).limit(count).all()

label()方法实现的时select as的功能。