Writing SELECT statements for ORM Mapped Classes¶

About this Document

この節では、 Declaring Mapped Classes 節に示されている、 SQLAlchemy Unified Tutorial で最初に説明されているORMマッピングを使用します。

SELECT文は、 Select オブジェクトを返す select() 関数によって生成されます。返すエンティティやSQL式(つまり”columns”節)は、位置的に関数に渡されます。そこから、以下に示す Select.where() メソッドのような、完全な文を生成するための追加のメソッドが使用されます:

>>> from sqlalchemy import select
>>> stmt = select(User).where(User.name == "spongebob")

完成した Select オブジェクトが与えられると、ORM内で実行して行を戻すために、そのオブジェクトは Session.execute() に渡され、そこで Result オブジェクトが返されます:

>>> result=session.execute(stmt)SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[...] ('spongebob',)
>>> foruser_objinresult.scalars():...     print(f"{user_obj.name} {user_obj.fullname}")spongebob Spongebob Squarepants

Selecting ORM Entities and Attributes¶

select() 構文は、マップされたクラスや、マップされた列を表すクラスレベルの属性を含むORMエンティティを受け付けます。これらは構築時に:term:ORMアノテーション付き FromClause と ColumnElement 要素に変換されます。

ORM注釈付きエンティティを含む Select オブジェクトは、通常、 Connection オブジェクトではなく、 Session オブジェクトを使って実行されます。これにより、ORMマップされたオブジェクトのインスタンスが返されるなど、ORM関連の機能が有効になります。 Connection を直接使うと、結果の行には列レベルのデータだけが含まれます。

Selecting ORM Entities¶

以下では、 User エンティティから選択して、 User がマップされた Table から選択する Select を生成します。:

>>> result=session.execute(select(User).order_by(User.id))SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.id
[...] ()

ORMエンティティから選択する場合、エンティティ自体は、一連の個別の列ではなく、単一の要素を持つ行として結果に返されます。例えば、上記の Result は、1行に1つの要素しか持たない Row オブジェクトを返し、その要素は User オブジェクトを保持します:

>>> result.all()
[(User(id=1, name='spongebob', fullname='Spongebob Squarepants'),),
 (User(id=2, name='sandy', fullname='Sandy Cheeks'),),
 (User(id=3, name='patrick', fullname='Patrick Star'),),
 (User(id=4, name='squidward', fullname='Squidward Tentacles'),),
 (User(id=5, name='ehkrabs', fullname='Eugene H. Krabs'),)]

ORMエンティティを含む単一要素の行のリストを選択する場合、一般的には Row オブジェクトの生成をスキップし、代わりにORMエンティティを直接受け取ります。これは、 Session.execute() メソッドではなく、 Session.scalars() メソッドを実行することで、行ではなく単一要素を生成する ScalarResult オブジェクトが返されるようにすることで、最も簡単に実現できます:

