Is it possible to merge multiple django querysets from multiple databases into one queryset?

Question

I want to create an api with django-rest to get events. For instance: I have a table events in multiple databases. A get request to /events should read all the events from all configured databases and aggregate all results into one queryset. Then I want to serialize the queryset and return the json to the user.

in settings.py I created a list of databases who have the events table. I can query events from DATABASE_1 and DATABASE_2, with a single query. But when I try to aggregate the results in a loop, then I only get the events from DATABASE_2. So the aggregation isn't working.

How do I aggregate these querysets into one? Or is there even a better solution for querying multiple databases?

settings.py

DATABASES = {
    'default': env.db(),
    'DATABASE_1': env.db('DATABASE_1'),
    'DATABASE_2': env.db('DATABASE_2'),
}

EVENT_DATABASES = [
    'DATABASE_1',
    'DATABASE_2',
]

urls.py

urlpatterns = [
    path('events/', EventView.as_view())
]

event_view.py

class EventView(APIView):
    serializer_class = EventSerializer

    def get_queryset(self):
        last_24_hours = datetime.datetime.today() - datetime.timedelta(1)
        events = Event.objects.none()
        for database in settings.EVENT_DATABASES:
            events |= Event.objects.using(database).filter(updated_at__gte=last_24_hours).exclude(code='OK')
        return events

    def get(self, request, format=None):
        queryset = self.get_queryset()
        serializer = EventSerializer(queryset, many=True)
        return Response(serializer.data)

Steve Bywater · Accepted Answer · 2019-03-26 15:19:03Z

1

You will want to use itertools.chain

answered Mar 26, 2019 at 15:19

Steve Bywater

111 bronze badge

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

3 Comments

Yves Wehrli Over a year ago

Do you have an example?

Christian Bouvier Over a year ago

You can chain querysets by doing: chain(queryset1, queryset2, ... , querysetN)

Yves Wehrli Over a year ago

@ChristianBouvier Yes I can chain them that way. The problem is that the serializer wants a queryset object, but after chaining I have a chain object. I get the error 'itertools.chain' object has no attribute 'model'. So is there a way to convert that back to a single queryset? Otherwise chaining isn't the right solution....

Christian Bouvier · Accepted Answer · 2019-03-27 12:17:32Z

0

If you are trying to merge querysets of the same model you can use the | operator.

i.e. merged_queryset = queryset_1 | queryset_2

Otherwise, you best friend is itertools.chain (as stated in the previous answer). The result of a chain is a chain object you can turn into a list by doing:

merged_queryset = list(chain(queryset_1, queryset_2))

Hope this help!

answered Mar 27, 2019 at 12:17

Christian Bouvier

4633 silver badges11 bronze badges

Collectives™ on Stack Overflow

Is it possible to merge multiple django querysets from multiple databases into one queryset?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related