django queryset/annotate get extra column

[stunaz]

Hi,

I would like to write the following query using django orm.

select * ,
(select image_url from blog_image bi where bi.blog_id = b.id and bi.principal=1) as blog_main_imange
from blog b

Any idea, how to write the queryset ?

[James Schneider]

Without Django models, not with much accuracy.

Assuming that your models are named Blog and BlogImage, with an FK from Blog to BlogImage named 'image', I think you can do something like this:

from django.db.models import Prefetch

Blog.objects.all().prefetch_related(

    Prefetch('image', 

             queryset=BlogImage.objects.filter(principal=1), 

             to_attr='blog_main_image'

            )

)

References:

https://docs.djangoproject.com/en/1.9/ref/models/querysets/#prefetch-related

https://docs.djangoproject.com/en/1.9/ref/models/querysets/#prefetch-objects

Completely untested and written off the top of my head using a model structure I deduced from your query, so YMMV, but I hope that at least points you in the right direction.

-James

[stunaz]

Wonderfull
(ps :sorry, I should have put the model)