>>> session.scalars(select(User).order_by(User.id)).all()SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.id
[...] ()
[User(id=1, name='spongebob', fullname='Spongebob Squarepants'), User(id=2, name='sandy', fullname='Sandy Cheeks'), User(id=3, name='patrick', fullname='Patrick Star'), User(id=4, name='squidward', fullname='Squidward Tentacles'), User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')]

Session.scalars() メソッドを呼び出すのは、 Session.execute() を呼び出して Result オブジェクトを受け取ってから、 Result.scalars() を呼び出して ScalarResult オブジェクトを受け取るのと同じです。

Selecting Multiple ORM Entities Simultaneously¶

select() 関数は、複数のORMクラスを要求することも含めて、任意の数のORMクラスや列式を一度に受け付けます。複数のORMクラスから選択する場合、クラス名に基づいてそれぞれの結果行に名前が付けられます。以下の例では、 User と Address に対するSELECTの結果行は、 User と Address という名前でそれらを参照します:

>>> stmt=select(User,Address).join(User.addresses).order_by(User.id,Address.id)>>> forrowinsession.execute(stmt):...     print(f"{row.User.name} {row.Address.email_address}")SELECT user_account.id, user_account.name, user_account.fullname,
address.id AS id_1, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
ORDER BY user_account.id, address.id
[...] ()
spongebob spongebob@sqlalchemy.orgsandy sandy@sqlalchemy.orgsandy squirrel@squirrelpower.orgpatrick pat999@aol.comsquidward stentcl@sqlalchemy.org

行内のこれらのエンティティに異なる名前を割り当てたい場合は、 aliased.name パラメータを使用して aliased() 構文を使用し、明示的な名前でエイリアスを付けます:

>>> fromsqlalchemy.ormimportaliased>>> user_cls=aliased(User,name="user_cls")>>> email_cls=aliased(Address,name="email")>>> stmt=(...     select(user_cls, email_cls)...     .join(user_cls.addresses.of_type(email_cls))...     .order_by(user_cls.id, email_cls.id)... )>>> row=session.execute(stmt).first()SELECT user_cls.id, user_cls.name, user_cls.fullname,
email.id AS id_1, email.user_id, email.email_address
FROM user_account AS user_cls JOIN address AS email
ON user_cls.id = email.user_id ORDER BY user_cls.id, email.id
[...] ()
>>> print(f"{row.user_cls.name} {row.email.email_address}")spongebob spongebob@sqlalchemy.org

上のaliased形式については、 Using Relationship to join between aliased targets で詳しく説明しています。

既存の Select 構文は、 Select.add_columns() メソッドを使って、ORMクラスや列式をcolumns節に追加することもできます。この形式を使って、上記と同じ文を生成することもできます:

>>> stmt=(...     select(User).join(User.addresses).add_columns(Address).order_by(User.id, Address.id)... )>>> print(stmt)SELECT user_account.id, user_account.name, user_account.fullname,
address.id AS id_1, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
ORDER BY user_account.id, address.id

Selecting Individual Attributes¶

上記の文は、以下の実行時のデモで示すように、 Row オブジェクトを name 列と email_address 列と共に返します。:

>>> for row in result:
...     print(f"{row.name}  {row.email_address}")
spongebob  spongebob@sqlalchemy.org
sandy  sandy@sqlalchemy.org
sandy  squirrel@squirrelpower.org
patrick  pat999@aol.com
squidward  stentcl@sqlalchemy.org

Grouping Selected Attributes with Bundles¶

Bundle 構文は拡張可能なORMのみの構文で、列式の集合を結果行にグループ化することができます:

>>> fromsqlalchemy.ormimportBundle>>> stmt=select(...     Bundle("user", User.name, User.fullname),...     Bundle("email", Address.email_address),... ).join_from(User, Address)>>> forrowinsession.execute(stmt):...     print(f"{row.user.name} {row.user.fullname} {row.email.email_address}")SELECT user_account.name, user_account.fullname, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
[...] ()
spongebob Spongebob Squarepants spongebob@sqlalchemy.orgsandy Sandy Cheeks sandy@sqlalchemy.orgsandy Sandy Cheeks squirrel@squirrelpower.orgpatrick Patrick Star pat999@aol.comsquidward Squidward Tentacles stentcl@sqlalchemy.org

Bundle は軽量ビューやカスタム列グループの作成に役立つ可能性があります。 Bundle をサブクラス化して、別のデータ構造を返すこともできます。例として Bundle.create_row_processor() を参照してください。

See also

Bundle

Bundle.create_row_processor()

Selecting ORM Aliases¶

Using Aliases のチュートリアルで説明したように、ORMエンティティのSQLエイリアスを作成するには、マップされたクラスに対して aliased() 構文を使用します:

>>> fromsqlalchemy.ormimportaliased>>> u1=aliased(User)>>> print(select(u1).order_by(u1.id))SELECT user_account_1.id, user_account_1.name, user_account_1.fullname
FROM user_account AS user_account_1 ORDER BY user_account_1.id

Table.alias() を使用する場合と同様に、SQLエイリアスは匿名で名前が付けられます。明示的な名前を持つ行からエンティティを選択する場合は、 aliased.name パラメータも渡すことができます:

>>> fromsqlalchemy.ormimportaliased>>> u1=aliased(User,name="u1")>>> stmt=select(u1).order_by(u1.id)>>> row=session.execute(stmt).first()SELECT u1.id, u1.name, u1.fullname
FROM user_account AS u1 ORDER BY u1.id
[...] ()
>>> print(f"{row.u1.name}")spongebob

See also

aliased コンストラクトは、次のようないくつかのユースケースで中心的な役割を果たします。:

ORMでサブクエリを使用します。セクション Selecting Entities from Subqueries と Joining to Subqueries では、このことについてさらに説明します。

結果セット内のエンティティの名前を制御します。例については Selecting Multiple ORM Entities Simultaneously を参照してください。

同じORMエンティティに複数回結合します。例については、 ref:orm_queryguide_joining_relationships_aliased を参照してください。

Getting ORM Results from Textual Statements¶

ORMは、他のソースからのSELECT文からのエンティティの読み込みをサポートしています。典型的な使用例はテキストのSELECT文で、SQLAlchemyでは text() 構文を使用して表現されます。 text() 構文は、文が読み込むORMにマップされた列に関する情報で拡張できます。これをORMエンティティ自体に関連付けて、この文に基づいてORMオブジェクトを読み込むことができます。

テキストのSQL文をロードしたい場合:

>>> from sqlalchemy import text
>>> textual_sql = text("SELECT id, name, fullname FROM user_account ORDER BY id")

TextClause.columns() メソッドを使用して、文に列情報を追加することができます。このメソッドが呼び出されると、 TextClause オブジェクトは TextualSelect オブジェクトに変換されます。このオブジェクトは Select 構文と同等の役割を持ちます。 TextClause.columns() メソッドには通常、 Column オブジェクトまたは同等のものが渡されます。この場合、ORMにマップされた属性を User クラスで直接利用できます。

>>> textual_sql = textual_sql.columns(User.id, User.name, User.fullname)

これで、”id”、”name”、”fullname”列を別々にロードできるORM設定のSQL構文ができました。代わりにこのSELECT文を完全な User エンティティのソースとして使用するには、 Select.from_statement() メソッドを使用して、これらの列を通常のORM対応の Select 構文にリンクします。

>>> orm_sql = select(User).from_statement(textual_sql)
>>> for user_obj in session.execute(orm_sql).scalars():
...     print(user_obj)
{execsql}SELECT id, name, fullname FROM user_account ORDER BY id
[...] (){stop}
User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=2, name='sandy', fullname='Sandy Cheeks')
User(id=3, name='patrick', fullname='Patrick Star')
User(id=4, name='squidward', fullname='Squidward Tentacles')
User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')

