SQLAlchemy ORM - Làm việc với Joins

Bây giờ chúng ta có hai bảng, chúng ta sẽ xem cách tạo truy vấn trên cả hai bảng cùng một lúc. Để tạo một liên kết ngầm đơn giản giữa Khách hàng và Hóa đơn, chúng ta có thể sử dụng Query.filter () để cân bằng các cột liên quan của chúng với nhau. Dưới đây, chúng tôi tải các thực thể Khách hàng và Hóa đơn cùng một lúc bằng phương pháp này -

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()

for c, i in session.query(Customer, Invoice).filter(Customer.id == Invoice.custid).all():
   print ("ID: {} Name: {} Invoice No: {} Amount: {}".format(c.id,c.name, i.invno, i.amount))

Biểu thức SQL do SQLAlchemy phát ra như sau:

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email, invoices.id 
AS invoices_id, invoices.custid 
AS invoices_custid, invoices.invno 
AS invoices_invno, invoices.amount 
AS invoices_amount
FROM customers, invoices
WHERE customers.id = invoices.custid

Và kết quả của các dòng mã trên như sau:

ID: 2 Name: Gopal Krishna Invoice No: 10 Amount: 15000
ID: 2 Name: Gopal Krishna Invoice No: 14 Amount: 3850
ID: 3 Name: Govind Pant Invoice No: 3 Amount: 10000
ID: 3 Name: Govind Pant Invoice No: 4 Amount: 5000
ID: 4 Name: Govind Kala Invoice No: 7 Amount: 12000
ID: 4 Name: Govind Kala Invoice No: 8 Amount: 8500
ID: 5 Name: Abdul Rahman Invoice No: 9 Amount: 15000
ID: 5 Name: Abdul Rahman Invoice No: 11 Amount: 6000

Cú pháp SQL JOIN thực tế có thể dễ dàng đạt được bằng cách sử dụng phương thức Query.join () như sau:

session.query(Customer).join(Invoice).filter(Invoice.amount == 8500).all()

Biểu thức SQL cho phép nối sẽ được hiển thị trên bảng điều khiển -

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers JOIN invoices ON customers.id = invoices.custid
WHERE invoices.amount = ?

Chúng ta có thể lặp lại kết quả bằng vòng lặp for -

result = session.query(Customer).join(Invoice).filter(Invoice.amount == 8500)
for row in result:
   for inv in row.invoices:
      print (row.id, row.name, inv.invno, inv.amount)

Với 8500 là tham số liên kết, kết quả sau được hiển thị:

4 Govind Kala 8 8500

Query.join () biết cách nối giữa các bảng này vì chỉ có một khóa ngoại giữa chúng. Nếu không có khóa ngoại hoặc nhiều khóa ngoại hơn, Query.join () sẽ hoạt động tốt hơn khi một trong các dạng sau được sử dụng:

query.join (Hóa đơn, id == Address.custid)	điều kiện rõ ràng
query.join (Customer.invoices)	xác định mối quan hệ từ trái sang phải
query.join (Hóa đơn, Khách hàng.invoices)	giống nhau, với mục tiêu rõ ràng
query.join ('hóa đơn')	tương tự, sử dụng một chuỗi

Tương tự như vậy, hàm ngoài () có sẵn để đạt được kết nối bên ngoài bên trái.

query.outerjoin(Customer.invoices)

Phương thức subquery () tạo ra một biểu thức SQL đại diện cho câu lệnh SELECT được nhúng trong một bí danh.

from sqlalchemy.sql import func

stmt = session.query(
   Invoice.custid, func.count('*').label('invoice_count')
).group_by(Invoice.custid).subquery()

Đối tượng stmt sẽ chứa một câu lệnh SQL như sau:

SELECT invoices.custid, count(:count_1) AS invoice_count FROM invoices GROUP BY invoices.custid

Khi chúng ta có câu lệnh của mình, nó hoạt động giống như một cấu trúc Bảng. Các cột trên câu lệnh có thể truy cập được thông qua một thuộc tính được gọi là c như được hiển thị trong đoạn mã dưới đây:

for u, count in session.query(Customer, stmt.c.invoice_count).outerjoin(stmt, Customer.id == stmt.c.custid).order_by(Customer.id):
   print(u.name, count)

Vòng lặp for ở trên hiển thị số lượng hóa đơn theo tên khôn ngoan như sau:

Arjun Pandit None
Gopal Krishna 2
Govind Pant 2
Govind Kala 2
Abdul Rahman 2