Reputation: 343

How to make queries from multiple models in a single view

I am working on a school placement system and I have multiple models to query from. First I want to loop through the students' list and check if the two student's ID in the profile and result model match. Then I will check if the student has passed and then place the student.

I am using Django 2.2.1. I did a lot of search but to no avail. Below are some of my codes.

Here is my student model.

class StudentProfile(models.Model):
    user = models.OneToOneField(User, on_delete=models.CASCADE)

    # Extra Fields
    student_ID = models.CharField(max_length=10)
    middle_Name = models.CharField(max_length=50, blank=True)
    gender = models.CharField(max_length=10, choices=STUDENT_GENDER_CHOICES)
    disability = models.CharField(max_length=5, choices=STUDENT_DISABILITY_CHOICES)
    locality = models.CharField(max_length=30, choices=STUDENT_LOCALITY_CHOICES)
    age = models.PositiveIntegerField()
    profile_picture = models.ImageField(upload_to='profile_photos', blank=True, default='profile_photos/default.svg')

    def __str__(self):
        return self.user.username

This is my result upload model

class StudentResultsUpload(models.Model):
    student_ID = models.CharField(max_length=10)
    english = models.PositiveIntegerField()
    mathematics = models.PositiveIntegerField()
    integrated_Science = models.PositiveIntegerField()
    social_Studies = models.PositiveIntegerField()
    basic_Design_Technology = models.PositiveIntegerField()
    home_Economics = models.PositiveIntegerField()
    ghanaian_Language = models.PositiveIntegerField()
    french = models.PositiveIntegerField(blank=True)

This is my school selection model.

NB: I removed the multiple school models and put everything in one model.

class SchoolSelection(models.Model):
    student = models.ForeignKey(User, on_delete=models.CASCADE)

    school_One = models.CharField(max_length=100)
    program_One = models.CharField(max_length=50)
    residential_Status_One = models.CharField(max_length=50, choices=RESIDENTIAL_STATUS_CHOICES)

    school_Two = models.CharField(max_length=100)
    program_Two = models.CharField(max_length=50)
    residential_Status_Two = models.CharField(max_length=50, choices=RESIDENTIAL_STATUS_CHOICES)

    school_Three = models.CharField(max_length=100)
    program_Three = models.CharField(max_length=50)
    residential_Status_Three = models.CharField(max_length=50, choices=RESIDENTIAL_STATUS_CHOICES)

    school_Four = models.CharField(max_length=100)
    program_Four = models.CharField(max_length=50)
    residential_Status_Four = models.CharField(max_length=50, choices=RESIDENTIAL_STATUS_CHOICES)

    school_Five = models.CharField(max_length=100)
    program_Five = models.CharField(max_length=50)
    residential_Status_Five = models.CharField(max_length=50, choices=RESIDENTIAL_STATUS_CHOICES)

And this is my views.py for placement.

def placement(request):
    profile = StudentProfile.objects.all()
    place = StudentResultsUpload.objects.all()
    schools = SchoolSelection.objects.all()


    placements = []
    for candidate in profile:
        for pl in place:

            if candidate.student_ID == pl.student_ID:
                if (pl.english >= 50 and pl.mathematics >= 50 and pl.integrated_Science >= 50 and pl.social_Studies >= 50):

                    raw_score = (pl.english + pl.mathematics + pl.integrated_Science + pl.social_Studies + pl.basic_Design_Technology + pl.home_Economics)
                    for school in schools:
                        if raw_score >= 480:
                            place_me = school.school_One
                        elif raw_score >= 420:
                            place_me = school.school_Two
                        elif raw_score >= 360:
                            place_me = school.school_Three
                        elif raw_score >= 300:
                            place_me = school.school_Four
                        else:
                            place_me = school.school_Five

                        placements.append({'student': candidate, 'school': place_me})
                    return render(request, 'schools/placement.html', {'place_me': placements})

                else:
                    return HttpResponse("Sorry, you did not qualify for placement.")

Here is my placement.html file

<div class="container">
<p id="success">
    Placement Here.
</p>
{% for placement in place_me %}
    Stdudent ID: {{ placement.student.student_ID }}<br>
    Stdudent username: {{ placement.student.user }}<br>
    Stdudent gender: {{ placement.student.gender }}<br>
    School: {{ placement.school }}<br><br>

{% endfor %}
</div>

But when I run it, it is not iterating properly through all the students in the system. It is only retaining multiple instances of one student but it will pick the schools of other students and display for the one student that it is returning its multiple instances.

Upvotes: 1

Answers (3)

theomeli

Reputation: 400

As Daniel Roseman indicated you should use Foreign Keys to associate different models. A refactoring of your code should be something like the codes below.

The StudentResultsUpload model

