On Wed, 2007-04-18 at 19:57 -0300, Luiz Carlos Geron wrote:
> Hi,
> I have three models, listed at [1], that I want to get data from with
> only one query, because of performance issues with my app. The way
> that worked so far is:
>
> league_ids = [12, 21]
> bets =
> models.Bet.objects.filter(game__league__id__in=league_ids).order_by('bet__game__league.id',
> 'game_part', 'bet__game.venuedate')
>
> The problem with this is that for each bet I iterate, the database
> will be hit one or more times because I need to use
> bet.game.league.name and so on. So I tried adding .select_related() to
> the query and now it returns no results, while without
> select_related() I get 172 results.
So that looks like a bug somewhere. Using select_related() is intended
to have no effect whatsoever on the contents of the result set, from the
point of view of using the results.
If you could reduce this to a simple test case (how many relations can
you remove before it stops giving different results?), it would be good
if you could file a ticket, including the models that cause the problem,
and we could examine it in detail.
> Looking at the sql generated by
> the query with and without select_related() I noticed that with it,
> the sql was very strange, when the orm could have just added fields
> from the other tables to the select list.
Not quite sure what you mean here. What "other tables"? Without seeing
your models, it looks like all the relevant columns are being selected
in the second query.
Are some of your relations nullable? If so, that might explain the
difference in the SQL results -- there should be some left outer joins
in there for those cases, rather than all inner joins.
> Please look at the original
> generated sql at [2] and the one with select_related() at [3]. This
> looks like a bug to me, at least on the documentation, if I am not
> supposed to use select_related() with queries like this.
>
> BTW, is there are any better way to do what I want (get columns from
> the three models in only one query) with the django orm? I tried to
> use .values() in the query but it turns out that I cannot specify
> columns from other tables in it. For now I'll be using the sqlalchemy
> model I already have for this database, but I'd really appreciate to
> do everything with the django parts, since they seem to fit very good
> together and this is the first really bad problem I found while
> developing with django.
If you want to select basically arbitrary columns from arbitrary tables,
you will need to write a method containing custom SQL. That's not too
hard to do and is the recommended way. The difficulty with trying to do
this automatically in the Django ORM without resorting to SQL is that
there's no natural way to map the results back to models: they aren't
related to a model's columns any longer, after all.
Regards,
Malcolm
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