同じ TextualSelect オブジェクトを、 TextualSelect.subquery() メソッドを使ってサブクエリに変換し、 aliased() 構文を使ってそのオブジェクトの User エンティティにリンクすることもできます。これは、以下の Selecting Entities from Subqueries で説明するのと同様の方法です:

>>> orm_subquery=aliased(User,textual_sql.subquery())>>> stmt=select(orm_subquery)>>> foruser_objinsession.execute(stmt).scalars():...     print(user_obj)SELECT anon_1.id, anon_1.name, anon_1.fullname
FROM (SELECT id, name, fullname FROM user_account ORDER BY id) AS anon_1
[...] ()
User(id=1, name='spongebob', fullname='Spongebob Squarepants')User(id=2, name='sandy', fullname='Sandy Cheeks')User(id=3, name='patrick', fullname='Patrick Star')User(id=4, name='squidward', fullname='Squidward Tentacles')User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')

TextualSelect を Select.from_statement() で直接使用する場合と aliased() を使用する場合の違いは、前者の場合、結果のSQLに副問い合わせが生成されないことです。これは、パフォーマンスや複雑さの観点から有利な場合があります。

Selecting Entities from Subqueries¶

前のセクションで説明した aliased() 構文は、 Select.subquery() などのメソッドから派生した Subuqery 構文と一緒に使用して、ORMエンティティをそのサブクエリから返される列にリンクすることができます。サブクエリによって提供される列とエンティティがマップされる列との間には、 列の対応 の関係がなければなりません。つまり、サブクエリは、次の例のように、最終的にこれらのエンティティから派生する必要があります。:

>>> inner_stmt=select(User).where(User.id<7).order_by(User.id)>>> subq=inner_stmt.subquery()>>> aliased_user=aliased(User,subq)>>> stmt=select(aliased_user)>>> foruser_objinsession.execute(stmt).scalars():...     print(user_obj) SELECT anon_1.id, anon_1.name, anon_1.fullname
FROM (SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
FROM user_account
WHERE user_account.id < ? ORDER BY user_account.id) AS anon_1
[generated in ...] (7,)
User(id=1, name='spongebob', fullname='Spongebob Squarepants')User(id=2, name='sandy', fullname='Sandy Cheeks')User(id=3, name='patrick', fullname='Patrick Star')User(id=4, name='squidward', fullname='Squidward Tentacles')User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')

Joining to Subqueries

Selecting Entities from UNIONs and other set operations¶

union() 関数と union_all() 関数は最も一般的な集合演算で、 except_(),:func:_sql.intersect などの他の集合演算とともに、 CompoundSelect として知られるオブジェクトを提供します。これは、set-operationキーワードで結合された複数の Select 構成体で構成されています。ORMエンティティは、前に Getting ORM Results from Textual Statements で説明した Select.from_statement() メソッドを使用して、単純な複合選択から選択できます。このメソッドでは、UNION文はレンダリングされる完全な文であり、 Select.from_statement() が使用された後に追加の基準を追加することはできません:

