LEFT JOIN on two Django Querysets

d-dev 0
Viewed 20

I've been searching SO and Google for two days now and haven't found a solution to my specific problem. I have a SQL query that I am trying to convert into Django ORM (if even possible). In SQL, the query looks something like this:

SELECT *
FROM(
SELECT
    max(effective_date) AS effective_date,
    floorplan_id
FROM
    floorplan_counts
GROUP BY
    floorplan_id) s1
LEFT JOIN (
        SELECT
            floorplan_id AS fc_floorplan_id,
            units AS fc_units,
            beds AS fc_beds,
            expirations AS fc_expirations,
            effective_date
        FROM
            floorplan_counts) s2 ON s1.floorplan_id = s2.fc_floorplan_id
)

I am able to use Django ORM to generate subqueries s1 and s2:

s1 = FloorplanCount.objects.values('floorplan_id').annotate(effective_date=Max('effective_date')
s2 = FloorplanCount.objects.values('effective_date').annotate(
            fc_floorplan_id=F('floorplan_id'), fc_units=F('units'), fc_beds=F('beds'), fc_expirations=F('expirations'))

Currently, the solution I have in place is using Django .raw(), but would like to use Django ORM. Also would like to not use .extra() since documentations says it will be deprecated in the future.

How can I left join s1 to s2?

left-join django-orm mysql django
1 Answers

i don't know your model but i don't understand why you need second select:

queryset = FloorplanCount.objects.annotate(
    effective_date=Max('effective_date'),
    fc_floorplan_id=F('floorplan_id'), 
    fc_units=F('units'), 
    fc_beds=F('beds'),
    fc_expirations=F('expirations')
)
Maxim Danilov0
Related