Raw query results multiplies many times when I use INNER JOIN in Django

I can’t figure it out why my raw query multiplies the result as many time as the number of users. I’m using INNER JOIN and I bonded two objects so I really can’t get it. (I’m not experienced in Django Query Language so I’m using raw query).

views.py

def index(request):
    projekt = Projekt.objects.raw('SELECT  projekt_id, stressz_projekt.projekt, last_name, first_name, stressz_profile.id, stressz_profile.user_id, auth_user.id FROM stressz_profile INNER JOIN stressz_projekt ON stressz_projekt.id = stressz_profile.projekt_id INNER JOIN auth_user ON auth_user.id = stressz_profile.user_id')

    template = loader.get_template('stressz/index.html')

    context = {
        'projekt': projekt,
    }
    return HttpResponse(template.render(context, request))

If I’m using Django ORM I get the same result

profile = Profile.objects.get(user=request.user)
projekt = Profile.objects.filter(projekt=profile.projekt)

models.py

class Viselkedestipus(models.Model):

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

        user_name = models.ForeignKey(User, on_delete=models.CASCADE, default=1)
        date = models.DateField(auto_now_add=True, auto_now=False, blank=True)


class Profile(models.Model):

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

    user = models.OneToOneField(User, null=True, on_delete=models.CASCADE)
    date = models.DateField(auto_now_add=True, auto_now=False, blank=True)
    projekt = models.ForeignKey(Projekt, on_delete=models.CASCADE, default=1)


class Projekt(models.Model):

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

    projekt = models.TextField(max_length=150)
    company_name = models.ForeignKey('Company', on_delete=models.CASCADE, default=1)
    date = models.DateField(auto_now_add=True, auto_now=False, blank=True)

Answer

Here is a simple example of how to use the ORM that you can adapt to your case:

Assume we have two models, a Country and a City like so:

class Country(models.Model):

    """ country model """

    name = models.CharField(...)


class City(models.Model):

    """ city model with a foreign key to a country """

    country = models.ForeignKey('Country', ..., related_name='cities')
    name = models.CharField(...)
    population = models.PositiveIntegerField(...)

We can use the Django ORM as follows:

# filter for a country with a given name:
country = Country.objects.filter(name='Italy')
country.name # Italy

# filter for a city with a given name:
city = City.objects.filter(name='Rome')
city.name # Rome

# get the country the city belongs to:
country = city.country
country.name # Italy

# get the queryset of all cities with a population greater than 1 million in a specific country:
country = Country.objects.filter(name='Italy')
large_cities = country.cities.filter(population__gt=1000000)
for city in large_cities:
    print(city.name)   
# loop will print: Rome, Milan

Also, it looks like you are just passing a Projeckt instance to your template. So you really don’t need the ORM, instead you can do something like:

I’ll also briefly add it appears you have reversed the profile-projekt relation – as it stands a profile can only have one projekt, but a projekt can have many profiles – not sure if that was intended.

views.py

def index(request):

    # get projekt by id:
    projekt = Projekt.objects.get(id=<the-id>)

    template = loader.get_template('stressz/index.html')

    context = {
        'projekt': projekt,
    }
    return HttpResponse(template.render(context, request))

And then in your template: stressz/index.html

<!-- use context data in template -->
<div> Name: {{projekt.projekt}} </div>

<!-- loop over all profiles in the project -->
{% for profile in projekt.profile_set %}
  <div> User First Name: {{ profile.user.first_name }} </div>
  <div> User Last Name: {{ profile.user.last_name}} </div>
{% endfor %}