id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
30650	Need QuerySet.extra for a where clause based on on a CharField value in a second model	Scott Deerwester	nobody	"I'm responding to ""[https://docs.djangoproject.com/en/2.2/ref/models/querysets/#extra please file a ticket]"" in the QuerySet documentation. I have a couple of models something like:

{{{
    class ShippingStory(models.Model):
        ...
        story_type = models.CharField(...)
        status = models.CharField(...)
        orig_airport_code = models.CharField(...)
        dest_airport_code = models.CharField(...)
        orig_port_code = models.CharField(...)
        dest_port_code = models.CharField(...)
        ...

    class Airport(models.Model):
        name = models.CharField(...)
        iata_code = models.CharField(...)
        country = models.CharField(...)
}}}

I want to do the equivalent of:

{{{
    SELECT shippingstory.* FROM shippingstory, airport
    WHERE
       ... shipping story-related selections
       AND
       airport.iata = shippingstory.orig_airport_code AND
       airport.country = 'US';
}}}

I'm unable to see how to do that without resorting to something like

{{{
    subquery = {...} # fields related only to the ShippingStory model
    qs = ShippingStory.objects.filter(**subquery)
    qs = qs.extra(
        where='shippingstory.orig_airport_code=airport.iata AND airport.country=%s'
        where_params=(orig_country,),
        tables=('airport',)
    )
}}}

For a number of complicated reasons, I'm unable to express the respective fields in `ShippingStory` as `ForeignKey` fields."	New feature	closed	Database layer (models, ORM)	2.2	Normal	needsinfo	QuerySet.extra		Unreviewed	0	0	0	0	0	0