Home > Software design >  Django: Add result from second queryset to first queryset
Django: Add result from second queryset to first queryset

Time:11-18

I have two models from two different databases (one read-only) without ForeignKey between the two models (did not get that working, as far i found it isn't possible). In the main model I store the ID from the second model (read-only DB). I want to display multiple records/rows on one view (like al table) There for I want to get the content of the second model with the id from the main model. and combine it to one row. Normal you can get it by the ForeignKey but did won't work with 2 different databases.

What i got (simplified):

model.py

class Overeenkomst(models.Model):
    park = models.IntegerField(blank=False, null=False, default='0')
    object = models.IntegerField(blank=False, null=False, default='0')  # ID from model second database  
    date_start = models.DateField()
    date_end = models.DateField()


class Object(models.Model):
    id = models.IntegerField(db_column='ID', primary_key=True)  # Field name made lowercase.
    nummer = models.IntegerField(db_column='NUMMER', blank=True, null=True)  # Field name made lowercase.
    omschrijving = models.CharField(db_column='OMSCHRIJVING', max_length=50, blank=True, null=True)  # Field name made lowercase.
    idobjectsoort = models.IntegerField(db_column='IDOBJECTSOORT', blank=True, null=True)  # Field name made lowercase.
    idobjecttype = models.IntegerField(db_column='IDOBJECTTYPE', blank=True, null=True)  # Field name made lowercase.
    (.....)

    class Meta:
        managed = False
        db_table = 'OBJECT'
        unique_together = (('nummer', 'idpark', 'id'), ('id', 'idpark', 'idobjecttype', 'idobjectsoort', 'dubbelboeking'), ('code', 'id'),)


    def __str__(self):
        return self.omschrijving

view.py

def ovk_overview(request):
    ctx={}

    overeenkomsten =models.Overeenkomst.objects.filter(park=request.session['park_id'])

    ovk = []
    for overeenkomst in overeenkomsten:
        obj = models.Object.objects.using('database2').filter(pk=overeenkomst.object).values('omschrijving')
        
        ##### Here I Missing a part #####
        

    ctx['overeenkomsten'] = ovk
    return render(request, 'overeenkomsten/overzicht.html', context=ctx)

overzicht.html

{% extends "base.html" %}
{% load static %}

{% block content %} 
<table class='table table-sm'>
    <tr>
        <th>#</th>
        <th>Object</th>
        <th>Start datum</th>
        <th>Eind datum</th>
        <th>&nbsp;</th>
    </tr>

    {% for ovk in overeenkomsten %}
    {{ ovk }}::
    <tr>
        <td>{% now 'Y' %}{{ ovk.park }}{{ovk.object}}{{ovk.id}}</td>
        <td>{{ ovk.object.omschrijving}}</td>
        <td>{{ ovk.date_start|date:"d-m-Y" }}</td>
        <td>{{ ovk.date_end|date:"d-m-Y" }}</td>
        <td><a href="{% url 'overeenkomsten:pdf_vast' ovk.id %}" target="_blank"><button class="btn btn-primary">Download pdf</button></a></td>
    </tr>
    {% endfor %}
</table>
{% endblock %}

I have tried to use list() and chain() (as answer https://stackoverflow.com/a/8171434) but then i get only the values of the Object model and nothing from the Overeenkomsten model.

I hope someone has a answer/idea for me.

CodePudding user response:

You could do something like this.

views.py

from django.forms.models import model_to_dict 

    def ovk_overview(request):
        ctx={}
    
        overeenkomsten =models.Overeenkomst.objects.filter(park=request.session['park_id'])
    
        ovk = []
        for overeenkomst in overeenkomsten:
            overeenkomst_dict = model_to_dict(overeenkomst)
            obj = models.Object.objects.using('database2').get(pk=overeenkomst.object) #Assumed there is always an obj. If not, change accordingly.
            overeenkomst_dict['omschrijving'] = obj.omschrijving
            ovk.append(overeenkomst_dict) 
            
            
            
    
        ctx['overeenkomsten'] = ovk
        return render(request, 'overeenkomsten/overzicht.html', context=ctx)

overzicht.html

{% extends "base.html" %}
{% load static %}

{% block content %} 
<table class='table table-sm'>
    <tr>
        <th>#</th>
        <th>Object</th>
        <th>Start datum</th>
        <th>Eind datum</th>
        <th>&nbsp;</th>
    </tr>

    {% for ovk in overeenkomsten %}
    {{ ovk }}::
    <tr>
        <td>{% now 'Y' %}{{ ovk.park }}{{ovk.object}}{{ovk.id}}</td>
        <td>{{ ovk.omschrijving }}</td>
        <td>{{ ovk.date_start|date:"d-m-Y" }}</td>
        <td>{{ ovk.date_end|date:"d-m-Y" }}</td>
        <td><a href="{% url 'overeenkomsten:pdf_vast' ovk.id %}" target="_blank"><button class="btn btn-primary">Download pdf</button></a></td>
    </tr>
    {% endfor %}
</table>
{% endblock %}

CodePudding user response:

First, a warning. Don't call fields object or id. They aren't Python reserved words, but using them overlays the meanings normally supplied by Python. It's confusing as hell, and in the case of object in particular, is also likely to cause you a world of pain at a later date if you start using Class-based views and Mixins. So call them something_id, or just obj.

OK. An idea ... yes. It depends on having enough memory to convert the main queryset into a list of objects. Then you "annotate" the objects in the first list, with the data from the corresponding objects in the second database.

I've replaced object with other_id in the following, because I simply couldn't think about it with the original name. It was like BLUE printed in RED ink.

    # query the first DB. You might want to chheck that the length
    # of the query is sensible, and/or slice it with a maximum length

    overeenkomsten  = models.Overeenkomst.objects.filter(park=request.session['park_id'])
    if overeenkomsten.count() > MAX_OBJECTS: # shouldn't ever happen
        # do something to save our server!
        overeenkomsten = overeenkomsten[:MAX_OBJECTS]

    # fetch all the data
    overeenkomsten = list(  overeenkomsten )

    # get the required data from the other DB. 
    # One query, retaining pk to tie the two together. 
    # avoids N queries on second DB

    other_db_ids = [ x.other_id for x in  overeenkomsten ]  # was x.object
    data_from_other_db = models.Object.objects.using('database2'
        ).filter(pk__in = other_db_ids
        ).values_list('pk', 'omschrijving'
        )

    # convert to a dict. This way for clarity and because I can't remember the dict method 
    #for converting a list of key/value pairs into a dict.

    omschrivings = {}
    for k,v in data_from_other_db:
         omschrivings[k] = v

    # "Annotate" the objects from the first query with the data from the second. 
    # It's read-only so no need to worry about saving it should somebody update it.
    # (but you could subclass the save method if it wasn't)

    for obj in overeenkomsten:
       setattr( obj, 'omschriving', omschrivings[ obj[x.other_id] ] )

And in the template, just

<td>{{ ovk.omschrijving}}</td>
  • Related