[Answer]-Multiple joins in Django model

by

1👍

✅

This is how I would refactor your code:

class Species(models.Model):
    name = models.CharField(db_column='Name', max_length=100)

class Status(models.Model):
    species = models.ForeignKey(Species, db_column='Species ID', primary_key=True)
    protected = models.NullBooleanField(db_column='Protected')

class Images(models.Model):
    species = models.ForeignKey('Species', db_column='Species ID')
    url = models.CharField(db_column='URL', max_length=719)

Then I would do:

protected_species = Status.objects
                    .filter(protected=True)
                    .values_list('species_id', flat=True)
Images.objects.filter(species_id__in=protected_species)
              .values('url','species__name')

But as @Maxime Lorant said, I think your Models are dazzled.

👤Mihai Zamfir

0👍

If you change your models a little bit, you could do something like this:

images = Images.objects.filter(species__status__protected=True)

Updated models:

class Species(models.Model):
    name = models.CharField(max_length=100)

class Status(models.Model):
    species = models.ForeignKey('Species', primary_key=True)
    protected = models.NullBooleanField()

class Images(models.Model):
    species = models.ForeignKey('Species')
    url = models.CharField(max_length=719)
👤Matt

0👍

Your database structure is kind of broken first. I suppose there is only 1 status per Species object, so you don’t need to have 3 models there. With 2 models you can do the following:

class Species(models.Model):
    species_id = models.IntegerField(db_column='Species ID', primary_key=True)
    name = models.CharField(db_column='Name', max_length=100)
    protected = models.NullBooleanField(db_column='Protected')


class Images(models.Model):
    species = models.ForeignKey('Species', db_column='Species ID')
    url = models.CharField(db_column='URL', max_length=719)

# query
data = Images.objects.filter(species__protected=True).values('url', 'species__name')
👤Maxime Lorant

Leave a comment