CODEWITHSUNDEEP
pythondjangoorm
Lucas Ou-Yang
Lucas Ou-Yang

Reputation: 5655

Custom SQL in the django ORM: Filtering a list based off another filtered list in one query

What i'm trying to do is retrieve a filtered list of CarModel objects where the carfield is in a list of fields of another model, say the GasModel. But the set of GasModels must also be filtered out, to a list where a field in GasModel must equal another field from the CarModel (different field).

So pretty much I want to filter a list so that a field of that list is contained in a separate list of fields of a different model, and that list (of the second model) is also filtered (but by a different field of the first (car) model). I'd like for this to be all in one queryset call.

This is what I have so far, the error I belive is

WHERE anothergasfield = another_field_from_car_carmodel

Am I missing a FROM keyword or something? And if so where should it go?

CarModel.objects.extra(where = ['carfield IN (SELECT gasfield FROM\
    gas_gasmodel WHERE anothergasfield = another_field_from_car_carmodel)'])
      .order_by(...)

Thanks

Upvotes: 1

Views: 332

Answers (1)

miki725
miki725

Reputation: 27861

How about this:

CarModel.objects.extra(where = ['carfield IN (SELECT gasfield FROM\
    gas_gasmodel WHERE anothergasfield = carmodel.another_field_from_car_carmodel)'])
      .order_by(...)

Just replace the carmodel with the table name for CarModel. Usually is {{ app_name }}_{{ model_name }}.

You have nested select statement inside of which fields are from gas_gasmodel table, not from carmodel table.

Upvotes: 3

Related Questions