Need QuerySet.extra for a where clause based on on a CharField value in a second model

[Scott Deerwester]

I'm responding to "​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.

[Simon Charette]

Hello Scott,

Could you elaborate on these complicated reasons?

Given this query could be simply expressed as .filter(orig_airport__country='US') I'm afraid that unless you provide a bit more details we can't judge whether or not this is a valid request to keep .extra(**) around or introduce a new API.

Please reopen this ticket with more details if you can provide more details about why ForeignKey and it's large number of options (e.g. to_field='iata_code', db_constraints=False) can't be use for your needs.

Thanks.