postgresql - How to get a subquery in FROM clause in Django ORM -

i'm trying express following (postgres) sql statement using django orm:

select      v.id, v.min_salary, v.max_salary, v.min_weekly_hours, v.max_weekly_hours     p.min_start_date, p.max_end_date      vacancy v,     (         select          id, vacancy_id, min(start_date) min_start_date, max(end_date) max_end_date          vacancypublication         (active = true , site_id = 1 , start_date <= current_timestamp)          group id, vacancy_id     ) p       p.vacancy_id = v.id ,     v.workflow_status = 'a' order p.min_start_date desc; 

the problem i'm using subquery in clause (also known "inline-view").

i've tried using .extra(tables=['...']) django adds quotes statement, making sql invalid.

i'd rather not resort .raw query. there way this? maybe through reusable app if core api doesn't provide way.

edit:

this (seemingly) equivalent statement using join:

    select     v.id, v.code, v.min_salary, v.max_salary, v.min_weekly_hours, v.max_weekly_hours, v.application_email, v.application_url, v.available_positions,     min(case when (p.active = true , p.site_id = 1 , p.start_date <= current_timestamp) p.start_date else null end) start_date,      max(case when (p.active = true , p.site_id = 1) p.end_date else null end) end_date base_vacancy v  left outer join      base_vacancypublication p on v.id = p.vacancy_id v.workflow_status = 'a' group v.id, v.code, v.min_salary, v.max_salary, v.min_weekly_hours, v.max_weekly_hours, v.application_email, v.application_url, v.available_positions having min(case when (p.active = true , p.site_id = 1 , p.start_date <= current_timestamp) p.start_date else null end) not null  order start_date desc; 

it's ~3 times slow, it's possible write using django 1.9 orm methods:

vacancy.objects.annotate(     start_date=min(         case(             when(publication_set__is_active=true, publication_set__site_id=1, publication_set__start_date__lte=now(), then='publication_set__start_date'),             default=none         )     ),     end_date=max(         case(             when(publication_set__is_active=true, publication_set__site_id=1, then='publication_set__end_date'),             default=none         )     ) ).filter(     start_date__isnull=false, status=workflow.approved ).order_by(     '-start_date' ) 

you can launch query directly against postgres...

will kind of:

        con = mysql.connector.connect(....)         cur = con.cursor()         query = "select v.id, v.min_salary, v.max_salary,v.min_weekly_hours, v.max_weekly_hours, p.min_start_date, p.max_end_date vacancy v, (select id, vacancy_id, min(start_date) min_start_date, max(end_date) max_end_date vacancypublication active = true , site_id = 1 , start_date <= current_timestamp) group id, vacancy_id) p p.vacancy_id = v.id , v.workflow_status = 'a' order p.min_start_date desc;"         cur.execute(query)         rows = cur.fetchall()         row in rows:         // stuff.