How to combine multiple querysets in django

Question

Suppose there is an event model and for each event there is one client and one consultant. Also, one consultant can have multiple events. Each event has number of different documents. I am trying to display list of events when a consultant logs in and in that list od events it should display their respective documents.

Models.py:

class Client_Profile(models.Model):
    user_id = models.OneToOneField(User, primary_key=True, on_delete=models.CASCADE)  # Field name made lowercase.
    first_name = models.CharField(db_column='First_name', max_length=50)  # Field name made lowercase.
    last_name = models.CharField(db_column='Last_name', max_length=50)  # Field name made lowercase.
    phone_number = models.PositiveIntegerField(db_column='Phone_number', max_length=10)  # Field name made lowercase.
    # role_id = models.ForeignKey(Role, on_delete=models.CASCADE)

    def __str__(self):
        return self.first_name


class Consultant_Profile(models.Model):
    user_id = models.OneToOneField(User, primary_key=True, on_delete=models.CASCADE)  # Field name made lowercase.
    first_name = models.CharField(db_column='First_name', max_length=50)  # Field name made lowercase.
    last_name = models.CharField(db_column='Last_name', max_length=50)  # Field name made lowercase.
    phone_number = models.PositiveIntegerField(db_column='Phone_number', max_length=10)  # Field name made lowercase.
    # role_id = models.ForeignKey(Role, on_delete=models.CASCADE)

    def __str__(self):
        return self.first_name


class Event(models.Model):
    event_id = models.AutoField(db_column='event_id', primary_key=True)
    client_id = models.ForeignKey(Client_Profile, db_column='Client_ID', on_delete=models.CASCADE)  # Field name made lowercase.
    consultant_id = models.ForeignKey(Consultant_Profile, db_column='Consultant_ID', on_delete=models.CASCADE)  # Field name made lowercase.

    def __str__(self):
        return str(self.event_id)


class Document(models.Model):
    document_id = models.AutoField(db_column='document_id', primary_key=True)
    document_name = models.CharField(db_column='document_name', max_length=50, null=True, blank=True)  # Field name made lowercase.
    path = models.FileField(null=True, upload_to='files/')
    date_uploaded = models.DateTimeField(default=timezone.now, null=True, blank=True)
    event_id = models.ForeignKey(Event, db_column='Client_ID', on_delete=models.CASCADE)  # Field name made lowercase.

    def __str__(self):
        return self.document_name + ": " + str(self.path)  # this specifies how should instance of this class should be printed.

views.py

@login_required
def consultant_home(request):
    consultant_pr = Consultant_Profile.objects.get(user_id=request.user)
    event_id = Event.objects.filter(consultant_id=consultant_pr.pk)
    for id in event_id:
        doc = Document.objects.filter(event_id=id)
    context = {'id': event_id, 'doc': doc, 'consultant_pr': consultant_pr}
    return render(request, 'Consultant/consultant_documents.html', context)

document.html

                    {% for eve in id %}
                    <p>Event id: {{ eve.event_id }}</p>
                        {% for dox in doc %}
                            <p>document name: {{ dox.document_name }}</p>
                            <p>path: <a href="/media/{{dox.path}}">{{ dox.path}} </a> </p>
                        {% endfor%}
                    {% endfor %}

it's discussed [here](https://stackoverflow.com/questions/431628/how-can-i-combine-two-or-more-querysets-in-a-django-view) with details. — Ali Aref, May 16 '21 at 11:00
Does this answer your question? [How can I combine two or more querysets in a Django view?](https://stackoverflow.com/questions/431628/how-can-i-combine-two-or-more-querysets-in-a-django-view) — Ali Aref, May 16 '21 at 11:01
_combine_ is quite a misnomer for what you want, you don't want to combine querysets, you just want the related instances. — Abdul Aziz Barkat, May 16 '21 at 11:03

Abdul Aziz Barkat · Accepted Answer · 2021-05-16T11:39:08.023

0

You can simply loop over the related Document objects from the Event object itself by using document_set (the default related_name for Document i.e. the model name in lowercase with _set appended). Also you can optimise the number of queries made by using prefetch_related [Django docs]:

In your view:

@login_required
def consultant_home(request):
    consultant_pr = Consultant_Profile.objects.get(user_id=request.user)
    events = Event.objects.filter(consultant_id=consultant_pr.pk).prefetch_related('document_set')
    context = {'events': event, 'consultant_pr': consultant_pr}
    return render(request, 'Consultant/consultant_documents.html', context)

In your template:

{% for event in events %}
    <p>Event id: {{ event.event_id }}</p>
    {% for document in event.document_set.all %}
        <p>document name: {{ document.document_name }}</p>
        <!-- Use document.path.url instead of manually rendering its url -->
        <p>path: <a href="{{ document.path.url }}">{{ document.path }} </a></p>
    {% endfor%}
{% endfor %}

edited May 16 '21 at 11:39

answered May 16 '21 at 11:09

Abdul Aziz Barkat

19,475
3
20
33

i tried that still giving error saying "Cannot find 'document' on Event object, 'document' is an invalid parameter to prefetch_related()" – YASH JARIWALA May 16 '21 at 11:34
@YASHJARIWALA try `.prefetch_related('document_set')` instead? – Abdul Aziz Barkat May 16 '21 at 11:35
now it is just displaying the event id not the list of documents for an event – YASH JARIWALA May 16 '21 at 11:37
oh....its done now thanks – YASH JARIWALA May 16 '21 at 11:39

How to combine multiple querysets in django

1 Answers1