# SQLAlchemy 2.0(11)Working with ORM Related Objects(02)
###### tags: `python` `sqlalchemy`
[官方文件](https://docs.sqlalchemy.org/en/20/tutorial/orm_related_objects.html)
## Using Relationships in Queries
現在我們已經知道怎麼關聯資料表了，就來做後續比較複雜的操作
### Using Relationships to Join
用ORM的話不用像用CORE那樣的麻煩，因為我們有設置好關聯了：
```python=
>>> print(select(Address.email_address).select_from(User).join(User.addresses))
```
```python=
SELECT address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
```
如果沒有特別指定`relationship`，那`select.join`或是`select.join_from`就不會用ORM的`relationship`來推論它的`ON`，基本上只要你有設定好ForeignKey，那還是可以成功的關聯兩個資料表：
```python=
>>> print(select(Address.email_address).join_from(User, Address))
```
```python=
SELECT address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
```
### Loader Strategies
在[SQLAlchemy 2.0(10)Working with ORM Related Objects(01)](https://hackmd.io/@shaoeChen/BkLu0Sbth)的範例中我們看到lazy loading，預設情況下就是說，`u1`會在我們實際去訪問它的屬性`addresses`的時候再做資料的下載，這樣我們去查詢的時候才看的到東西。
基本上這是ORM一種很重要的模式，也是很受爭議的一個模式。畢竟如果你的記憶體中幾十個物件都是這種情況的話，不小心同時載入也是很耗資源的，然後載入的時候是隱式的查詢，有時候很難發現，而且有時候會造成一些問題。
不過請你相信SQLAlchemy的ORM對於這些問題的掌握一定是好棒棒的。
最重要的當然就是說，如果真的使用ORM的lazy loading，那首先就要先來測一下，看看是不是真的會有很多的冗餘的查詢，如果真的有很多不好的載入的話，那機確考慮使用loader strategies。
loader strategies就是說可以用[Select.options()](https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.Select.options)來處理一些關聯：
```python=
for user_obj in session.execute(
select(User).options(selectinload(User.addresses))
).scalars():
user_obj.addresses # access addresses collection already loaded
```
這樣，`addresses`的部份就會同步的下載下來。當然，我們也可以利用參數設置來決定這部份：
```python=
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import relationship
class User(Base):
__tablename__ = "user_account"
addresses: Mapped[List["Address"]] = relationship(
back_populates="user", lazy="selectin"
)
```
下面我們就來針對各種loader strategy說明
### Selectin Load
[selectinload()](https://docs.sqlalchemy.org/en/20/orm/queryguide/relationships.html#sqlalchemy.orm.selectinload)算是SQLAlchemy最有幫助一種加載器了，一次執行通通滿足，如稍早的範例那般，取得`User`的同時也把相關的`Address`帶入：
```python=
>>> from sqlalchemy.orm import selectinload
>>> stmt = select(User).options(selectinload(User.addresses)).order_by(User.id)
>>> for row in session.execute(stmt):
... print(
... f"{row.User.name} ({', '.join(a.email_address for a in row.User.addresses)})"
... )
```
```
spongebob (spongebob@sqlalchemy.org)
sandy (sandy@sqlalchemy.org, sandy@squirrelpower.org)
patrick ()
squidward ()
ehkrabs ()
pkrabs (pearl.krabs@gmail.com, pearl@aol.com)
```
```python=
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.id
[...] ()
SELECT address.user_id AS address_user_id, address.id AS address_id,
address.email_address AS address_email_address
FROM address
WHERE address.user_id IN (?, ?, ?, ?, ?, ?)
[...] (1, 2, 3, 4, 5, 6)
```
範例明顯可以發現，我們只做一次的`session.execute`，但也同時的去取得`Address`的相關資訊。
### Joined Load
[joinload()](https://docs.sqlalchemy.org/en/20/orm/queryguide/relationships.html#sqlalchemy.orm.joinedload)是SQLAlchemy中最古老的一種eager loader。它主要是利用`join`來關聯其它資料表，主要是看你的`options`怎麼設置，重點是，它是最適合執行多對一的加載方法：
```python=
>>> from sqlalchemy.orm import joinedload
>>> stmt = (
... select(Address)
... .options(joinedload(Address.user, innerjoin=True))
... .order_by(Address.id)
... )
>>> for row in session.execute(stmt):
... print(f"{row.Address.email_address} {row.Address.user.name}")
```
```
spongebob@sqlalchemy.org spongebob
sandy@sqlalchemy.org sandy
sandy@squirrelpower.org sandy
pearl.krabs@gmail.com pkrabs
pearl@aol.com pkrabs
```
```python=
SELECT address.id, address.email_address, address.user_id, user_account_1.id AS id_1,
user_account_1.name, user_account_1.fullname
FROM address
JOIN user_account AS user_account_1 ON user_account_1.id = address.user_id
ORDER BY address.id
[...] ()
```
範例可以看的到，查詢的主要目標是`Address `，然後利用`innerjoin`去關聯所屬的`user`。
也是可以拿`joinedload()`來做一對多的查詢，看情況吧，一對多就盡量拿`selectinload()`來做了。
### Explicit Join + Eager load
意思大概就是說，你可以在利用`join`關聯查詢的時候同步把資料也利用`contains_eager`給載下來，這跟`joinload`有點像，不同的是，這方式的`join`是我們顯示的指定：
```python=
>>> from sqlalchemy.orm import contains_eager
>>> stmt = (
... select(Address)
... .join(Address.user)
... .where(User.name == "pkrabs")
... .options(contains_eager(Address.user))
... .order_by(Address.id)
... )
>>> for row in session.execute(stmt):
... print(f"{row.Address.email_address} {row.Address.user.name}")
```
```
pearl.krabs@gmail.com pkrabs
pearl@aol.com pkrabs
```
```python=
SELECT user_account.id, user_account.name, user_account.fullname,
address.id AS id_1, address.email_address, address.user_id
FROM address JOIN user_account ON user_account.id = address.user_id
WHERE user_account.name = ? ORDER BY address.id
[...] ('pkrabs',)
```
上面我們做的就是說，指定`Address.user`的同步取得，如果改用`joinedload()`的話會發現多了一次不必要的查詢：
```python=
>>> stmt = (
... select(Address)
... .join(Address.user)
... .where(User.name == "pkrabs")
... .options(joinedload(Address.user))
... .order_by(Address.id)
... )
>>> print(stmt) # SELECT has a JOIN and LEFT OUTER JOIN unnecessarily
```
```python=
SELECT address.id, address.email_address, address.user_id,
user_account_1.id AS id_1, user_account_1.name, user_account_1.fullname
FROM address JOIN user_account ON user_account.id = address.user_id
LEFT OUTER JOIN user_account AS user_account_1 ON user_account_1.id = address.user_id
WHERE user_account.name = :name_1 ORDER BY address.id
```
### Raiseload
[raiseload()](https://docs.sqlalchemy.org/en/20/orm/queryguide/relationships.html#sqlalchemy.orm.raiseload)可以完全的擋掉那種因為lazy load所造成的[N+1](https://docs.sqlalchemy.org/en/20/glossary.html#term-N-plus-one)的問題。你可以在`relationship()`中設置`relationship.lazy`的時候設置`lazy="raise_on_sql"`：
```python=
>>> from sqlalchemy.orm import Mapped
>>> from sqlalchemy.orm import relationship
>>> class User(Base):
... __tablename__ = "user_account"
... id: Mapped[int] = mapped_column(primary_key=True)
... addresses: Mapped[List["Address"]] = relationship(
... back_populates="user", lazy="raise_on_sql"
... )
>>> class Address(Base):
... __tablename__ = "address"
... id: Mapped[int] = mapped_column(primary_key=True)
... user_id: Mapped[int] = mapped_column(ForeignKey("user_account.id"))
... user: Mapped["User"] = relationship(back_populates="addresses", lazy="raise_on_sql")
```
這樣設置之後，當然我們就會完全的擋掉lazy loading，下面範例可以看的到`user.addresses`是沒有東西可以撈的，還會拋出異常：
```python=
>>> u1 = session.execute(select(User)).scalars().first()
```
```
>>> u1.addresses
Traceback (most recent call last):
...
sqlalchemy.exc.InvalidRequestError: 'User.addresses' is not available due to lazy='raise_on_sql'
```
```python=
SELECT user_account.id FROM user_account
[...] ()
```
這說明的就是你要自己手動來一發：
```python=
>>> u1 = (
... session.execute(select(User).options(selectinload(User.addresses)))
... .scalars()
... .first()
... )
```
```python=
SELECT user_account.id
FROM user_account
[...] ()
SELECT address.user_id AS address_user_id, address.id AS address_id
FROM address
WHERE address.user_id IN (?, ?, ?, ?, ?, ?)
[...] (1, 2, 3, 4, 5, 6)
```
## 延伸閱讀
更多的資訊你可以參考[ORM Querying Guide](https://docs.sqlalchemy.org/en/20/orm/queryguide/index.html)
Sign in with Wallet
Connect another wallet