CODEWITHSUNDEEP
pythonsqldjangodjango-models
davidnotplay
davidnotplay

Reputation: 11

Get data effectively using django model

I'm using Django and i have one question about models. I have 2 models, both related to a 'ManyToMany' field.

I want a query that gets the data of all tags, the last image with the tag and the number of images with the tag. I used this code

        selargs = (
        'id', 
        'image__title',
        'image__smallImage', 
        'image__smallWidth', 
        'image__smallHeight',
        'name', 
        'nameCleaned')

    tags = Tag.objects.values(*selargs).annotate(
        imageCount=Count('image__id')).order_by('-image__date')

The code works but the problem is the sql sentence, is so .... pfff

SELECT "gallery_tag"."id", "gallery_image"."title", "gallery_image"."smallImage", "gallery_image"."smallWidth", "gallery_image"."smallHeight", "gallery_tag"."name", "gallery_tag"."nameCleaned", COUNT("gallery_image_tags"."image_id") AS "imageCount" 
FROM "gallery_tag" 
LEFT OUTER JOIN "gallery_image_tags" ON ( "gallery_tag"."id" = "gallery_image_tags"."tag_id" ) 
LEFT OUTER JOIN "gallery_image" ON ( "gallery_image_tags"."image_id" = "gallery_image"."id" ) 
GROUP BY "gallery_tag"."id", "gallery_image"."title", "gallery_image"."smallImage", "gallery_image"."smallWidth", "gallery_image"."smallHeight", "gallery_tag"."name", "gallery_tag"."nameCleaned", "gallery_image"."date" 
ORDER BY "gallery_image"."date"

i don't know if this is effective. Is there a better way? Is important that i get the data in one operation because after i use this query in a paginator for limit the the records. Is possible do this in one query or is it better use more models operations?

Thank you.

Upvotes: 1

Views: 62

Answers (1)

knbk
knbk

Reputation: 53669

If it ain't broken, don't fix it - this extends to optimization in Python/Django. You can spend half your development time on optimizing small parts of your code, and it will still barely make a difference. Just make sure to use a proper architecture for your application.

You'll want to avoid doing a lot of queries per request, as even in small-scale applications the difference will be noticeable, if it's not already driving you nuts. Don't build querysets in a for-loop, and use select_related() and prefetch_related() where applicable, and you'll be fine.

The query in your case is absolutely fine. LEFT OUTER JOINs are optimized quite well across database engines, even across multiple tables. Pagination prevents a large result set from clogging up your system resources by limiting on a database-level.

If you notice that a certain request is consistently taking a considerable amount of time, then - and only then - is it time to profile your application and find out exactly what is causing this slowdown - and of course to fix it.

Upvotes: 3

Related Questions