#29262: Custom Left Outer Join in Queries
-------------------------------------+-------------------------------------
Reporter: Sassan Haradji | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:
Keywords: ORM Join | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Sassan Haradji):
Unfortunately I don't think so. What I needed was exactly left join.
The query I needed was a rather complicated query, I try to abstract it
here so that we can investigate it and find out what's needed in Django
ORM to achieve it.
Suppose that I have this table:
{{{
CREATE TABLE foo(id,related_id,value,type)
AS VALUES
( 1 , 1, 'A1' , 1 ),
( 2 , 1, 'A2' , 2 ),
( 3 , 1, 'A3' , 3 ),
( 4 , 1, 'A4' , 4 ),
( 5 , 1, 'A5' , 5 ),
( 6 , 2, 'B1' , 1 ),
( 7 , 2, 'B2' , 2 ),
( 8 , 2, 'B3' , 3 ),
( 9 , 2, 'B4' , 4 ),
( 10, 2, 'B5' , 5 )
;
}}}
I want to aggregate these values and make this intermediate table:
{{{
-----------------------------------------------------------------------------
| id | related_id | values
|
-----------------------------------------------------------------------------
| 1 | 1 | (('A1',1),('A2',2),('A3',3),('A4',4),('A5',5))
|
-----------------------------------------------------------------------------
| 6 | 2 | (('B1',1),('B2',2),('B3',3),('B4',4),('B5',5))
|
-----------------------------------------------------------------------------
}}}
To do so I need to do a simple aggregation:
{{{
foo.objects.values('related_id').annotate(
id=Min('id'),
values=ArrayAgg(
Func(
'value',
'type',
function='ARRAY',
template='%(function)s[%(expressions)s]',
arg_joiner=',',
), output_field=ArrayField(ArrayField(models.FloatField())),
),
)
}}}
This will generate this sql query (or something equivalent):
{{{
SELECT t.*
FROM (
SELECT
min(id),
related_id,
array_agg(ARRAY(value, type)) AS values,
FROM foo
GROUP BY id
) AS t
}}}
So far so good. Then I want to order this query based on value column but
this order should order values of this column that are in a row that has
type=X. I can do so by this sql:
{{{
SELECT t1.*
FROM (
SELECT
min(id),
related_id,
array_agg(ARRAY(value, type)) AS values,
FROM foo
GROUP BY id
) AS t1
LEFT OUTER JOIN (SELECT value FROM foo WHERE type=X) AS t2 USING (id)
ORDER BY t2.value
}}}
This is where I need this left join. It should be left join cause I don't
wanna miss rows that don't have X type value.
Now do you think it's possible to do the above with current Django ORM
API?
Consider that this table is really big, and the above sql query is the
only one I found that executes in rational time and doesn't miss anything.
Also the real problem is much more complected, the number of columns that
are involved are much more so if you think there's room for simplifying
the above sql solution consider that in my real usecase it may not be
applicable so I please lets concentrate on interpreting the exact above
query into Django ORM API and not change the sql query so that it fits the
API.
--
Ticket URL: <https://code.djangoproject.com/ticket/29262#comment:11>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/065.e88defa24661d0066f9179754a6e7aea%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
