How to write a get response for two different models data in one response sorted by date?-CodePudding

I have two models:

class A(models.Model):
    ...
    date=models.DateTimeField(auto_add_now=True)
    ...
class B(models.Model):
    ...
    date=models.DateTimeField(auto_add_now=True)
    ...

Both have date field in them. Now I want to write a get response so that I can get both of these model's data from the database sorted by date on the frontend. I have written model serializers with all fields for them.

Now in the get response, I have written something like this:

A = A.objects.all()
B = B.objects.all()

aSerializer = A_serializer(A, many=True)
bSerializer = B_serializer(B, many=True)

all = aSerializer.data   bSerializer.data
all = sorted(all, key=lambda item: item['date'],reverse=True)

return Response(all)

I think I will not be able to do pagination and other stuff with this. Please let me know if there is any better approach to do this?

CodePudding user response：

you have to use one to one relationship field between two models and then define serializer linke below

enter code here

class BasicSerializer(serializers.ModelSerializer): class Meta: model = MyModel fields = 'all'

class AdvandedSerializer(BasicSerializer): additional_field = serializers.SerializerMethodField()

def get_additional_field(self, obj):
    return('not important')

class Meta(BasicSerializer.Meta):
    fields = BasicSerializer.Meta.fields   ('additional_field',)`

CodePudding user response：

Try .union.

NOTE if A and B do not share same columns, you need to explicitly pick out shared fields.

# Fields to be serialized
shared_fields = ['date', ...]

AB = A.objects.all().values(*shared_fields)\
    .union(B.objects.all().values(*shared_fields))\
    .order_by('date')

# You probably need a new serialize
ab = UnionSerializer(AB, many=True)

return Response(ab.data)

--- Edited: SQL View Function ---

Create a view function to union a and b

create view myapp_ab_view as
    select 
        date, id as a_id, null as b_id
        from myapp_a
    union select
        date, null as a_id, id as b_id
        from myapp_b;

And define corresponding django model

class AB(models.Model):
    date = models.DateField()
    a = models.ForeignKey(A, on_delete=models.DO_NOTHING, null=True)
    b = models.ForeignKey(B, on_delete=models.DO_NOTHING, null=True)
    class Meta:
        db_table = 'myapp_ab_view'
        managed = False

And in your view function

ab = AB.objects.order_by('date')

--- Edit 2: Aliasing ---

a = A.objects.all().values(
    'date',
    col1=F('field_m'),
    col2=F('field_n')
)
b = B.objects.all().values(
    'date',
    col1=F('field_x'),
    col2=F('field_y')
)

# This requires field_m and field_x of similar field type,
# such as SlugField and  CharField. So do field_n and field y. 
ab = a.union(b).order_by('date')