>>> fromsqlalchemyimportunion_all>>> u=union_all(...     select(User).where(User.id < 2), select(User).where(User.id == 3)... ).order_by(User.id)>>> stmt=select(User).from_statement(u)>>> foruser_objinsession.execute(stmt).scalars():...     print(user_obj)SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.id < ? UNION ALL SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.id = ? ORDER BY id
[generated in ...] (2, 3)
User(id=1, name='spongebob', fullname='Spongebob Squarepants')User(id=3, name='patrick', fullname='Patrick Star')

CompoundSelect 構文は、以前に Selecting Entities from Subqueries で説明したように、サブクエリに編成し、 aliased() を使用してORMエンティティにリンクすることで、さらに修正できるクエリ内でより柔軟に使用できます。次の例では、まず CompoundSelect.subquery() を使用してUNION ALL文のサブクエリを作成し、次にそれを aliased() 構文にパッケージ化します。この構文では、 select() 構文内の他のマップされたエンティティと同様に使用できます。エクスポートされた列に基づいてフィルタリングや基準による順序付けを追加することもできます。

>>> subq = union_all(
...     select(User).where(User.id < 2), select(User).where(User.id == 3)
... ).subquery()
>>> user_alias = aliased(User, subq)
>>> stmt = select(user_alias).order_by(user_alias.id)
>>> for user_obj in session.execute(stmt).scalars():
...     print(user_obj)
{execsql}SELECT anon_1.id, anon_1.name, anon_1.fullname
FROM (SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
FROM user_account
WHERE user_account.id < ? UNION ALL SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
FROM user_account
WHERE user_account.id = ?) AS anon_1 ORDER BY anon_1.id
[generated in ...] (2, 3)
{stop}User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=3, name='patrick', fullname='Patrick Star')

Joins¶

Select.join() メソッドと Select.join_from() メソッドは、SELECT文に対してSQL JOINを作成するために使用されます。

このセクションでは、これらのメソッドのORMユースケースについて詳しく説明します。コアの観点からのそれらの使用の一般的な概要については、 SQLAlchemy Unified Tutorial の Explicit FROM clauses and JOINs を参照してください。

2.0 style クエリのORMコンテキストでの Select.join() の使い方は、レガシーなユースケースを除けば、 1.x style クエリでの Query.join() メソッドの使い方とほとんど同じです。

Simple Relationship Joins¶

2つのクラス User と Address の間に、それぞれの User に関連付けられた Address オブジェクトの集合を表す User.addresses という関係がある場合を考えてみましょう。 Select.join() の最も一般的な使い方は、この関係に沿ってJOINを作成することです。これは、 User.addresses 属性をどのように発生するかを示すインジケータとして使用します。:

>>> stmt = select(User).join(User.addresses)

上記の場合、 Select.join() を`User.addresses`とともに呼び出すと、SQLはほぼ次のようになります:

>>> print(stmt)SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account JOIN address ON user_account.id = address.user_id

上の例では、 Select.join() に渡された User.addresses を”on節”として参照しています。つまり、JOINの”ON”部分がどのように構築されるべきかを示しています。

Tip

Chaining Multiple Joins¶

結合のチェーンを構築するには、複数の Select.join() 呼び出しを使用することができます。relationship-bound属性は、結合の左側と右側の両方を同時に意味します。追加のエンティティ Order と Item を考えてみましょう。ここで、 User.orders 関係は Order エンティティを参照し、 Order.items 関係は関連テーブル order_items を介して Item エンティティを参照します。2つの Select.join() 呼び出しは、最初に User から Order へのJOIN、2番目に Order から Item へのJOINになります。ただし、 Order.items は many to many 関係なので、2つの別々のJOIN要素が生成され、結果のSQLには合計3つのJOIN要素が生成されます。

