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',)
>>> for user_obj in result.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)
>>> for row in session.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.org
sandy sandy@sqlalchemy.org
sandy squirrel@squirrelpower.org
patrick pat999@aol.com
squidward stentcl@sqlalchemy.org
行内のこれらのエンティティに異なる名前を割り当てたい場合は、 aliased.name
パラメータを使用して aliased()
構文を使用し、明示的な名前でエイリアスを付けます:
>>> from sqlalchemy.orm import aliased
>>> 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のみの構文で、列式の集合を結果行にグループ化することができます:
>>> from sqlalchemy.orm import Bundle
>>> stmt = select(
... Bundle("user", User.name, User.fullname),
... Bundle("email", Address.email_address),
... ).join_from(User, Address)
>>> for row in session.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.org
sandy Sandy Cheeks sandy@sqlalchemy.org
sandy Sandy Cheeks squirrel@squirrelpower.org
patrick Patrick Star pat999@aol.com
squidward Squidward Tentacles stentcl@sqlalchemy.org
Bundle
は軽量ビューやカスタム列グループの作成に役立つ可能性があります。 Bundle
をサブクラス化して、別のデータ構造を返すこともできます。例として Bundle.create_row_processor()
を参照してください。
Selecting ORM Aliases¶
Using Aliases のチュートリアルで説明したように、ORMエンティティのSQLエイリアスを作成するには、マップされたクラスに対して aliased()
構文を使用します:
>>> from sqlalchemy.orm import aliased
>>> 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
パラメータも渡すことができます:
>>> from sqlalchemy.orm import aliased
>>> 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)
>>> for user_obj in session.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)
>>> for user_obj in session.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')
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()
が使用された後に追加の基準を追加することはできません:
>>> from sqlalchemy import union_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)
>>> for user_obj in session.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')
See also
Selecting ORM Entities from Unions - in the SQLAlchemy Unified Tutorial
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))
>>> for row in session.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")
>>> for row in session.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)
メソッド呼び出しのため、この文は最終的には次のようになります。:
>>> from sqlalchemy.sql import join
>>>
>>> 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