Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#30650 closed New feature (needsinfo)

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

Reported by: Scott Deerwester Owned by: nobody
Component: Database layer (models, ORM) Version: 2.2
Severity: Normal Keywords: QuerySet.extra
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (1)

comment:1 by Simon Charette, 5 years ago

Resolution: needsinfo
Status: newclosed

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.

Version 0, edited 5 years ago by Simon Charette (next)
Note: See TracTickets for help on using tickets.
Back to Top