django数据表不设置外键进行关联查询

django orm数据表不设置外键进行关联查询

最近在django项目中有遇到关联查询但是数据表中字段未设置外键关联的情况，记录一下解决办法。

数据表

from django.db import models


class Book(models.Model):
    """书籍表"""
    name = models.CharField('名称', max_length=50)
    price = models.IntegerField('价格', default=50)
    publisher_id = models.IntegerField('出版社ID')

    class Meta:
        db_table = 'books_book'


class Publisher(models.Model):
    """出版社表"""
    name = models.CharField('出版社名称', max_length=50)
    addr = models.CharField('出版社地址', max_length=50)

    class Meta:
        db_table = 'books_publisher'
        

问题：查询book所有字段和publisher.name? (等同如下sql效果)

select books_book.*, books_publisher.name from books_book
left join books_publisher
on books_book.publisher_id = books_publisher.id;

方法一： 修改books_book表 models字段定义，改为逻辑关联

Tips: django中的ForeignKey与数据库中FOREIGN KEY约束并不一样，ForeignKey是一种逻辑上的关联关系，是否使用数据库中的外键约束通过db_contraint参数设置。

class Book(models.Model):
    """书籍表"""
    name = models.CharField('名称', max_length=50)
    price = models.IntegerField('价格', default=50)
    publisher = models.ForeignKey('Publisher', on_delete=models.CASCADE, db_contraint=False)

	class Meta:
	        db_table = 'books_book'


class Publisher(models.Model):
    """出版社表"""
    name = models.CharField('出版社名称', max_length=50)
    addr = models.CharField('出版社地址', max_length=50)

	class Meta:
	        db_table = 'books_publisher'

查询语句：

queryset = Book.objects.values(‘id’, ‘name’, ‘price’, ‘publisher__name’)

方法二：通过extra api函数实现

queryset = Book.objects.extra(select={‘publisher_name’: ‘SELECT books_publisher.name FROM books_publisher WHERE books_publisher.id = books_book.publisher_id’})

测试结果：
可以发现查询出的queryset中多了一个publisher_name属性，然后在序列化阶段将这个字段加上即可。

>>> from books.models import Book, Publisher
>>> queryset = Book.objects.extra(select={'publisher_name': 'SELECT books_publisher.name FROM books_publisher WHERE books_publisher.id = books_book.publisher_id'})
>>> for item in queryset:
...     print(item.__dict__)
...
{'_state': <django.db.models.base.ModelState object at 0x000002993198F208>, 'id': 1, 'name': '书本1', 'price': 20, 'publisher_id': 1, 'publisher_name': '工业出版社'}

方法三：执行原生sql实现

方法三与方法二比较类似

queryset = Book.objects.raw( ‘select books_book.*, books_publisher.name as publisher_name from books_book left join books_publisher on books_book.publisher_id = books_publisher.id;’)

测试结果

>>> queryset = Book.objects.raw( 'select books_book.*, books_publisher.name as publisher_name from books_book left join books_publisher on books_book.publisher_id = books_publisher.id;')
>>> for item in queryset:
...     print(item.__dict__)
...
{'_state': <django.db.models.base.ModelState object at 0x0000029931119748>, 'id': 1, 'name': '书本1', 'price': 20, 'publisher_id': 1, 'publisher_name': '工业出版社'}

方法四：在序列化阶段根据相关联的id字段再执行一次查询

# ProjectVersion model 序列化器

class BookSerializer(serializers.ModelSerializer):
    publisher_name = serializers.SerializerMethodField()

    def get_publisher_name(self, obj):
        """
        :param obj: Book实例
        """
        publisher = Publisher.objects.filter(id=obj.publisher_id).first()
        if publisher:
            return publisher.name
        else:
            return ''

通过查看SerializerMethodField源码可以发现绑定的序列化方法为：get_{field_name}

class SerializerMethodField(Field):
    """
    A read-only field that get its representation from calling a method on the
    parent serializer class. The method called will be of the form
    "get_{field_name}", and should take a single argument, which is the
    object being serialized.

    For example:

    class ExampleSerializer(self):
        extra_info = SerializerMethodField()

        def get_extra_info(self, obj):
            return ...  # Calculate some data to return.
    """
    def __init__(self, method_name=None, **kwargs):
        self.method_name = method_name
        kwargs['source'] = '*'
        kwargs['read_only'] = True
        super().__init__(**kwargs)

    def bind(self, field_name, parent):
        # In order to enforce a consistent style, we error if a redundant
        # 'method_name' argument has been used. For example:
        # my_field = serializer.SerializerMethodField(method_name='get_my_field')
        default_method_name = 'get_{field_name}'.format(field_name=field_name)

        # The method name should default to `get_{field_name}`.
        if self.method_name is None:
            self.method_name = default_method_name

        super().bind(field_name, parent)

    def to_representation(self, value):
        method = getattr(self.parent, self.method_name)
        return method(value)

参考链接：
https://www.h5w3.com/10765.html
https://www.cnblogs.com/shouke/p/13423151.html