Hi Malcolm,
Weird. I managed to fix this problem by using the distinct filter
which I didn't know existed but does what I actually wanted anyway :)
########################
// get results
friends_albums = Albums.objects.filter(users__in=friend_list,
date__gte=today).distinct().select_related()
// count results
count['friends'] = friends_albums.count()
########################
Thanks for your help though :)
Cheers,
Chris
On Dec 11, 1:56 am, Malcolm Tredinnick <[EMAIL PROTECTED]>
wrote:
> On Wed, 2008-12-10 at 02:11 -0800,Darthmahonwrote:
>
> [...]
>
>
>
> > Now what I've noticed is that the number of results (count) is
> > different between the results that have not been group and those that
> > have. Hopefully the code below makes some sense:
>
> > ###############################################
>
> > // get results
> > friends_albums = Albums.objects.filter(users__in=friend_list,
> > date__gte=today).extra(select={'count': 'count(1)'}).select_related()
>
> > // count results
> > count['friends1'] = friends_albums.count()
>
> > // now group by
> > friends_albums.query.group_by = ['album_id']
>
> > // count results again once grouped
> > count['friends2'] = friends_albums.count()
>
> > ###############################################
>
> > So count['friends1'] counts the results before being grouped, but once
> > I group them in the line after, count['friends2'] returns a different
> > number.
>
> Details below, but the first one is wrong and the second one is right.
>
> > The count in the extra parameter in the query is the correct number I
> > want, but I can't figure out how to access this within the View.pm
> > file?
>
> > Any ideas? I'll try and provide some examples of data when I get home
> > if this isn't enough information.
>
> I found it a bit hard to follow what you were doing, but I think the
> explanation is the same regardless. To understand what's going on here,
> if I were you, I'd look at the SQL generated in both cases (look at
> friends_album.query.as_sql()) and run that SQL directly in an SQL shell
> to see what the database returns.
>
> I'm kind of surprised your first attempt works at all, since it almost
> certainly is generating invalid SQL. However when I tested a similar
> situation, PostgreSQL raised the sort of error I was expecting (you
> can't do counting on a specific select column unless it's grouped), but
> SQLite returned an answer. The answer in the latter case was almost
> totally bogus, in the sense of not meaning much, but it didn't raise an
> error.
>
> So I think you should throw away your first query and only use the
> grouped version, as that's the correct SQL and going to do what you
> want. By way of example (using SQLite), here's a similar case, using a
> Tag model that has a parent_id field (it's just the tagging model and
> test data for my blog):
>
> (1) This is the normal sort of situation, grouping by the independent
> column(s):
>
> sqlite> select parent_id, count(1) from weblog_tag group by parent_id;
> parent_id count(1)
> ---------- ----------
> 5
> 2 3
> 8 2
> 9 2
> 10 1
>
> (2) This is the nonsensical case. No grouping, but still attempting to
> count by the first column:
>
> sqlite> select parent_id, count(1) from weblog_tag;
> parent_id count(1)
> ---------- ----------
> 8 13
>
> The "8" for the parent_id column here is odd (well, any answer is as
> valid as any other in this "doesn't make sense" situation). It turns out
> to be the result of count(parent_id) -- in other words, the number of
> non-NULL occurrences of parent_id.
>
> Note that if I try to run the second query on a stricter database
> (PostgreSQL), I get this:
>
> website=# select parent_id, count(1) from weblog_tag;
> ERROR: column "weblog_tag.parent_id" must appear in the GROUP BY
> clause or be used in an aggregate function
>
> Hopefully that explains why you're seeing different results and which
> one to trust. But, again, play around with the queries in an SQL shell
> and see what's going on
>
> Good eye, picking up the difference, though, and at least wondering what
> on earth was going on.
>
> 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 [email protected]
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
-~----------~----~----~----~------~----~------~--~---