class StudentResultsUpload(models.Model):
    student = models.ForeignKey(StudentProfile, on_delete=models.CASCADE, null=True)
    english = models.PositiveIntegerField()
    mathematics = models.PositiveIntegerField()
    integrated_Science = models.PositiveIntegerField()
    social_Studies = models.PositiveIntegerField()
    basic_Design_Technology = models.PositiveIntegerField()
    home_Economics = models.PositiveIntegerField()
    ghanaian_Language = models.PositiveIntegerField()
    french = models.PositiveIntegerField(blank=True)

The SchoolSelectionOne model

class SchoolSelectionOne(models.Model):
    student = models.ForeignKey(StudentProfile, on_delete=models.CASCADE, null=True)
    name_of_School = models.CharField(max_length=100)
    program = models.CharField(max_length=50)
    residential_Status = models.CharField(max_length=50, choices=RESIDENTIAL_STATUS_CHOICES

The placement function

def placement(request):
    profiles = StudentProfile.objects.all()
    uploads = StudentResultsUpload.objects.all()
    school_one = SchoolSelectionOne.objects.all()
    school_two = SchoolSelectionTwo.objects.all()
    school_three = SchoolSelectionThree.objects.all()
    school_four = SchoolSelectionFour.objects.all()
    school_five = SchoolSelectionFive.objects.all()

    profile_dicts = []
    for profile in profiles:
        try:
            upload = [u for u in uploads if u.student.pk == profile.pk][0]
            one = [s for s in school_one if s.student.pk == profile.pk][0]
            two = [s for s in school_two if s.student.pk == profile.pk][0]
            three = [s for s in school_three if s.student.pk == profile.pk][0]
            four = [s for s in school_four if s.student.pk == profile.pk][0]
            five = [s for s in school_five if s.student.pk == profile.pk][0]
        except (AttributeError, IndexError):
            continue

        profile_dicts.append({
            'upload': upload,
            'school_one': one,
            'school_two': two,
            'school_three': three,
            'school_four': four,
            'school_five': five
        })

    place_me_list = []
    for profile in profile_dicts:
        upload = profile['upload']

        if (
                upload.english >= 50 and
                upload.mathematics >= 50 and
                upload.integrated_Science >= 50 and
                upload.social_Studies >= 50
        ):
            raw_score = (
                upload.english +
                upload.mathematics +
                upload.integrated_Science +
                upload.social_Studies +
                upload.basic_Design_Technology +
                upload.home_Economics
            )

            if raw_score >= 480:
                place_me = profile['school_one']
            elif raw_score >= 420:
                place_me = profile['school_two']
            elif raw_score >= 360:
                place_me = profile['school_three']
            elif raw_score >= 300:
                place_me = profile['school_four']
            else:
                place_me = profile['school_five']

            place_me_list.append(place_me)

    return render(request, 'schools/placements.html', {'place_me': place_me_list})

In placement function to avoid using the nested for I firstly found the desired information for each candidate (supposing that each one is associated with schools and upload models) and then I made the calculations.

Upvotes: 2

katoozi

Reputation: 398

place is a queryset. so must be iterate it.

try this code:

def placement(request):
    profile = StudentProfile.objects.all()
    place = StudentResultsUpload.objects.all()
    school_one = SchoolSelectionOne.objects.all()
    school_two = SchoolSelectionTwo.objects.all()
    school_three = SchoolSelectionThree.objects.all()
    school_four = SchoolSelectionFour.objects.all()
    school_five = SchoolSelectionFive.objects.all()

    placements = []
    for candidate in profile:
        for pl in place:
            if candidate.student_ID == pl.student_ID:
                if (pl.english >= 50 and pl.mathematics >= 50 and pl.integrated_Science >= 50 and pl.social_Studies >= 50):
                    raw_score = (pl.english + pl.mathematics + pl.integrated_Science + pl.social_Studies + pl.basic_Design_Technology + pl.home_Economics)

                    if raw_score >= 480:
                        place_me = school_one
                    elif raw_score >= 420:
                        place_me = school_two
                    elif raw_score >= 360:
                        place_me = school_three
                    elif raw_score >= 300:
                        place_me = school_four
                    else:
                        place_me = school_five

                    placements.append({'student': candidate, 'school': place_me})
    return render(request, 'schools/placement.html', {'place_me': placements})

placement.html:

<div class="container">
    <p id="success">
        Placement Here.
    </p>
    {% for placement in place_me %}
        Stdudent Id: {{ placement.student.student_ID }}
        Stdudent middle name: {{ placement.student.middle_Name }}
        Stdudent gender: {{ placement.student.gender }}

        Placements:
            {% for school in placement.school %}
                {{ school }}
            {% endfor %}
    {% endfor %}
</div>

Upvotes: 0

voodoo-burger

Reputation: 2153

On these two lines

for candidate in profile:
    if profile.student_ID == place.student_ID:

you are indicating you want to use candidate as the variable name inside the loop, so the second line should probably read if candidate.student_ID instead of if profile.student_ID.