How do I join on two non-unique columns in django?

I would like to be able to join two models by a column which is not unique in either one.

class Ctystate(models.Model):
    ctystate_id = models.IntegerField(primary_key=True)
    zip5 = models.IntegerField()
    [...]

class Addr2zip(models.Model):
    add开发者_StackOverflow中文版r2zip_id = models.IntegerField(primary_key=True)
    ctystate = models.ForeignKey(Ctystate, db_column='zip5', to_field='zip5')
    zip5 = models.IntegerField()
    [...]

Using the ForeignKey field just results in a model validation error:

Field 'zip5' under model 'Addr2zip' must have a unique=True constraint.

Is there any way I can join these two models using the 'zip5' field in both? I do realize that this could result in duplicates in results but there are other protections against that that will be used in the filter.

I'd also prefer not to have an intermediate table. If I have to, I can just write my own SQL but am trying to stick with the ORM as much as possible. Thanks.

a single zip code in the US may contain >1 city. A city definitely can have > 1 zip code, so you need a ManyToMany relation there.

Further, a zip code can cross state line and there are cities that cross state lines (e.g. Kansas City), but formally each municipality belongs to only one state.

The data models will depend on how you want to interpret this.

Most likely you won't even need to run joins like that with reasonable models.

Something like this might work though of you want to keep your models without changes (you'll need to check the real table names):

addr_list = Ctystate.objects.extra(
    select = {
        'city_name': 'ctystate.name',
        'addr': 'addr2zip.address'
    },
    where = ['ctystate.zip5 = addr2zip.zip5'],
    tables = ['ctystate', 'addr2zip']#assuming that those are names in your table
)

I think you should be able to solve it like this:

class Ctystate(models.Model):
    ctystate_id = models.IntegerField(primary_key=True)
    zip5 = models.IntegerField(unique=True)

class Addr2zip(models.Model):
    addr2zip_id = models.IntegerField(primary_key=True)
    ctystate = models.ForeignKey(Ctystate, db_column='zip5', to_field='zip5')

If you would do it like that, the ForeignKey ctystate will be represented by the value of zip5 in a db column named zip5. If you want to use a foreign key realtion you must set the to_field to unique=True because otherwise it wouldn't be a 1:n (foreign key) relation anymore (every Addr2zip object could relate otherwise to more than one Ctystate object, which would be a m:n relation - if you need this you must solve it using a ManyToManyField!).