Merge queryset from two databases in Django Rest

Question

I have two routes in my api looking like that :

http://mywebsite/websites/website_1/annonces/

http://mywebsite/websites/website_2/annonces/

I need to make an ListAPIView merging these 2 routes but each route call its own database.

The two databases are made with the same django Model. I made two databases because it suits my architecture better.

The problem is that I have no column in my databases which indicates the website the records are from. The records are only differentiated by the names of their database.

I want to get all record in a single route but also being able to tell from which database they are from in the json response.

 class Annonce(models.Model):
     annonce_type = models.CharField(max_length=200, blank=True, null=True)
     annonce_id = models.CharField(unique=True, max_length=200, blank=True, null=True)
     url = models.TextField(blank=True, null=True)
     region = models.TextField(blank=True, null=True)

 class AnnoncesList(generics.ListAPIView):
     authentication_classes = ()
     permission_classes = ()
     serializer_class = AnnonceListSerializer
     pagination_class = LargeResultsSetPagination
     filter_backends = (DjangoFilterBackend,)
     filterset_fields = ('advert_type', 'asset_type', 'sales_type', 'price', 'area', 'department', 'department_id', 'city', 'postal_code')

     def get_queryset(self):
         queryset = Annonce.objects.using(self.kwargs["website_name"]).all()
         return queryset

Please, share the code for your models and your views

HuLu ViCa
– HuLu ViCa

2019-05-21 18:35:55 +00:00
Commented May 21, 2019 at 18:35 — HuLu ViCa
– HuLu ViCa, Commented May 21, 2019 at 18:35
@HuLuViCa Ok it's done

SimonR
– SimonR

2019-05-22 07:48:37 +00:00
Commented May 22, 2019 at 7:48 — SimonR
– SimonR, Commented May 22, 2019 at 7:48

Lucas Weyne · Accepted Answer · 2019-06-02 19:44:12Z

1

Make the queryset for each database, then use annotate() to add a column website_name for each record on queryset. Concatenate the querysets into a list (check this) (will hit all items on database), make sure the querysets have already been properly filtered.

from itertools import chain

from rest_framework.generics import ListAPIView
from rest_framework.response import Response

from django.db.models import Value, CharField


class AnnonceMergedList(ListAPIView):
    serializer_class = AnnonceMergedListSerializer
    queryset = Annonce.objects.all()

    def list(self, request, **kwargs):
        # Make the querysets for each database
        q1 = self.get_queryset().using('website_1').annotate(website_name=Value('website_1', CharField()))
        q2 = self.get_queryset().using('website_2').annotate(website_name=Value('website_2', CharField()))

        # Filtering the querysets
        q1 = self.filter_queryset(q1)
        q2 = self.filter_queryset(q2)

        # Merge (hit items on database)
        data = list(chain(q1, q2))

        serializer = self.get_serializer(data, many=True)
        return Response(serializer.data)

The serializer for this view must receive the website_name to display the website the records are from

class AnnonceMergedListSerializer(serializers.ModelSerializer):
    website_name = serializers.CharField(read_only=True) # Field from annotation

    class Meta:
        model = Annonce
        fields = ('website_name', ...)

edited Jun 2, 2019 at 19:44

answered May 22, 2019 at 16:34

Lucas Weyne

1,1521 gold badge7 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SimonR Over a year ago

looks good but I get that error : Response' object has no attribute 'model

Lucas Weyne Over a year ago

Do you override get_queryset method? I guess this error happens when DRF try to retrieve the model through get_queryset return, but the method returned a Response object instead a queryset. In this sample I not override get_queryset method, only the queryset property

SimonR Over a year ago

Yes I did override python get_queryset. I not finding a way to merge 2 queryset with two different _db and still return a queryset and not a Response object.

Collectives™ on Stack Overflow

Merge queryset from two databases in Django Rest

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related