CODEWITHSUNDEEP
djangoormsubquery
wuent
wuent

Reputation: 63

How to django ORM with a subquery?

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?

Upvotes: 4

Views: 3867

Answers (2)

youxun
youxun

Reputation: 21

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()

Upvotes: 0

Paulo Scardine
Paulo Scardine

Reputation: 77251

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.

Upvotes: 2

Related Questions