How to django ORM with a subquery?

Question

The sql subquery is:

SELECT  * 
FROM   ( SELECT * 
         FROM   article 
         ORDER BY Fid desc 
         LIMIT 0, 200
       ) as l 
 WHERE  keyId = 1 
 AND  typeId = 0

I tried this:

rets = Article.objects.order_by("-Fid").values('Fid')
kwargs['keyId'] = 1
kwargs['typeId'] = 0
re =  Article.objects.filter(Fid__in=rets).filter(**kwargs).values()

But it's not working. Can anyone explain how I can do this?

http://stackoverflow.com/questions/8556297/how-to-subquery-in-queryset-in-django is an example of subquerying, but without model definitions and expected output, I can't really say for sure why it isnt working for you. — Francis Yaconiello, Jan 10 '13 at 16:30
@FrancisYaconiello: perhaps he doesn't even need a subquery (seems like the result would be the same moving the inner query conditions/modifiers to the outer query). — Paulo Scardine, Jan 10 '13 at 17:03
@Paulo that is what I was wondering. but without seeing the models/schema and expected result, its hard for me to make query suggestions. — Francis Yaconiello, Jan 10 '13 at 17:07
http://stackoverflow.com/questions/8556297/how-to-subquery-in-queryset-in-django — Ciro Santilli OurBigBook.com, Jun 08 '16 at 21:15

Paulo Scardine · Accepted Answer · 2013-01-13T21:52:07.317

2

In your case I guess you can resort to raw SQL (untested). Note that using raw SQL you have to know the real table and column names (just test the statement directly on the database first, to see if it flies).

For example:

Article.objects.raw("""SELECT * from (
                         SELECT * FROM yourapp_article 
                         ORDER BY fid DESC
                         LIMIT 0, 200
                       ) AS q1 WHERE key_id=1 AND type_id=0""")

[update]

wuent wrtote:

thanks for your help. But the raw SQL is not my wanted. I need keep my program orm style. – wuent

If you are used to more powerful/consistent ORMs like SQLAlchemy or even peewee, give up your hope. The Django ORM has a very crippled design and AFAIK you can't do this kind of thing using it - the first version of this answer started with a rant about this.

Looking at your query again, I got the impression that you do not need a subquery, try querying the table directly - my guess is the result will be the same.

edited Jan 13 '13 at 21:52

answered Jan 10 '13 at 04:10

Paulo Scardine

73,447
11
124
153

thanks for your help. But the raw SQL is not my wanted. I need keep my program orm style. – wuent Jan 10 '13 at 06:18
1

Raw SQL is not what the OP is asking for. Also, why Django ORM sucks? IMHO, this is not a good answer. First you're not answering the original question and then you're judging without any evidence something that it is not directly relevant to the question. – amb Mar 25 '14 at 15:14
@amb: Django ORM made a design decision to simplify some common use cases in exchange of expressiveness. It is clearly inferior if compared to alternatives like SQLAlchemy. AFAIK there is no way to achieve what the OP wants using the Django ORM without resorting to raw SQL. You are entitled to downvote, but I dare you to came up with a better answer. – Paulo Scardine Mar 25 '14 at 17:25

score 0 · Answer 2 · answered Jun 10 '15 at 07:48

0

How about this?

rets = Article.objects.order_by("-Fid").values_list('Fid', flat=True)
kwargs['keyId'] = 1
kwargs['typeId'] = 0
re =  Article.objects.filter(Fid__in=rets).filter(**kwargs).values()

answered Jun 10 '15 at 07:48

youxun

21
1
5

How to django ORM with a subquery?

2 Answers2