SQLAlchemy 2 In Practice - Chapter 6: A Page Analytics Solution
SQLAlchemy 第六章：預設 inner join 讓 493 筆頁面瀏覽靜默消失，outer join 與 UNION 是跨資料庫的修正方案
- BlogView 使用自增 ID 取代複合主鍵，讓用戶在同一 session 重複瀏覽同一文章的記錄可正確保存
- 2022/11 按產品統計瀏覽數：ZX Spectrum 1096 次居首，但 inner join 導致 493 筆無關聯文章瀏覽被吃掉
- Full outer join 在 MySQL 不支援，需用兩次 left outer join 加 union() 組合模擬完整結果
一個看似正確的 SQLAlchemy 分組查詢，靜悄悄地漏掉了 493 筆頁面瀏覽數——原因只是預設使用 inner join 而非 outer join。這是 Miguel Grinberg《SQLAlchemy 2 in Practice》第六章的核心示範：以虛構電商公司 RetroFun 的部落格流量分析系統為例，完整走完資料模型設計、大量資料匯入到實際分析查詢的全流程，其中 inner/outer join 的選擇是整章最有「陷阱感」的知識點。
RetroFun 部落格的五張資料表設計架構
本章分三個階段擴充資料庫。第一階段建立 BlogArticle（文章）和 BlogAuthor（作者）兩張表，文章可選擇性關聯至某個 Product（產品）——這個「可選（nullable）外鍵」設計埋下了後來查詢陷阱的伏筆。第二階段引入 BlogUser（訪客）和 BlogSession（造訪會話），再以 BlogView 記錄每次頁面瀏覽，形成「訪客→會話→瀏覽記錄」的三層追蹤鏈。第三階段則加入多語言支援與翻譯關聯。
BlogUser 和 BlogSession 都使用 UUID（通用唯一識別碼，一種不遞增的隨機 ID）作為主鍵，而非常見的自增整數。原因是這些 ID 可能存在 cookie 中對瀏覽器可見：若用自增整數，攻擊者可輕易推算資料庫的使用者總量；UUID 則無此洩漏風險。
BlogView 為何用自增 ID 取代複合主鍵
多對多關聯表通常以兩個外鍵組成複合主鍵，天然防止重複記錄。但 BlogView 特意打破這個慣例，改用普通自增整數作為主鍵。理由很直接：同一個用戶在同一個 session 內可能重複瀏覽同一篇文章（例如重新整理頁面），若用複合主鍵，這些重複的真實瀏覽行為就會被資料庫拒絕寫入，導致統計失真。
資料匯入方面，文章資料集相對小，用標準的 session.begin() 一次提交即可。但瀏覽記錄的 views.csv 約 19MB、138,000 筆，若將所有資料積累在單一 session 再一次性提交，記憶體壓力極大且容易超時。原文的解法是每 100 筆做一次 session.commit()，並搭配計數器印出進度，讓長時間的匯入過程可追蹤。
Inner Join 讓 493 筆無產品文章的瀏覽數憑空消失
匯入完 138,000 筆瀏覽資料後，就可以開始跑分析查詢。2022 年 11 月的總頁面瀏覽數為 4034 次，按文章排名最高的是「Boy itself fish traditional」（57 次）。但當查詢改為「按產品分組統計瀏覽數」時，結果的加總只有 3541，比總數少了 493。
消失的原因正是 inner join（內部聯結）的特性：SQLAlchemy 的 join() 預設使用 inner join，只保留兩邊都能配對成功的記錄。那些 product_id 為 None 的文章，無法在 Product 表找到對應項目，整批從結果中被丟棄，連帶的 493 筆瀏覽也一起消失。
使用 left outer join 後取得的完整數據，None 代表無產品關聯的文章所產生的 493 筆瀏覽
Outer Join 的三種類型與跨資料庫相容方案
要找回那 493 筆，需要改用 outer join（外部聯結）。Outer join 分三種：full outer join（保留兩側未配對的記錄）、left outer join（保留左側未配對）、right outer join（保留右側未配對）。SQLAlchemy 以 full=True 啟用 full outer join，以 isouter=True 啟用 left outer join。
但資料庫支援不一致是現實問題：MySQL 不支援 full outer join；SQLite 在 3.39.0（2022 年）之前也不支援。原文提供了一個跨資料庫的相容方案：用兩個 left outer join 查詢（一個取有瀏覽的產品，另一個取零瀏覽的產品），再透過 SQLAlchemy 的 union() 函數合併結果。這樣不依賴 full outer join，在 MySQL 和舊版 SQLite 上同樣可行。此外，一旦 join 可能返回 None 的行，where() 子句的時間範圍過濾也必須加上 or_(BlogView.timestamp == None, ...) 的豁免條件，否則 None 記錄會被 where 直接剔除，外連結的效果等於白做。
自我參照關聯追蹤多語言翻譯來源
第三階段加入多語言支援，難點在於追蹤「某篇文章是另一篇文章的翻譯」這層關聯——關聯的兩端都是 BlogArticle，屬於自我參照關聯（self-referential relationship），在 SQLAlchemy 中需要在 relationship() 加上 remote_side=id 參數，明確告訴 ORM 哪一側是「一」（原文）、哪一側是「多」（翻譯版本）。translation_of_id 外鍵指向同一張表的主鍵，而兩個反向屬性（translation_of 和 translations）都定義在同一個 model class 中。這個設計讓之後可以直接從任何一篇文章查到它的所有語言版本，或反向查到原始出處文章，為多語言瀏覽數分析打好基礎。
Inner join 是 SQLAlchemy 的預設行為，凡遇到 nullable 外鍵的關聯查詢，就得主動評估是否需要改用 outer join，否則統計數字會靜默偏低。