>>> stmt = select(User).join(User.orders).join(Order.items)
>>> print(stmt)
{printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN user_order ON user_account.id = user_order.user_id
JOIN order_items AS order_items_1 ON user_order.id = order_items_1.order_id
JOIN item ON item.id = order_items_1.item_id

Select.join() メソッドへの各呼び出しの順序は、新しいターゲットを指定する前に、結合元の”左側”がFROMのリストに存在する必要がある場合にのみ意味があります。例えば、 Select.join() は、 select(User).join(Order.items).join(User.orders) を指定した場合、正しく結合する方法を知らず、エラーを発生させます。正しい方法では、 Select.join() メソッドは、SQLのJOIN句のレンダリング方法と一致するように呼び出され、各呼び出しはその前にあるものからの明確なリンクを表す必要があります。

FROM句で対象とする要素はすべて、FROMの結合を継続するための潜在的なポイントとして使用できます。上記の User エンティティから結合する他の要素を追加し続けることができます。たとえば、 User.addresses 関係を結合のチェーンに追加します。

>>> stmt = select(User).join(User.orders).join(Order.items).join(User.addresses)
>>> print(stmt)
{printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN user_order ON user_account.id = user_order.user_id
JOIN order_items AS order_items_1 ON user_order.id = order_items_1.order_id
JOIN item ON item.id = order_items_1.item_id
JOIN address ON user_account.id = address.user_id

Joins to a Target Entity¶

Select.join() の2番目の形式では、マップされたエンティティまたはコアの選択可能な構成体をターゲットとして使用できます。この使用法では、 Select.join() は、2つのエンティティ間の自然な外部キー関係を使用して、JOINのON句を推測しようとします:

>>> stmt=select(User).join(Address)>>> print(stmt)SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account JOIN address ON user_account.id = address.user_id

上記の呼び出し形式では、 Select.join() が呼び出されて”on節”が自動的に推測されます。この呼び出し形式は、2つのマップされた Table 構成体の間に ForeignKeyConstraint が設定されていない場合、またはそれらの間に複数の ForeignKeyConstraint があり、使用する適切な制約があいまいな場合に、最終的にエラーを発生させます。

Note

ON句を指定せずに Select.join() または Select.join_from() を使用する場合、ORMで設定された relationship() 構文は 考慮されません 。JOINのON句を推測しようとすると、マップされた Table オブジェクトのレベルでエンティティ間に設定された ForeignKeyConstraint 関係のみが調べられます。

Joins to a Target with an ON Clause¶

3番目の呼出し形式では、ターゲット・エンティティとON句の両方を明示的に渡すことができます。ON句としてSQL式を含む例を次に示します。

>>> stmt = select(User).join(Address, User.id == Address.user_id)
>>> print(stmt)
{printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account JOIN address ON user_account.id = address.user_id

式ベースのON句は、 Simple Relationship Joins で使用されるのと同じように、 relationship() でバインドされた属性にすることもできます:

>>> stmt=select(User).join(Address,User.addresses)>>> print(stmt)SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account JOIN address ON user_account.id = address.user_id

上の例は、2つの異なる方法で Address の対象を示すという点で冗長に見えますが、この形式の有用性はエイリアスされたエンティティに結合するときに明らかになります。例については Using Relationship to join between aliased targets を参照してください。

Combining Relationship with Custom ON Criteria¶

relationship() 構文によって生成されたON句は、追加の条件で拡張される可能性があります。これは、関係パス上の特定の結合の範囲を簡単に制限する場合や、 joinedload() や selectinload() のようなローダー戦略を設定する場合に便利です。 PropComparator.and_() メソッドは、ANDを介してJOINのON句に結合される一連のSQL式を位置的に受け入れます。例えば、 User から Address にJOINしたいが、ON条件を特定のメールアドレスだけに制限したい場合は、次のようにします。:

>>> stmt=select(User.fullname).join(...     User.addresses.and_(Address.email_address == "squirrel@squirrelpower.org")... )>>> session.execute(stmt).all()SELECT user_account.fullname
FROM user_account
JOIN address ON user_account.id = address.user_id AND address.email_address = ?
[...] ('squirrel@squirrelpower.org',)
[('Sandy Cheeks',)]

See also

PropComparator.and_() メソッドは、 joinedload() や selectinload() のようなローダ方式でも動作します。 Adding Criteria to loader options の節を参照してください。

Using Relationship to join between aliased targets¶

relationship() で結合された属性を使用してON句を示す結合を構築する場合、 Joins to a Target with an ON Clause に示されている2つの引数の構文を拡張して、 aliased() 構文を使用して、ON句を示すために relationship() で結合された属性を使用しながら、結合のターゲットとしてSQLエイリアスを示すことができます。以下の例では、 User エンティティが Address エンティティに対して2つの異なる aliased() 構成体に2回結合されています。:

>>> address_alias_1=aliased(Address)>>> address_alias_2=aliased(Address)>>> stmt=(...     select(User)...     .join(address_alias_1, User.addresses)...     .where(address_alias_1.email_address == "patrick@aol.com")...     .join(address_alias_2, User.addresses)...     .where(address_alias_2.email_address == "patrick@gmail.com")... )>>> print(stmt)SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN address AS address_1 ON user_account.id = address_1.user_id
JOIN address AS address_2 ON user_account.id = address_2.user_id
WHERE address_1.email_address = :email_address_1
AND address_2.email_address = :email_address_2

同じパターンは、修飾子 PropComparator.of_type() を使ってより簡潔に表現することができます。これは relationship() -bound属性に適用され、1つのステップでターゲットを示すためにターゲットエンティティに沿って渡されます。次の例では、 PropComparator.of_type() を使って、今説明したものと同じSQL文を生成します:

>>> print(...     select(User)...     .join(User.addresses.of_type(address_alias_1))...     .where(address_alias_1.email_address == "patrick@aol.com")...     .join(User.addresses.of_type(address_alias_2))...     .where(address_alias_2.email_address == "patrick@gmail.com")... )SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN address AS address_1 ON user_account.id = address_1.user_id
JOIN address AS address_2 ON user_account.id = address_2.user_id
WHERE address_1.email_address = :email_address_1
AND address_2.email_address = :email_address_2

relationship() を利用して**エイリアスされたエンティティから**結合を構築するために、属性は aliased() 構文から直接利用できます:

>>> user_alias_1=aliased(User)>>> print(select(user_alias_1.name).join(user_alias_1.addresses))SELECT user_account_1.name
FROM user_account AS user_account_1
JOIN address ON user_account_1.id = address.user_id

Joining to Subqueries¶

結合のターゲットは、subuqeryを含む任意の 選択可能な エンティティにすることができます。ORMを使用する場合、これらのターゲットは aliased() 構文で記述されるのが一般的ですが、特に結合されたエンティティが結果に返されない場合には、これは厳密には必要ありません。例えば、 User エンティティから Address エンティティに結合するには、 Address エンティティが行限定サブクエリとして表されている場合、まず Select.subquery() を使用して Subquery オブジェクトを構築し、これを Select.join() メソッドのターゲットとして使用できます:

>>> subq=select(Address).where(Address.email_address=="pat999@aol.com").subquery()>>> stmt=select(User).join(subq,User.id==subq.c.user_id)>>> print(stmt)SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
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_account.id = anon_1.user_id

上記のSELECT文を Session.execute() で呼び出すと、 User エンティティを含む行が返されますが、 Address エンティティは返されません。結果セットに返されるエンティティのセットに Address エンティティを含めるには、 Address エンティティと Subquery オブジェクトに対して aliased() オブジェクトを作成します。また、 aliased() 構造体に名前を付けることもできます。たとえば、以下で使用する address は、結果行で名前で参照できるようにします。

>>> address_subq = aliased(Address, subq, name="address")
>>> stmt = select(User, address_subq).join(address_subq)
>>> for row in session.execute(stmt):
...     print(f"{row.User} {row.address}")
{execsql}SELECT user_account.id, user_account.name, user_account.fullname,
anon_1.id AS id_1, anon_1.user_id, anon_1.email_address
FROM user_account
JOIN (SELECT address.id AS id,
address.user_id AS user_id, address.email_address AS email_address
FROM address
WHERE address.email_address = ?) AS anon_1 ON user_account.id = anon_1.user_id
[...] ('pat999@aol.com',){stop}
User(id=3, name='patrick', fullname='Patrick Star') Address(id=4, email_address='pat999@aol.com')

Joining to Subqueries along Relationship paths¶

前のセクションで説明したサブクエリ形式は、 Using Relationship to join between aliased targets で示された形式の1つを使用して、 relationship() -bound属性を使用して、より具体的に表現することができます。たとえば、特定の relationship() の結合に沿った結合を保証しながら同じ結合を作成するには、 PropComparator.of_type() メソッドを使用して、結合のターゲットである Subquery オブジェクトを含む aliased() 構造体を渡します:

>>> address_subq=aliased(Address,subq,name="address")>>> stmt=select(User,address_subq).join(User.addresses.of_type(address_subq))>>> forrowinsession.execute(stmt):...     print(f"{row.User} {row.address}")SELECT user_account.id, user_account.name, user_account.fullname,
anon_1.id AS id_1, anon_1.user_id, anon_1.email_address
FROM user_account
JOIN (SELECT address.id AS id,
address.user_id AS user_id, address.email_address AS email_address
FROM address
WHERE address.email_address = ?) AS anon_1 ON user_account.id = anon_1.user_id
[...] ('pat999@aol.com',)
User(id=3, name='patrick', fullname='Patrick Star') Address(id=4, email_address='pat999@aol.com')

Subqueries that Refer to Multiple Entities¶

複数のORMエンティティにまたがる列を含む副問い合わせは、一度に複数の aliased() 構文に適用され、同じ Select 構文で各エンティティに関して別々に使用される可能性があります。レンダリングされたSQLは、このような aliased() 構文をすべて同じ副問い合わせとして扱い続けますが、ORM/Pythonの観点からは、適切な aliased() 構文を使用することで、異なる戻り値とオブジェクト属性を参照できます。

例えば、 User と Address の両方を参照するサブクエリがあるとします。:

>>> user_address_subq = (
...     select(User.id, User.name, User.fullname, Address.id, Address.email_address)
...     .join_from(User, Address)
...     .where(Address.email_address.in_(["pat999@aol.com", "squirrel@squirrelpower.org"]))
...     .subquery()
... )

それぞれ同じオブジェクトを参照する aliased() 構文を User と Address の両方に対して作ることができます:

>>> user_alias = aliased(User, user_address_subq, name="user")
>>> address_alias = aliased(Address, user_address_subq, name="address")

両方の実体から選択する Select 構文は副問い合わせを一度レンダリングしますが、結果行のコンテキストでは同時に User クラスと Address クラスの両方のオブジェクトを返すことができます:

>>> stmt=select(user_alias,address_alias).where(user_alias.name=="sandy")>>> forrowinsession.execute(stmt):...     print(f"{row.user} {row.address}")SELECT anon_1.id, anon_1.name, anon_1.fullname, anon_1.id_1, anon_1.email_address
FROM (SELECT user_account.id AS id, user_account.name AS name,
user_account.fullname AS fullname, address.id AS id_1,
address.email_address AS email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE address.email_address IN (?, ?)) AS anon_1
WHERE anon_1.name = ?
[...] ('pat999@aol.com', 'squirrel@squirrelpower.org', 'sandy')
User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='squirrel@squirrelpower.org')

Setting the leftmost FROM clause in a join¶

Select の現在の状態の左辺が結合元と一致しない場合、 Select.join_from() メソッドを使うことができます:

>>> stmt=select(Address).join_from(User,User.addresses).where(User.name=="sandy")>>> print(stmt)SELECT address.id, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE user_account.name = :name_1

Select.join_from() メソッドは、2つまたは3つの引数を、 (<join from>, <onclause>), or (<join from>, <join to>, [<onclause>]) のいずれかの形式で受け取ります。:

>>> stmt=select(Address).join_from(User,Address).where(User.name=="sandy")>>> print(stmt)SELECT address.id, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE user_account.name = :name_1

Select.join() が後で使えるようにSELECTの最初のFROM句を設定するには、 Select.select_from() メソッドも使えます:

>>> stmt=select(Address).select_from(User).join(Address).where(User.name=="sandy")>>> print(stmt)SELECT address.id, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE user_account.name = :name_1

Tip

Select.select_from() メソッドは、実際にはFROM句内のテーブルの順序について最終的な決定権を持っていません。ステートメントが、異なる順序で既存のテーブルを参照する Join 構文も参照する場合、 Join 構文が優先されます。 Select.join() や Select.join_from() のようなメソッドを使用する場合、これらのメソッドは最終的にはそのような Join オブジェクトを作成します。したがって、次のような場合には Select.select_from() の内容が上書きされていることがわかります:

>>> stmt=select(Address).select_from(User).join(Address.user).where(User.name=="sandy")>>> print(stmt)SELECT address.id, address.user_id, address.email_address
FROM address JOIN user_account ON user_account.id = address.user_id
WHERE user_account.name = :name_1

上記では、最初に select_from(User) と指定したにもかかわらず、FROM句が address JOIN user_account であることがわかります。 .join(Address.user) メソッド呼び出しのため、この文は最終的には次のようになります。:

>>> fromsqlalchemy.sqlimportjoin>>>>>> user_table=User.__table__>>> address_table=Address.__table__>>>>>> j=address_table.join(user_table,user_table.c.id==address_table.c.user_id)>>> stmt=(...     select(address_table)...     .select_from(user_table)...     .select_from(j)...     .where(user_table.c.name == "sandy")... )>>> print(stmt)SELECT address.id, address.user_id, address.email_address
FROM address JOIN user_account ON user_account.id = address.user_id
WHERE user_account.name = :name_1

上記の Join 構文は、 Select.select_from() リストに別のエントリとして追加され、前のエントリに優先します。

Relationship WHERE Operators¶

relationship() 構文を Select.join() および Select.join_from() メソッド内で使用することに加えて、 relationship() は、 Select.where() メソッドを使用して、WHERE句で一般的に使用されるSQL式を作成するのにも役立ちます。

EXISTS forms: has() / any()¶

Exists 構文は、 EXISTS subqueries セクションの SQLAlchemy Unified Tutorial で最初に導入されました。このオブジェクトは、スカラー副問い合わせと共にSQL EXISTSキーワードを描画するために使用されます。 relationship() 構文は、関係に関してクエリの一般的なEXISTSスタイルを生成するために使用できるいくつかのヘルパーメソッドを提供します。

>>> stmt=select(User.fullname).where(...     User.addresses.any(Address.email_address == "squirrel@squirrelpower.org")... )>>> session.execute(stmt).all()SELECT user_account.fullname
FROM user_account
WHERE EXISTS (SELECT 1
FROM address
WHERE user_account.id = address.user_id AND address.email_address = ?)
[...] ('squirrel@squirrelpower.org',)
[('Sandy Cheeks',)]

EXISTSは負のルックアップに対してより効率的な傾向があるため、一般的なクエリは、関連するエンティティが存在しないエンティティを見つけることです。これは、関連する Address 行を持たない User エンティティを選択するために、 ~User.addresses.any() のようなフレーズを使用して簡潔に表現されています。

>>> stmt=select(User.fullname).where(~User.addresses.any())>>> session.execute(stmt).all()SELECT user_account.fullname
FROM user_account
WHERE NOT (EXISTS (SELECT 1
FROM address
WHERE user_account.id = address.user_id))
[...] ()
[('Eugene H. Krabs',)]

PropComparator.has() メソッドは PropComparator.any() とほとんど同じように動作しますが、”sandy”に属するすべての Address オブジェクトを見つけたい場合など、多対1の関係に使用される点が異なります。:

>>> stmt=select(Address.email_address).where(Address.user.has(User.name=="sandy"))>>> session.execute(stmt).all()SELECT address.email_address
FROM address
WHERE EXISTS (SELECT 1
FROM user_account
WHERE user_account.id = address.user_id AND user_account.name = ?)
[...] ('sandy',)
[('sandy@sqlalchemy.org',), ('squirrel@squirrelpower.org',)]

Relationship Instance Comparison Operators¶

relationship() -bound属性は、関連するオブジェクトの特定のインスタンスに関して relationship() -bound属性をフィルタすることを目的としたSQL構築実装もいくつか提供しています。これは、与えられた persistent (またはあまり一般的ではありませんが detached )オブジェクトインスタンスから適切な属性値を展開し、ターゲットの relationship() に関してWHERE条件を構築することができます。

多対1のイコール比較 - 特定のオブジェクトインスタンスを多対1の関係と比較して、ターゲットエンティティの外部キーが指定されたオブジェクトの主キー値と一致する行を選択できます:
```
>>> user_obj = session.get(User, 1)
SELECT ...
>>> print(select(Address).where(Address.user == user_obj))
SELECT address.id, address.user_id, address.email_address
FROM address
WHERE :param_1 = address.user_id
```

多対1の不等号比較 - 不等号演算子も使用できます:

>>> print(select(Address).where(Address.user!=user_obj))SELECT address.id, address.user_id, address.email_address
FROM address
WHERE address.user_id != :user_id_1 OR address.user_id IS NULL

オブジェクトは1対多のコレクションに含まれています - これは基本的に1対多の”等しい”比較です。主キーが関連オブジェクトの外部キーの値と等しい行を選択します:

>>> address_obj=session.get(Address,1)SELECT ...
>>> print(select(User).where(User.addresses.contains(address_obj)))SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.id = :param_1

オブジェクトは一対多の観点から特定の親を持ちます - with_parent() 関数は与えられた親から参照される行を返す比較を生成します。これは本質的には、多対一の側で == 演算子を使うのと同じです:
```
>>> from sqlalchemy.orm import with_parent
>>> print(select(Address).where(with_parent(user_obj, User.addresses)))
SELECT address.id, address.user_id, address.email_address
FROM address
WHERE :param_1 = address.user_id
```

SQLAlchemy 2.0 Documentation - 私家版日本語訳ドキュメント

SQLAlchemy 2.0 Documentation - 私家版日本語訳ドキュメント

SQLAlchemy ORM

Writing SELECT statements for ORM Mapped Classes¶

Selecting ORM Entities and Attributes¶

Selecting ORM Entities¶

Selecting Multiple ORM Entities Simultaneously¶

Selecting Individual Attributes¶

Grouping Selected Attributes with Bundles¶

Selecting ORM Aliases¶

Getting ORM Results from Textual Statements¶

Selecting Entities from Subqueries¶

Selecting Entities from UNIONs and other set operations¶

Joins¶

Simple Relationship Joins¶

Chaining Multiple Joins¶

Joins to a Target Entity¶

Joins to a Target with an ON Clause¶

Combining Relationship with Custom ON Criteria¶

Using Relationship to join between aliased targets¶

Joining to Subqueries¶

Joining to Subqueries along Relationship paths¶

Subqueries that Refer to Multiple Entities¶

Setting the leftmost FROM clause in a join¶

Relationship WHERE Operators¶

EXISTS forms: has() / any()¶

Relationship Instance Comparison Operators¶