django 多表查询

models.py内容：

1、商品表

class Product(models.Model):
    product_id = models.AutoField(primary_key=True)
    product_name = models.CharField(max_length=255, blank=True, null=True)
    product_code = models.CharField(max_length=255, blank=True, null=True,unique=True)

    class Meta:
        managed = False
        db_table = 'product'

2、仓库表

class Warehouse(models.Model):
    warehouse_id = models.AutoField(primary_key=True)
    warehouse_name = models.CharField(max_length=20, blank=True, null=True)
    warehouse_phone = models.CharField(max_length=50, blank=True, null=True)
    warehouse_address = models.CharField(max_length=50, blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'warehouse'

3、 库存表

from django.db import models
from product.models import Product
from warehouse.models import Warehouse

class Stock(models.Model):
    stock_id = models.AutoField(primary_key=True)
    # 关联商品表中的product_code字段，具有唯一性
    product_code = models.ForeignKey(to=Product,db_column='product_code',to_field='product_code',on_delete=models.CASCADE)
    # 关联仓库表中的自增主键warehouse_id
    warehouse = models.ForeignKey(to=Warehouse,on_delete=models.CASCADE, blank=True, null=True)
    product_num = models.IntegerField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'stock'

其中： db_column指的是当前表的外键，to_field指的是关联表中被关联的字段，to_field所指的字段必须是唯一的（有唯一性约束）。

1、多表查询全部数据

StockList = Stock.objects.values('stock_id','product_code','product_code__product_name','warehouse__warehouse_name','product_num')  # 获取所有数据

sql：

select stock_id,stock.product_code,product_name,warehouse_name,product_num from stock 
left join product on stock.product_code = product.product_code 
left join warehouse on stock.warehouse_id = warehouse.warehouse_id

2、多表查询仓库为1号仓库的数据

StockList = Stock.objects.filter(warehouse_id =1).values('stock_id','product_code','product_code__product_name','warehouse__warehouse_name','product_num')  # 获取所有数据

sql:

select stock_id,stock.product_code,product_name,warehouse_name,product_num from stock 
left join product on stock.product_code = product.product_code 
left join warehouse on stock.warehouse_id = warehouse.warehouse_id
where stock.warehouse_id = 1